Jan 11 2023 01:32 AM
Jan 11 2023 01:32 AM
I have a requirement where we need to store more than 5 million records in a list and that will keep on growing. I will be using SharePoint REST APIs to query SharePoint list looking for 10 to 20 records in one request. How much time it will take to respond with the data?
Jan 11 2023 02:27 PM
Jan 11 2023 11:10 PM
This highly depends on how exactly you are querying what information.
In the worst case you might have to loop through all the elements as @NicolasKheirallah pointed out. This will take a very long time.
If you can access the 10-20 datarows through a query that only uses fields that are indexed (and in my experience in ideally not more than a combination of 2 fields of the simple type: string, number, date) then the REST API is still quite fast.
Alternatively you can use the Search Rest API. The content of all datarows will be added to the search index. If you tweak the managed properties for your list columns so that you can query your 10-30 items through a search query, this will be very fast.
But if you make changes to the search schema after you added the 10 million records to the list, the re-indexing of all the data might take a long time.
As SharePoint is not a database i personally would take a look into other storage mechanisms for this usecase like i.e. Dataverse.
Jan 11 2023 11:35 PM
Jan 12 2023 12:42 AM
If you manage to set the indexed columns correctly and if you formulate the query correctly, the REST Call will return in a few milliseconds with your data.
But that is not really the right question to ask, because we are only looking into the best case secenario.
In the future you might need another query... and that might not work efficently because it uses a field that is not in the index (an re-indexing of so many data rows takes time) or that is ´not indexable. Then you will have a problem.
SharePoint can handle millions of records, but is meant to be a collaboration tool, not a database. You benefit from forms, workflows and the UI to a user, not from data storage capacity and efficient querying.
If you have the requirement of having 10 million records and more in the future, you should use a database.
That might even have mechanisms to import/export your data more easily end efficient than programming data import/export yourself through the REST API. (i.e. Dataflows in Dataverse or SSIS)