Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers 4

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)
{
   //
}
share|improve this answer
    
I've already set the QueryThrottleMode to Override in my query, but still getting the Throttle Exception. I don't want to set throttling off for all the users, only for my query –  Marlou Aug 6 '12 at 9:37
add comment

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.

share|improve this answer
    
I'm going to try the index and hope it helps. I've set a rowlimit so I don't know why it tries to retrieve so many items. –  Marlou Aug 6 '12 at 9:38
    
It's not that it has to retrieve too many items, but that it has to scan so many rows on the database. The index will prevent it having to scan those rows. –  Andy Burns Aug 6 '12 at 13:04
add comment

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.

share|improve this answer
    
Sadly it makes no difference –  lapsus Jan 17 at 15:42
add comment

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

query.Lists = "&lt;Lists BaseType='1' MaxListLimit='0'&gt;";

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.