When using Lookup Column facing exceed list view threshold

Recently faced following issue ; To clarify that i want to share how i reproduce it ;
This issue can be reproducable SPO, SharePoint 2013 and 2010

+Create a list named “LargeOne” with 2100 item
+Set the list view threshold limit 2000 for this list
+Create another list for lookup named “LookupOne”
+Add some data in LookUp List
+Add a Lookup column to “LargeOne” list as pointing the “LookupOne” List (For example title)
+Index that column in LargeOne List
+Create a view named “testview” by filtering related LookupColumn and set row limit 100

Occurence :
View not show items and facing following error
“The view can not be displayed because it exceeds the list view threshold(5000 items) enforced be the administrator”

Expected :
View shows items

What you will see in ULS Logs
01.06.2015 14:15:07.70 w3wp.exe (0x3978) 0x1694 SharePoint Foundation Health 46ri High
Throttled:Big list slow query. List item query elapsed time: 0 milliseconds, Additional data (if available): Query HRESULT: 80070024 List internal name, flags, and URL: {976E4B58-476A-4B5F-A4D5-7AB44279BD71}, flags=0x0000000020801080, URL=”http://blog.bugrapostaci.com/Lists/Largeone/testview.aspx&#8221; Current User: 2110 Query XML: “<Query><Where><Eq><FieldRef Name=”SiteID”/><Value Type=”Text”>999</Value></Eq></Where></Query>” SQL Query: “N/A”

01.06.2015 14:15:07.70 w3wp.exe (0x3978) 0x1694 SharePoint Foundation General xxpm High
Unable to execute query: Error 0x80070024

01.06.2015 14:15:07.74 w3wp.exe (0x3978) 0x1694 SharePoint Foundation Web Parts 89a1 High
Error while executing web part: Microsoft.SharePoint.SPQueryThrottledException:
The view can not be displayed because it exceeds the list view threshold(5000 items) enforced be the administrator
—> System.Runtime.InteropServices.COMException (0x80070024):
The view can not be displayed because it exceeds the list view threshold(5000 items) enforced be the administrator.

Well this issue is “By Design” . The throttling limit is enforced in order to protect the health of the SharePoint server. Looking up the value of the lookup list causes the throttling limit to be enforced.  When making a query to SQL, we limit our queries to less than 5000 items.   There is a hard coded functionality on SQL that will lock the entire table if the query exceeds 5000 items.  In order to offset this we set the list threshold to a value equal or less than this to avoid such actions.

And documented following article.
https://support.office.com/en-us/article/Manage-lists-and-libraries-with-many-items-b8588dae-9387-48c2-9248-c24122f07c59?ui=en-US&rs=en-US&ad=US#_Toc346115857
“Although you can index a lookup column to improve performance, using an indexed lookup column to prevent exceeding the List View Threshold does not work. Use another type of column as the primary or secondary index.”

And more extra info: Indexing the column in the other list or library does not improve performance of the lookup operation

For a workaround you think that you may be  disable throttling ,  but this time for a big possibility you will face performance issues.

$weburl=”http://<URL>&#8221;
$listname=”NameOftheList”
$web=Get-SPWeb $weburl
$list=$web.lists[$listname]
$list.enablethrottling = $false
$list.update()

On this manner we suggest make a good planing for handle large lists when you are using SharePoint otherwise that could create a real pain and lots of jobs to do.

The settings for this list have been recently changed. Refresh your browser before editing this list

Hello Everyone,

In this article,i am going to talk about one of SharePoint development issue with object model if you faced this annoying error :
“The settings for this list have been recently changed. Refresh your browser before editing this list”

Here a sample scenario:
* You have a C# application from which you are frequently uploading documents to a SharePoint Foundation 2010 Document Library while updating or deleting the list item getting this error.

You may encounter possible 3 issue:

1) Your event recievers is executing something wrong.
Check your event handlers code. for detect the problem you may try to remove the event reciever(s) and test that issue is happening again.
It may cause by some buggy code belongs to triggered eventreciever or this event reciever has activated more than one time or not an appropriate time.if you have this kind of issue
i suggest  that use SPEventReceiverBase.EventFiringEnabled property to prevent unnecessery code execution when your event recievers triggered.

2) You may changing some  property of a list which is not to done in item level operations.
somehow you change high level setting or property of this list object  like SPList.EnableThrottling property , by design you should get fallowing error “The settings for this list have been recently changed. Refresh your browser before editing this list.” This is same scenario as while UserA who already editing a document metadata , changing Versioning settings of the current list by an administrator, UserA will get the error when he/she try to save his/her changes.

Check your source code for to be dangerous changes of list settings.So there is not any way to escape from this error while changing this kind of settings .Main rule is simple do not change list settings while you are working on list items.

3) It caused by a bug. Consider the following scenario:

  • You click the Variation link to configure the variation setting in a Windows SharePoint Services 3.0 publishing site.
  • You click the Variation Labels link to create two variation labels.
  • You click the Create Hierarchies link to create hierarchies.
  • You create a new publishing page in the source variation site.
  • You publish the new publishing page, and then you wait until the page propagation ends.
  • You edit the page in the destination variation site, and then you try to save it.

In this scenario, you receive the following error message.it is fixed by June CU 2011
http://support.microsoft.com/kb/2536591

If you have another one , please inform me .)