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

SharePoint 2010 includes a list view lookup threshold as part of its resource throttling. Columns that count as a lookup include Lookup, taxonomy (managed metadata), and User (PersonOrGroup)

The default is set to 8, and for SharePoint 365 installations, this value cannot be increased.

After implementing a more complex solution to work within this limit, i noticed that by omitting the <Query/> element in my SOAP request, the threshold exception is not thrown

For instance the following request would succeed, returning the first 500 rows of data including all values for the lookup threshold counting columns:

"<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <GetListItemChangesSinceToken xmlns="http://schemas.microsoft.com/sharepoint/soap/">
      <listName>{9F2DD6AD-85A2-4E0B-8EB5-8FF2796A4659}</listName>
      <viewFields>
        <ViewFields Properties="True" xmlns="" />
      </viewFields>
      <rowLimit>500</rowLimit>
      <queryOptions>
        <QueryOptions xmlns="">
          <IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>
        </QueryOptions>
      </queryOptions>
    </GetListItemChangesSinceToken>
  </soap:Body>
</soap:Envelope>"

While the following request fails with the soap server exception: "The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator."

"<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <GetListItemChangesSinceToken xmlns="http://schemas.microsoft.com/sharepoint/soap/">
      <listName>{9F2DD6AD-85A2-4E0B-8EB5-8FF2796A4659}</listName>
      <query>
        <Query xmlns=""></Query>
      </query>
      <viewFields>
        <ViewFields Properties="True" xmlns="" />
      </viewFields>
      <rowLimit>500</rowLimit>
      <queryOptions>
        <QueryOptions xmlns="">
          <IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>
        </QueryOptions>
      </queryOptions>
    </GetListItemChangesSinceToken>
  </soap:Body>
</soap:Envelope>"

Question: Is this a bug in SharePoint, or is it valid to expect a table dump when no query is supplied?

If returning all columns still requires lookups for the threshold counting columns, this ought to be a bug.

share|improve this question
    
were you able to see your default view for the list directly in SharePoint? As you are not providing a "viewname" in your call, you are getting results back from within the default view for the list. If your default view has more than 8 lookup columns you should get the same exception and not see the list items at all. right? –  Ken May 1 '12 at 22:12
1  
I also noticed that "List View Lookup Threshold" setting does not apply to site collection admins...I mean site collection admins should still see the default view list items where as end users get the exception...make sure to check the service call credentials... –  Ken May 1 '12 at 22:26
    
For both calls, the user has limited privileges on the library. When viewed through SharePoint, the listview lookup threshold exception is thrown. In the example SOAP call, I tried to make the call as basic as possible, just to highlight the fact that I am able to by-pass the exception and retrieve all rows by simply excluding the Query element in the call. –  Arne Hoogensen May 2 '12 at 18:14
    
I don't have this totally understood yet myself, but I have seen something similar with the list view threshold. If you don't have a filter in your query you can (sometimes?) request all list items (assuming you provide a row limit) even though the total number of rows in the list exceeds the list view threshold. Once you add a filter/where in your query having the row limit does not save you. –  Kirk Liemohn May 11 '12 at 15:58

1 Answer 1

The article The SharePoint 2010 “List View Lookup Threshold” and Why We Don’t Change It explains the Threshold limit of List View Lookup Columns & apparent impact on performance.

Excerpt:

Each lookup column in a list view causes a join with another table. Each additional lookup column in a view increases the complexity of metadata navigation and list view queries. In addition to standard lookup columns, single-value managed metadata, multiple-value managed metadata, single-value people and group columns, and multiple-value people and group columns count as lookup columns. Adding lookup columns to a view does not cause a gradual or linear decrease in performance, rather performance is somewhat stable until after eight columns when it rapidly degrades.

share|improve this answer

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.