SharePoint 2010 – List View Threshold

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

Did you get this message when trying to view or delete some item in the list ?

If YES, then we got similar problem here. In my case, I have a custom page for viewing items from certain list (lets named it “Student List”). This list has around 60.000 items which is exceed the SharePoint threshold as default 5000.

So, I have some workarounds to solve this problem recently. Lets check it up!

  1. Increase the threshold value

    Yeah you can do it. It solves the problem quickly and no more message appear.

    –  Go to SharePoint Central Admin – Application Management – Manage web applications. You will see all web apps in your farm.

    –  Highlight  web app you want to solve, then go to General Setting – Resource Throttling

    a. List View Threshold : 5000

    Student List has 60.000 items and it’s obviously exceed the threshold value. This causes the problem, raise the value and problem solved.

    b. List View Threshold for auditors and administrators : 20000

    Admin has bigger threshold than user, so in some case this issue only occur in user’s side.

    c. List View Lookup Threshold : 8

    It means SharePoint only allow to show maximum 8 lookup fields on Listview.

    –  Here you go. Just raise the value as you need.

    But, as long the time, list will getting bigger and value has to be raised again someday. So I said It’s not recommended  to change the default.

  2. Creating Index

    Index will speed up processing query to show on listview. Go to selected list and choose List Setting, find Indexed Columns. For better performance, create index for ID field and lookup field typed.

  3. Improve the Query
    Wise action to take is improving your query by adding “RowLimit” property.
    Example :

    using ( SPSite site = new SPSite (http://servername/) )
    {
      using ( SPWeb web = site.OpenWeb() )
      {
        SPList list = web.Lists.TryGetList ( "StudentList" );
        SPQuery q = new SPQuery();
        q.Query = "<Where><Eq><FieldRef Name='StudentID' /><Value Type='Number'>"
             + id + "</Value></Eq></Where>";
        q.ViewFields = "<FieldRef Name='StudentID' />";
        q.RowLimit = 100;
      }
    }
    

    By doing this, your query will catch only top 100 items from the list and not exceed the threshold. Ignored RowLimit seems query read all items in the list and cause such a pain on server.If your type of field is Lookup then you should change the query :

    q.Query = "<Where><Eq><FieldRef Name='StudentLookup' LookupID = 'TRUE' />
         <Value Type='Lookup'>" + id + "</Value></Eq></Where>";
    
    
  4. Improve the codes
    Did you have code getting items from certain list, such as:

    SPList list = web.List["StudentList"];
    item = list.Items.GetItemById( Convert.ToInt32( id ) );
    
    

    This will retrieve all items in the list, then check for certain id.
    Consider to alter code in this way :

    item = list.GetItemById( Convert.ToInt32( id ) );
    
    

    This only retrieve one item which has same id.

That’s all I know for this moment. First time, I solve the problem using number one and getting performance issue on server. And now, implement ways number 2-4 solve all the pain :D. Thanks in advance for my mentor who guide me through this, mas Libinuko (http://blog.libinuko.com/).

These links might help more:
Handling Large List
SPServer 2010 Capacity Management
Designing Large Lists
SharePoint 2010: List View Threshold Explained

Advertisements

One thought on “SharePoint 2010 – List View Threshold

  1. Pingback: Relationship-Delete-Behavior in SharePoint List |

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s