Christopher Even on SharePoint Search

Best Practices for LARGE SharePoint Lists and Documents Libraries

SharePoint lists and document libraries are not designed to handle large numbers of items, in fact as soon as you add more than a few thousand you will see a warning within the List Settings Page that says:

This list or library contains a large number of items. Learn about managing a large list or library and ensuring that items display quickly.

As part of my product suite I consistently have lists with hundreds of thousands of items in them and have learned many valuable lessons in making these kind of numbers workable.

Hopefully some of what i learned can help you.

1. First off do not use the DataView webpart (from the SharePoint designer) or any xml based rendering webpart as they first grab all the items in the list and then apply the XLST to render and you will face both memory and huge performance problems. The regular list webpart works fine even for large datasets, but you will not be able to get total list counts regardless.

2. If you are planning on sorting or filtering by any columns be sure to add them to the lists indexed fields in the list settings page.

3. SharePoint is a database driven application and the list data is thus stored in database tables. The way it stores them is very denormalized as 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. Always ensure that your logical disks are defragmented once a week and use the SQL Management studio to create an index maintenence plan for all your databases.

4. For Developers using the Object model:

a. Getting counts: never reference the list item collections count property as that will trigger a complete retrieval of all the items in the list and possibly throw memory errors. If you need the list count use list.count instead of list.items.count

b. Item Access: never access the list item collection by index position as it will re-retrieve each item when accessed.

for (int i=0;i< list.items.count;i++) // bad

SPListItem li = list.items[i]; //bad

instead use the standard enumeration model to walk though your collection when needed

foreach (SPListItem li in list.items) //good

d. Paging through large numbers of list items: use the SPQuery and the RowLimit property to page through your larger list collections

SPQuery query = new SPQuery()
query.RowLimit = 10000;
do
{

SPListItemCollection myItems = list.GetItems(query);

for (SPListItem li in myItems)
{
//do something here
}
query.ListItemCollectionPosition = myItems.ListItemCollectionPosition;

} while (query.ListItemCollectionPosition != null);

e. Deleting large numbers of list items (new): use SPWeb.ProcessBatchData whenever possible. Taking above advice here is my idea of the best model for item deletion.

SPList lst = SPContext.Current.Web["MYLIST"];
SPQuery query = new SPQuery();
query.ViewFields = "<FieldRef Name='ID'/>";
query.IncludePermissions = false;
query.RowLimit = 20000;

// first get all the id's to be deleted
ArrayList al = new ArrayList();
do
{
SPListItemCollection myItems = lst.GetItems(query);

foreach (SPListItem item in myItems) // note use enumeration
{
if (item.ID == skipId) continue;
al.Add(item.ID.ToString());

}
query.ListItemCollectionPosition = myItems.ListItemCollectionPosition;
} while (query.ListItemCollectionPosition != null);

StringBuilder sbDelete = new StringBuilder();
sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
string listguid = lst.ID.ToString();
int bcount = 0;

for (int i = 0; i < al.Count; i++)
{
if (bcount > 1000)
{
sbDelete.Append("</Batch>");
lst.ParentWeb.ProcessBatchData(sbDelete.ToString());
sbDelete = new StringBuilder();
sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
bcount = 0;

}
bcount++;
sbDelete.Append("<Method>");
sbDelete.Append("<SetList Scope=\"Request\">" + listguid + "</SetList>");
sbDelete.Append("<SetVar Name=\"ID\">" + al[i].ToString() + "</SetVar>");
sbDelete.Append("<SetVar Name=\"Cmd\">Delete</SetVar>");
sbDelete.Append("</Method>");
}

sbDelete.Append("</Batch>");
lst.ParentWeb.ProcessBatchData(sbDelete.ToString());

 

I hope this helps. If you have any further advice please comment on this post and i will incorporate it for everyone. THANKS!

Joel's list of more performance related information

 also see http://go.microsoft.com/fwlink/?LinkId=95450&clcid=0x409  for an earlier white paper on the subject