Monday, September 16, 2013

List View Threshold in SharePoint

It's been a few years I have started working with SharePoint and lists, libraries, user groups and etc.. are now seems like much interesting from the internal way they are handled. Never faced a situation where 5000+ list items are involved in the days of training and now it seems like the best times where I get to know the performance management is important and other considerations are much valuable when it comes to the development.

SharePoint has this interesting feature that actually saves the database servers being loaded with heavy queries on list items and it is called list view threshold. By default it is set to 5000 for normal users and 20000 for users with administrative privileges. This can be set to any value at anytime and also disable and allow any load to be put on the db server, which is equivalent to breaking the fence around the house and asking people to come in thousands.

By the definition, it specifies the maximum number of items that a database operation can involve at one time. So when list view threshold is 5000, its 5000 items. But this does not mean it will display list items when there are less than or equal 5000 list items.

I got an opportunity to tryout this thing at office and my observations are as follows.

List View Threshold List Items Filter By Displays List Items OOTB Filtering Works
5,000 1,000 none Yes Yes
5,000 10,000 none Yes No
5,000 10,000 ID Yes No
5,000 10,000 Lookup column No No
10,000 10,000 Lookup column Yes Yes

According to these observations it shows that when you are applying filtering with any lookup column, then the list item count should be the minimal requirement for list views to work. So it seems SharePoint from the inside joins the tables associated with the lookup and then perform the filtering. 

No comments:

Post a Comment