1

I was querying a list having many columns, some of them being look-ups. My query was to return a SPListItem based on one ID. Now, when I want to bind the value of look-up field from the SPListItem to a drop-down or text field, I came across an interesting situation.

I used the below code for getting the look-up value:

SPFieldLookupValue fieldLookupValue1 = new SPFieldLookupValue(ListItem["LookupField"].ToString());

if (fieldLookupValue1 != null)
   Textbox.text = fieldLookupValue1.LookupValue;

The above was working well when logged in as System Account. But it throws an error "Value does not fall within the expected range" when logged in with a user having read access or above. What is interesting is after increasing the value in "List View Lookup Threshold" under Resource throttling for the Web Application in Central Admin this issue got resolved on its own.

Can someone explain what is happening behind? What is the trade-off for increasing the values under Resource throttling?

  • Thanks @CameronVerhelst ! Is it not that "List View Lookup Threshold" is meant for List "View", why then does an increase of this threshold resolve my error in the code where I get data using CAML query ? Just wanted to know the relationship.. – Sudhakar Jul 1 '14 at 11:40
  • 1
    Because it's actually an SQL configuration setting. Although it says "View" it applies to all list queries. It's even enforced within an SQL Query, if at any time you return more than the limit's amount of rows you're hitting the treshold. That's why they say to put your most limiting filter first, and especially the filters using indexed fields. This gives the weird result that you hit the limit when you're final result is actually only 20 rows or something. – Cameron Verhelst Jul 1 '14 at 11:45
3

As Cameron pointed out, it's because of performance reason. Administrators got permission to query above the default 8 columns, standard user's do not.

Microsoft states the following on their site about list performance. See the part Lookup columns and list views in the article. There's some good graphs showing what happens with the SQL performance as well.

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.

Designing large lists and maximizing list performance

  • Thanks! @user19952 Is it not that "List View Lookup Threshold" is meant for List "View", why then does an increase of this threshold resolve my error in the code where I get data using CAML query ? – Sudhakar Jul 1 '14 at 11:43
  • I'm afraid i do not understand your question, can you elaborate? – Christoffer Jul 1 '14 at 11:51
  • Just saw that Cameron answered. – Christoffer Jul 1 '14 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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