0

My understanding is that SharePoint On-Prem has an issue with lists that contain over 5000 items. The performance of the list starts to degrade. My understanding is a list can handle many more items, but they paint slowly.

Does SharePoint Online suffer from performance hit?

Thanks

  • 2
    Adding this as a comment since it's not really answering your question, but the list view threshold of 5000 will not cause items over 5000 to "paint slowly". If the view returns over 5000 items, the web part will not show any and display an error. The list can have more than 5000 items, but the views must be set up with filtering so that each view returns less than 5000 items. Otherwise - you get nothing. – Dylan Cristy Apr 15 at 20:55
1

basically there are 2 different things over here.

  1. List view threshold : Number of items returned in a view is called List view threshold. Limit for SharePoint Online is 5000, and if in a view item count more than 5k then it will throw the error. here is guidance to manage the large list from Microsoft.

  2. Total Items in list.: You can store up to 30 million items or files in a SharePoint list or library. but off course if you have more items in the list it may impact the performance a bit.

The best practice is to use folders and subfolders structure, where in each folder you have less than 5000 items.

0

Let us go over your question point by point and would also like to share some additional information.

My understanding is that SharePoint On-Prem has an issue with lists that contain over 5000 items.

I would not call it as issue. SQL has put a threshold limit to on lists item count so that performance of query does not degrade. we will get into detail of this.

The performance of the list starts to degrade. My understanding is a list can handle many more items, but they paint slowly.

If we are working withing limits of threshold performance does not directly degrade, SharePoint list can handle around 30 million items.

Does SharePoint Online suffer from performance hit?

True, all the above points is valid for SharePoint online also.

Some Additional information to understand about this limit.

For performance reasons, whenever SQL Server executes a single query that returns 5000 + items, locks escalation happens within the SQL table. As a result, the entire table will be locked. Since the entire Share Point data is stored as a single table, a single list view query that exceeds 5000+ items will lock the entire Share Point data table within that content. The database and all the users will face huge performance degradation. The entire set of users using Share Point at the time of lock escalation will have to wait for a longer time to retrieve the data. Hence, you can see that the list threshold is a limitation that is imposed upon Share Point by its backend SQL Server. This issue is generated from SQL and the reason is row lock escalation. In order to avoid this performance degradation, Share Point has imposed the limitation of 5000 items to be queried at any point of time. Any queries for 5000+ items will be dealt the threshold error message. Ref link

Point to note when working with query on List with more than 5000 items

List items threshold value is 5000(set default by SP but we can change). If you CAML query filters is going to return more than 5000 items, it will not send data but will give you error. If you have specified multiple filter, the first filter should return items less than 5000. So Order of specifying filter is also important.

Also make sure you are creating index for columns which you are going to use in filter. If your filter columns are not indexed and items in list have crossed above 5000. your caml query will not work.

if your query is going return data more than 5k items as per your business logic, it will not return data. To avoid this, you can do following.

List down all the columns on which you are going to filter data(pass column name and its value in where clause) Create indexes on this columns(also consider that you cannot create index on certain type of column like, multiline of text, multi value people and group etc. Analyse and check that is your filters/caml query would return more than 5k items in future. if that is case, you might need to apply additional filter based on some column and split your code logic so that it returns data for each for that column type. Provide some addtional filters on UI for user to select so that your query does not cross 5k limit while returning data.

Same case goes with OOTB views and filters, you have take care of above points.

Hope this helps...happy coding..!!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.