However, once the library has reached 6000 plus something items, everything suddenly slowed down dramatically, basic queries which used to load in 1 second started to time-out or take more than 60 seconds to load.
In addition to that, WorkflowServiceHealth.aspx page just died and only returned "Timeout Expired" error.
After several attempts to fix the issues by adding extra indexes and limit the number of results using "$top=1" I've created a support ticket and over the next week or two had a daily chat with Microsoft support team.
The final conclusion was that "slow performance should be expected in such scenarios" and there is nothing you can really do about it rather than decrease the number of items to <5000.
According to Microsoft support this is mentioned in the following part of the article:
"To minimize database contention, SQL Server often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it's more efficient for SQL Server to temporarily escalate the lock to the entire table until the database operation is completed."
I wish they had a note somewhere saying that the performance decrease can be so huge (1 second -> 1 minute).
As a "lesson learned" I've noted the following points:
- Do not query large document libraries using List REST API, use Search API instead
- Do not use workflows in a large libraries. Split the data into the "Active" and "Archive" libraries
I hope this will be useful to those who have similar issues or just planning to use large document libraries.
No comments:
Post a Comment