I have always understood that in SharePoint a list can contain 100,000+ items and still perform acceptably as long as the views are limited to 2000 items or less per view. However a developer I recently spoke with said that when a SharePoint list is opened by a user SharePoint queries every item in the list and then grabs the items that are needed for the specified view after all items have been returned. So he states any list with more than 2000 items total will have a performance hit. Can anyone shed some light on whether we should be limiting users to 2000 items per list total or just enforcing 2000 items per view?
Steve Peschka (from Microsoft) wrote an excellent whitepaper on this very topic, and it addresses your questions and more:
http://go.microsoft.com/fwlink/?LinkId=95450&clcid=0x409
The quick summary: large lists are feasible, but yes: they do require some special programmatic treatment. You'll find those options and how they break down in the white paper.
Good luck!
SharePoint Lists can literally hold millions of items and there is no reason to limit users to 2000 items per list. The performance however will be affected if it crosses 2000 items unless these large lists are managed and configured effectively. A simple and very effective way of improving performance is to use a combination of indexing and filtering on the lists.
If you go to the 'List Settings' page, you will see a link for 'Indexed Columns'. Select one or more columns that are actively used and then create filter for your custom views on these indexed columns. The performance should improve heavily without actually limiting users to 2000 items per list.
Looks like there have already been a lot of good replies/answers on your post. I will just tell you from my personal experience, that we have a 10,000+item list with about 40-50 fields. And it works just fine with no noticeable performance changes from our other small lists. We hit this list in a lot of different ways, including as a datasource in Access for reporting, in dashboards, and in views. I do have to be careful about filtering the views to manageable lengths, but otherwise no problems.
I am wondering if your developer was referring specifically to document libraries, rather than just plain lists. Document libraries, from my research, are a little different. I do not know the specifics for document libraries, but I would also recommend that whitepaper which several replies here referenced.
How long is a piece of string? ;)
http://technet.microsoft.com/en-us/library/cc287790%28office.12%29.aspx
Refer to the table under "The following table lists the recommended guidelines for site objects".
Hi Anjali,
Have you looked closer to the indexing of columns. I have heard and read (for instance this good post http://blog.dynatrace.com/2009/02/03/sharepoint-more-on-column-index-and-their-performance-impact/ ) that indexing more then one column might not be that much of performance improvement.
I also would like to share some findings from various customers.List can seariously cause problems with less then 2000 items. I have seen a quite large SQL cluster go down because on list had 147 lookup columns, Yes! your read right, and honestly, can you blame an end user for that? It's possible. In 2010 this is a part of the list throttling and the default value is, if I'm not mistaken 8.
Prior to SP2 it was a problem with the long running operations list that was never cleanup and that could cause problems like this.
If you are not aware of where you have large lists I suggesst installing the Administration toolkit that includes the SPDiag tool that amung other things are checking this.
At one time I saw an environment get really slow and that turned out to be the histor list for workflows containing 360 000. The history list is saves items up to 90 days (I think it was) so if you use a lot of workflows could be good to keep an eye on.
/Mattias
Hi Mattias,
Thanks for sending the link over and sharing your findings. I think there is a fine balance between the number of columns you should index Vs increasing the performance. It would very much depend on how your list columns are structured, the number of look up columns in the list etc. The performance will definitely take a hit if you start indexing each and every column as this will severely impact the backend SQL query performance and causes overhead for every index you define.
However, the performance should still improve even if you index more than one column if for example they are look up columns or columns that you will be using to filter, sort or 'group by' your data. So it involves a careful planning on really what column you need to index and not go by the notion that adding an index will always increase performance.
-Anjali