Forum Discussion

Vikas_Tiwari's avatar
Vikas_Tiwari
Copper Contributor
Jan 11, 2023

How much time it takes to query 5 records out of 10 million records from SharePoint List/Library?

Hi,   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 t...
Vikas_Tiwari's avatar
Vikas_Tiwari
Copper Contributor
Jan 12, 2023
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?
SvenSieverding's avatar
SvenSieverding
Bronze Contributor
Jan 12, 2023

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

Resources