Tuesday, March 25, 2014

SharePoint 2010: The view cannnot be displayed because it exceeds the list view threshold

Problem

You have a high-volume transactional SharePoint 2010 list that you use to track various items, such as, for example, service tickets.  You have a dashboard that provides filtered views onto this list.  The dashboard uses List View web parts to provide these filtered views.  Filtering is performed using simple values in list fields, such as Persons (usernames), text, etc.  When the number of service ticket items in this list exceeds the web application's List View Threshold, the dashboard web parts display errors:
This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.
To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold.
Learn about creating views for large lists.
None of your users can use the dashboard page to view tickets.

A temporary solution to this problem is to raise the web application's List View Threshold using Central Administration (see Note below).  This will enable your users to resume using the dashboard page, while you work on the problem.  A more permanent solution is to configure list column indices.  The permanent solution is discussed next.

Solution
  1. Logon as site collection administrator
  2. For each List View web part that exhibits the above error, identify the fields on which it is filtering the list.
  3. Navigate to the list's Site Settings.
  4. Scroll down below the Columns section until you find the Column options below it.
  5. Click Index Columns
  6. Click Create a new index.
  7. Select a Primary Column from the dropdown and do not select a Secondary Column.  The column that you select should be one of those columns that you found in

    Configure a single index if the problematic web part filters on one column. Configure a compound index if the web part filters on more than one column.
    Note: do not create a single index on a column that is filtered for blank values.  I have found that, if you do, the blank value filter will no longer function. This is not the case though if you create a compound index on this column.
  8. Click Create.  A new index will be shown.
  9. Using a standard domain account, go back to the dashboard page and press CTRL-F5.  The affect will be immediate.
  10. If the web part continues to present the same error, configure another column as index - taking the column from the list of such columns you found in step 3.  It may take just one, it may take several together.  Or try configuring a different compound index. You'll just need to test to determine the right combination.
  11. Once you have successfully resolved this for one List View web part, repeat for the next one, and so on.  For additonal information on the relationship between resource throttling and indexing, see the MSDN article Query Throttling and Indexing.
References
Notes
  • To configure the List View Threshold, go: Central Administration > Application Management > Manage web applications > [select the target web application] > General Settings > Resource Throttling.
  • I was able to resolve one threshold issue through some creative filtering.  A list view web part presented rows where a certain column had blank values.  Indexing that column caused the view to fail and not present any rows having this column blank values.  Using a compound index composed of this column and another column got around the threshold issue, but did not resolve the failure to show rows having a blank column value.  I then created an index on Created, and then I created a new view having a compound filter, where it filtered for both the blank value and for Created >= [Today]-7.  This seems to have efffectively introduced an indexed column into the view, Created, and thus resolved the threshold issue.

2 comments:

Anonymous said...

We had an issue today with a view and the Note under Point 7 fixed it. But was wondering, if we have any explanation, as to why we need a Compound Index if we are filtering for blank values?

Al said...

The solution I found here was developed through trial and error. Regretably, I don't know why it works.