Working with large lists in SharePoint

SharePoint 2010 lists and libraries can hold a maximum number of 30,000,000 items. In a SharePoint site the list view threshold is 5000 for users and 20,000 for auditors and administrators.

If the list has more than 5,000 items, even if you create views to filter out some items, you won’t see all items in the views.

To resolve this problem:

Option 1. Create index for the column to be filtered by. For example, create an index on “State” column, then create a view filtered by “State”. But if the items for this view is more than $5,000, it does not work; It only works on views with less than 5,000 items.

Option 2. Use folders to hold items; Each folders can have 5,000 items;

Option 3. Datasheet Views can display 50,100 items; (You browser must support ActiveX Control; Datasheet views doesn’t not work if you have Microsoft Office 64bits and Internet Explorer 64 bits)

Option 4. Administrator can set up a “Daily Time Window” in Central Administration – General Settings – Throttle Management”. During this time window, you can view large lists, and you can only create index for large lists in this time window, unless you have special permission.

Option 5. User “Key Filters” to filter the list;

Option 6. User “SharePoint Workspace” to work with large lists. Keep in mind that you can’t export a list that has more than 30,000 items;

Option 7. “Export to Excel”. Keep in mind that you can’t export a list that has more than 52,000 items;

Option 8. “Open with Access”

Option 9. Using Visual Studio

using (SPSite currentSite = new SPSite(SPContext.Current.Site.ID))
            using (SPWeb currentWeb = currentSite.OpenWeb("crm"))
                SPQuery qryCustomer = new SPQuery();
                qryCustomer.RowLimit = 1999;
                qryCustomer.Query = "";
                SPList oListCustomer = currentWeb.Lists["Customer"];
                    SPListItemCollection listItemCollection = oListCustomer.GetItems(qryCustomer);
                    foreach (SPListItem item in listItemCollection)
                        //string staffID = item["User Name"].ToString().Substring(0, item["User Name"].ToString().IndexOf(";#"));
                        DataRow dr;
                        dr = dtCustomer.NewRow();
                        dr[dc_dtCustomer1] = Convert.ToInt32(item["ID"]);
                        dr[dc_dtCustomer2] = item["State"].ToString();
                    qryCustomer.ListItemCollectionPosition = listItemCollection.ListItemCollectionPosition;

                } while (qryCustomer.ListItemCollectionPosition != null);

This entry was posted in SharePoint 2010. Bookmark the permalink.

2 Responses to Working with large lists in SharePoint

  1. says:

    Thanks for writing this up

    • Lin Zhang says:

      Thanks for reading my blog. I just made some changes to this post (Option 9. Using Visual Studio), please re-visit my blog if interested.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s