A "throttling" exception for a lookup field

Question

I have a large list (with item count > 5000) with invoice positions, which I want to be filtered by a lookup field (invoice header ID).

1. I tried to filter the list extending the URL, i.e.: ...FilterField1=InvoiceID&FilterValue1=185
2. I made a view with a hardcoded condition: InvoiceID=185
3. I tried to open a Dropdown for filtering the column

Every time I get a “throttling” exception, that says the list is too large, although the lookup column is indexed. For indexed fields of any other types (text, number) filtering works OK. Does anybody know a solution to this problem ?

Answer 1

I was having the same problem. After quite a bit of experimentation I figured out the solution for my case. My problem was the exact syntax of the query.

This works (for me). The key was to have LookupId='TRUE' in the FieldRef and to set the value type to 'Integer'.

<Query>  
   <Where>  
      <Eq>  
         <FieldRef Name='SubmissionLookup' LookupId='TRUE' />  
         <Value Type='Integer'>1162</Value>  
      </Eq>  
   </Where>  
</Query>

What was confusing is that U2U CAML Query builder did not give me all of this. What was also confusing is that in one test I had LookupID='TRUE' (note the capital 'D') and the index was clearly being used when logged in as a farm admin (I know this only because the time to make the call was sub-second vs. 10 seconds as I have 30,000+ rows in the list). However, when I logged in as a non-farm admin I got the throttle violation. So, it is somewhat of a bug that the throttle violation checker is not in line with the CAML query parser.

Answer 2

You are getting this exception because probably your query returns more results that the one allowed for that web app.

To verify and/or modify this go to your web application in central admin and look for Resource Throttling. (would give you better instructions but not sure what version of SharePoint you are using)

Bear in mind that bad configuration of Resource Throttling may affect performance.

Hope this helps

Renzo