When a list or library has a large number of items, you must carefully plan its organization and how users need to access the data to help preventing adverse effects on performance. For best performance, do not exceed 2,000 items in a list level. For example, the root of the list or a single folder. In case if you must create and browse large lists, use the following best practices.
1) Create Indexed Column (An index on a column enables Microsoft Windows SharePoint Services 3.0 to quickly analyze the data in that column, even when working with thousands or millions of items)
2) Use only one Indexed Column in view
3) First column that is used to filter the view must have an index
4) Change the default view of the list to a customized filtered view that follows these recommendations:
- The view returns fewer than 5,000 items.
- The first column that you use to filter the view has an index that sufficiently reduces the total number of items returned.
- The view displays only those columns that are absolutely required.
- The view includes as few lookup columns as possible. Each lookup column in a list that is included in a view causes an additional join and additional calls to the database.
5) [new] Do not use the DataView webpart or any xml based rendering webpart as they first grab all the items in the list and then apply the XLST to render, thus facing both memory and huge performance problems. The regular list webpart works fine even for large datasets (but no total list counts in this case)
6) [new] Defragment logical disks once a week and use the SQL Management studio to create an index maintenence plan for all your databases. This is necessary because list content is stored de-normalized - each columns data is stored in a single row, which means if you have 2 list items with 3 columns you get at least 6 rows of actual sql data. This adds up very fast and will quicky cause both disk and index fragmentation
7) [new] Never reference the list item collections count property, or refer by index, as that will trigger a complete retrieval of all the items
int i = List.Items.Count();
var items = List.Items,
always reference the list's propertly directly or use iterators
int i = List.Count();
foreach (SPListItem in list.items)
8) [new] Use SPQuery and the RowLimit property to page through your larger list collections.
9) [new] Use SPWeb.ProcessBatchData to delete items from list whenever possible