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=”https://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.
“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.

$web=Get-SPWeb $weburl
$list.enablethrottling = $false

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.


About bpostaci
Escalation Engineer in Microsoft.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: