SharePoint 2010 list view throttling and custom CAML queries

My current project involves storing thousands of items in SharePoint lists. Whilst we have been well aware of the usual considerations around being careful to limit the number of items that we are displaying at any one time, I recently ran into an interesting issue with the new list throttling capabilities of SharePoint 2010 and the manner in which the CAML query was being processed.

In this scenario I was using a Data Form Web Part and modifying the CAML query to restrict the query based upon parameters. I modified my query to return me items that where relevant for a given date, e.g.
Start Date <= {Date} <= End Date

With the corresponding CAML like the below:

<Query>
 <Where>
  <And>
    <Leq>
      <FieldRef Name="StartDate"/>
      <Value Type="DateTime">{date}</Value>
    </Leq>
    <Geq>
      <FieldRef Name="EndDate"/>
      <Value Type="DateTime">{date}</Value>
    </Geq>
  </And>
 </Where>
</Query>

Now, although this query was working on a list with several thousand items (more than 5000) I knew from the data that there was only a handful of items that should be returned for this query – for a given date. And yet, I received an error about this exceeding the list view threshold!

List View Threshold Web Part Error

After researching this I re-visited the Microsoft white paper ‘Designing Large Lists and Maximizing List Performance‘ and noticed the following extract:

There may be cases where multiple indexes are not useful. Consider a situation where you are filtering on two columns, Division AND Content Type. Because this is an AND operation, only the intersection where the filters for Division and Content Type match will be returned, this means that when the query is performed all of the items that match the Division filter will be returned first and then those items will be filtered by Content Type. If there are only 10 items matching a particular division, then you will have a sufficiently small set of data that an index on Content Type will not matter. If there are thousands of items that match the value for Division, then a compound index should be used.

The example in the extract indicates that the CAML query is processed in such a way that, in our example, all of the items where the Start Date value is less than the Date Parameter value will be returned, and only then further refined by End Date. So even though my query will only return a handful of items when completed, I need to consider how many items are returned by the first operation in the query.

I was really surprised at this. For some reason I expected it to somehow consider the ‘full query’ before applying the list throttling. I’m no DBA though and so perhaps this makes sense if you understand how the data is retrieved from SQL. The good news is that this is known/ expected behaviour and catered for with the ability to index list columns. Particularly with the ability to created compound indices based upon a combination of multiple columns.

The bad news is my scenario is that a column of type date seems to be one of the types where a compound index can not be created:

List Column Compound Index Option

So what to do…. I was able to find a workaround for this scenario because I was also filtering the results on another column. I was therefore able to construct my CAML query in such a way that the first operation in the query restricts my data to return less items than the threshold, e.g.

<Query>
 <Where>
  <And>
    <Eq>
        <FieldRef Name="Field1"/>
        <Value Type="Text">Value1</Value>	
    </Eq>
    <And>
      <Leq>
        <FieldRef Name="StartDate"/>
        <Value Type="DateTime">{date}</Value>
      </Leq>
      <Geq>
        <FieldRef Name="EndDate"/>
        <Value Type="DateTime">{date}</Value>
      </Geq>
    </And>
  </And>
 </Where>
</Query>

In conclusion, I think that I’ve learnt the following:

  • Read the white paper Designing Large Lists and Maximizing List Performance!
  • Indices on list columns will help when running into list view throttling
  • CAML queries are not necessarily processed as a whole, rather step-by-step
share and enjoy
  • Print
  • Twitter
  • Digg
  • del.icio.us
  • StumbleUpon
  • Yahoo! Buzz
  • Google Bookmarks
  • Facebook

9 comments to SharePoint 2010 list view throttling and custom CAML queries

  • Barto Molina

    “my scenario is that a column of type date seems to be one of the types where a compound index can not be created”… That’s so SharePoint! 🙂

  • Thanks – I did not know that CAML queries were broken down like this. I suppose this would affect LINQ to SharePoint in the same way when the SPMetal query become a CAML query.

  • PaulE

    I was going NUTS trying to figure out why I could never get my SPQuery to execute without giving the dreaded “…exceeds the list view threshold…”! Read too numerous writings about how throttling works and tried everything they recommended and then some: setting RowLimit on SPQuery; setting QueryThrottledMode = SPQueryThrottleOption.Override; and, even running query elevated (SPSecurity.RunWithElevatedPriviledges). Nothing worked. :-Z Found your article here and added the column I was using in my Where clause of the query and BINGO! THANK YOU!!!

  • Joe

    I am having a problem with the resource throttles. According to this article: http://msdn.microsoft.com/en-us/library/ff798465.aspx column indices should indeed allow you to query against more than 5000 items if you are filtering on an indexed column. But I have not yet been successful. Is the scenario from the article in the “How Does Indexing Affect Throttling?” section accurate?

    • Hi Joe – I’m not 100% sure about that sorry. As I understand it – and as I read it from the article – indexing columns should help you overcome the throttling in your scenario, but it isn’t something that I’ve tried to do before. If you’ve tried this and it isn’t working then perhaps it could be a bug?

  • Dhanya

    You won’t get a throttle error if you are searching for an indexed column . Can not create an indexed column for a multiple lines of text field and searching this column by adding a where clause scans the entire list and causes throttle exception .

  • Dhanya

    Please try this to avoid throttling error for an indexed column .

    Even if you set a column as indexed , it need not be strictly following that and using object model you can force the query to be as indexed as below :
    <Eqabc” + ContentIterator.ItemEnumerationOrderByNVPField;

    and use list.EnableThrottling = false for a non indexed column

  • Rahemath

    How to override the LVT, in case of LINQ with SharePoint 2010

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>