locked
SPSiteDataQuery and list throttling - does it consider indexed columns? RRS feed

  • Question

  • Hi all,

    Does anyone know if it's possible to do a SPSiteDataQuery where the total number of items in the list exceeds the list threshold limit?  We have indexed the column we are doing WHERE on, but always hit the list throttling limit since the total number of items in the lists is more than the throttling limit. The number of items returned is just a few though.

    The documentation from MS seems to give conflicting information.

    According to the following page, SPSiteDataQuery does not consider indexed columns. In that case the behaviour is expected.
    http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.querythrottlemode.aspx

    This page however, indicates that you actually CAN index columns to avoid throttling issues on SPSiteDataQuery.
    http://msdn.microsoft.com/en-us/library/ff798394.aspx

    What's correct here?

    If SPSiteDataQuery doesn't consider indexed columns, is there any workaround or other way to retrieve data from the entire site when the number of list items exceeds the threshold?

    Thanks,
    Christoffer

    Monday, July 25, 2011 8:46 AM

Answers

  • This throttling liimit also applies the number of lookup fields. The default is 8. So if your lists have more than 8 lookup lists (lookup, person/group, workflow tasks etc) combined then you will hit this limit. This too can be overriden or increased. According to the documentation you posted I would be confident that the SPSiteDataQuery does not use the indexes.
    Blog | SharePoint Field Notes Dev Tool | ClassMaster
    • Marked as answer by Shimin Huang Friday, August 5, 2011 7:11 AM
    Tuesday, July 26, 2011 6:16 PM

All replies

  • Does the column you are indexing have more than 10 unique values? Many times the index is ignored if the column does not have enough unique values. Secondly, are all the possible lists you are querying have this column indexed? Finally, if you are querying across many lists the total number of indexed values that have to be evaluated may throw you over the threshold. There are also settings in Central Administration to allow the limit to be overriden when using the object model.
    Blog | SharePoint Field Notes Dev Tool | ClassMaster
    Tuesday, July 26, 2011 3:27 AM
  • Hi Steve and thanks for the reply,

    There are only a handful of lists in the site collection. There are several hundred different values in the columns so that shouldn't be a problem.

    We don't want to override the limit - it's there for a reason. If we get index to work that would be better. But as we said, I'm not sure if it's supposed to work or not. The documentation indicates that it doesn't.

    You can probably recreate this by creating two custom lists with 1001 items in each list, use the same title as ID, index the title and then do a SPSiteDataQuery against it and pick up a specific item by title. I always hit the list threshold if I'm not site collection admin.

    /Christoffer

    Tuesday, July 26, 2011 10:15 AM
  • This throttling liimit also applies the number of lookup fields. The default is 8. So if your lists have more than 8 lookup lists (lookup, person/group, workflow tasks etc) combined then you will hit this limit. This too can be overriden or increased. According to the documentation you posted I would be confident that the SPSiteDataQuery does not use the indexes.
    Blog | SharePoint Field Notes Dev Tool | ClassMaster
    • Marked as answer by Shimin Huang Friday, August 5, 2011 7:11 AM
    Tuesday, July 26, 2011 6:16 PM