SPSiteDataQuery keeps throwing throttle exception

Question

I have a SPSiteDataQuery that returns a result from 6 lists. The lists together contain like 200000 items. The result for my query is like 100 items (only today's items). But no matter what I do, I keep getting a Throttle Exception. I already set the rowlimit to 2500 and override the query throttle mode. This is my code.

    Builder camlBuilder = new Builder(U2U.SharePoint.CAML.Enumerations.CamlTypes.Query);

    camlBuilder.AddWhereField("Created", createdFrom.ToString(), "DateTime", "Geq", out addCombinerNode);
    camlBuilder.AddWhereField("Created", createdTill.ToString(), "DateTime", "Leq", out addCombinerNode);

    camlBuilder.AddViewField("ID");
    camlBuilder.AddViewField("LinkFilename");
    camlBuilder.AddViewField("FileDirRef");
    camlBuilder.AddViewField("ContentType");
    camlBuilder.AddViewField("Created");
    camlBuilder.AddViewField("Modified");

    SPSiteDataQuery apQuery = new SPSiteDataQuery();
    apQuery.Lists = "<Lists BaseType='1' />";
    apQuery.Webs = "<Webs Scope='Recursive' />";
    apQuery.ViewFields = camlBuilder.ViewFieldsNode.InnerXml;
    apQuery.Query = camlBuilder.WhereNode.OuterXml;
    apQuery.RowLimit = 2500;
    apQuery.QueryThrottleMode = SPQueryThrottleOption.Override;       

    // execute the query on the site
    DataTable dt = new DataTable();
    dt = web.GetSiteData(apQuery);

I don't know why I'm still getting this Throttle Exception and how to prevent this. Can someone explain this behaviour?

Answer 1

If throttle is enabled, you can change your queries based on that knowledge to avoid hitting the throttle. There is always a SPQueryThrottledException class which can be used when you fail to change your query and hit the throttle.

But, once you know the throttle is there and can catch exceptions, you also may want to override the throttle, using the object model. There are two approaches you can use:

1. First, you can do it on an individual query, like on SPSiteDataQuery object's QueryThrottleMode property.

2. If you are a farm admin, you can use the SPList’s EnableThrottling property to turn throttling off. Last, you can use the OM to set the time when the throttle is off.

The most common approach is the SPQuery or SPSiteDataQuery object, see below an example

try
{

    SPSiteDataQuery qry = new SPSiteDataQuery();
    qry.QueryThrottleMode = SPQueryThrottleOption.Override;        
    dt = web.GetSiteData(apQuery);
}
catch (SPQueryThrottledException)
{
   //
}

Answer 2

I would suggest:

Try to query indexed fields. If your query is on an unindexed column then the query forces a scan of all the rows - which is slow, and which is possibly why you're getting the exception. You make also need to add a sort order of:

<OrderBy UseIndexForOrderBy='TRUE' Override='TRUE' />

I'm not entirely sure how column indexing relates to the SPSiteDataQuery, but it makes a big difference for SPQuery objects.

Even then, you may have problems with retrieving too many items. You could try paging the retrieved items, though the SPSiteDataQuery class doesn't seem to have this built in by default.

Answer 3

The answer is to increase the MaxListsLimit property of the lists element, like so:

query.Lists = "<Lists BaseType='1' MaxListLimit='0'>";

http://suranja.blogspot.co.uk/2009/11/spsitedataquery-error-too-many-lists-in.html

Answer 4

To override throttle mode, may be you should run the code under elevated privileges which uses the application pool's account for the execution. Additionally, list throttling levels are different for administrators and regular users. You can check for increasing throttling and query limits for administrators as well.