Forum Discussion
How much time it takes to query 5 records out of 10 million records from SharePoint List/Library?
Hi Vikas_Tiwari,
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.
Best Regards,
Sven
- Thank you Sven for answering the query. If, I have to get less than 20 data rows with rest API having 3 indexed columns (1 string, 2 date), approximately how much time it will take if query is performed on 10 million records?
Hello Vikas_Tiwari,
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)
Best Regards,
Sven
