What are good ways around the List View Threshold?

Question

I'm trying to use data from a SharePoint list in SSRS (Reporting services).
The report isn't too big - only a few dozens rows, but for each row I need to add data from a second list, which (currently) contains 7000 rows. On SharePoint 2007 I've successfully done similar things, but I can't get it to work on SharePoint 2010.
I've tried:

• Using owssvr.dll to get list data as xml (my favorite).
• Using the new ListData.svc service.
• Using the web services (Lists.asmx, GetListItems).

I keep hitting the same wall - I can't get any data because the list is too big! I get a similar error to:

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

This is discouraging. I've read quite a lot on the subject and can't see a good way around it besides increasing the threshold on the whole application.

Is there an easy way around this new limitation? Is there a hard way?
I'm looking for a "best practice"... It doesn't look I can keep using any of these methods reliably. The only "good" solution I can think of it to use SSIS to copy all rows to a database, but that seems like an overkill...

Answer 1

In Central Administration you can disable the list threshold completely, or set a higher limit. You can also set a time window when the limit is disabled and run your report service then. Go to the web application management page in central administration and select the Resource Throttling option from the General Settings button in the ribbon.

If you CAML query a list with a filter such that it returns less items than the threshold that should also work.

Answer 2

SSRS 2008 R2 has a SharePoint data source option that was added. The SharePoint data source will perform a series of 2000 max item queries until the entire query has been completed. This allows you to overcome the list view threshold and not majorly affect performance. PowerPivot performs this even better. It will query the lists 1000 rows at a time and cache the results in an excel tied workbook. PowerPivot also allows for creating lookup columns and relationships. SSRS can then query a PowerPivot data source inside SharePoint like it is an analytics cube.

Answer 3

You can use the ContentIterator class to bypass the list view threshold, but you would need to resort to custom code to fetch the data.

Documentation: "Provides methods to iterate list items, lists, sites to regulate the amount of data that is transferred (i.e., to avoid throwing a SPQueryThrottledException)."

Answer 4

To avoid the listview threshold, you need to index columns in your library and query against that column. So long as the number of rows returned in your query is below the listview threshold, you won't get the exception.

As an example, I hit this problem when trying to retrieve all the folders in a SharePoint library. The number of folders was well below 5000 (the default listview threshold), but with over 20,000 items in the library, I was triggering the exception.

The solution was to index Content Type (accessible through SharePoint library settings) and use the following query:

<Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='ContentTypeId'>0x0120</Value></BeginsWith></Where>

You just have to figure out how to ensure your query will return less than the listview threshold.

Answer 5

If you have it available, perhaps consider combining the search API and a web service? Search can index and return a much larger number of fields. Its a roundabout way of getting at the data but it is supportable.

Answer 6

Try to look at

Developer Best Practices Resource Center

Best Practices with SharePoint Foundation

Handling Large Folders and Lists

Answer 7

you could define a "service account" and give that account site collection administrator rights. Use that account to query the data. I believe they can query 20.000 items (or was it 5000?!) at once.

If that doesn't work:

• put an indexer on a list column (uid?)
• Use powershell to increase the listviewtreshold

Keep in mind that the 5000 items boundary has a purpose. query of 2200 items may decrease performance, a query of 5000+ may even lock the table in the database!