"View exceeds list view threshold" even when it doesn't

Question

I have a document library in SharePoint Foundation 2010 which contains over 10,000 items, arranged in folders such that no folder contains more than 2000 items. I've created a public view called 'Documents checked out' whose criteria is very simple:

• Filter: 'Checked out to' is not equal to ''
• Folders: 'Show all items without folders'

Under WSS 3.0, this view would achieve the desired behaviour. For this library, there are never more than 100 documents checked out at any given time. Under SharePoint 2010, I get the message 'This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.'

Manually increasing the threshold to 20,000 items allows the view to display, showing less than 20 items in this particular case. Re-enforcing the 5000 limit causes the view to fail to display again.

I thought views were supposed to be a legitimate way of avoiding the list view threshold? How can I create the view as intended?

Answer 1

You have the option to set the list view throttling really high, however there is another option to disable the throttling altogether. We've actually disabled the list view throttling on two particular lists on our farm.

Here is a powershell script, to disable list view throttling:

$web = Get-SPWeb http://url/to/web/with/list
$list = $web.Lists[“BIG_LIST_NAME”]
$list.EnableThrottling = $false

Source: http://blogs.msdn.com/b/mimorr/archive/2012/08/28/disable-sharepoint-2010-list-throttling-at-the-list-level.aspx

Answer 2

Yes, however, your threshold also applies to queries, and creating a view that must query more than 5000 items will again hit the threshold. You can bypass this by using indexed columns. Then run your query on the indexed column, because you are querying the index. To set the "Checked out To" column as an index, go to the library settings and below the list of columns, click on Indexed Columns, create a new index and set it to that column. Now when you run your view, make sure that you have the query from the column that shows as being indexed and you should be able to make your view work within the threshold.

Answer 3

Yikes!!!! I cannot believe turning off the threshold is an accepted solution here. There is a reason why a threshold exists.

I had a similiar issue in a SPF environment. First I set Checked Out To as indexed. Here are the view filters that were entered to get the desired results:

1. Checked Out To is not equal to [leave the textbox empty]
2. ID is greater than 0

I would not recommend turning off thresholds indefintely unless you have a supercomputer running SharePoint and only two users are accessing the site. Even then... yikes!!!

Answer 4

I know this question was asked a long time ago, but i thought I'd share an answer anyway in case someone is still experiencing this problem and can't figure out why the problem occurs.

Yes, indexing a column and filtering on that column should work. However, it will not work when the filter is written like [Column] equal to (Empty) for the simple reason that an empty value can't be Indexed in the database. This causes the query to have to iterate over all the data anyway in order to find the ones that have empty values for the desired column.

I.E the problem is primarily a limitation on the database level.

Mentioned here: https://stackoverflow.com/questions/9175591/index-for-nullable-column and here: https://stackoverflow.com/questions/20687213/does-sql-server-index-null-values-in-a-non-clustered-non-unique-index

The only way around this that I have found is to not have views that are entirely filtered on empty values. If possible I use a non-empty column for the first filter field in order to reduce the result and then the next filter-field can be filtered on Empty because then the query only iterates the already filtered out result that should be less than 5000.