Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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:

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...

share|improve this question

9 Answers 9

up vote 4 down vote accepted

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.

share|improve this answer
1  
This is what we ended up doing. We figured the threshold is there to warn us about potential problems, but we really had a system with many items. I would also note it is possible to disable throttling per list: SPList.EnableThrottling (using code or sp manager) –  Kobi May 2 '12 at 18:59

Try to look at

Developer Best Practices Resource Center

Best Practices with SharePoint Foundation

Handling Large Folders and Lists

share|improve this answer
    
I'm sorry, but these really don't help much. The first two are very general, and the third missed the point a little. First - I'm not looking for a code solution - I had it working in moss 2001 without code. Second - the article recommends enabling throttling (which I already have, it's a default afaict). I'm looking for a good way to integrate SharePoint data despite of throttling. Thanks anyway. –  Kobi Jul 19 '11 at 10:20

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!

share|improve this answer
1  
Thanks. It isn't that simple, I'm afraid - When I run a query by code I can specifically request to use SPQueryThrottleOption.Override, and then I get the higher limit of 20000. It isn't automatically served to administrators as the name suggest. See also these two posts. 20000 should be enough, and I may write a solution that deployed such a service, but I'm hoping for an OOTB solution, like what worked for me on 2007. –  Kobi Jul 19 '11 at 11:42

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
Thanks! That's a good tip, and I'm sure it will help someone. –  Kobi May 2 '12 at 18:53

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)."

share|improve this answer

While the choice for this questioner is to raise the limit, it's there for a good reason: to avoid creating poorly performing sites. As your site grows in content, or it becomes a template for other sites, you'll find that every user hitting the pages that require "relaxing' this restriction will cause your site to slow down. The more users, the faster that performance hit will occur.

I blogged about this and a set of solutions at http://squarepoint.blogspot.com/2013/05/creating-performant-sharepoint-apps.html. There might be some suggestions in there that could help you avoid the problem and keep SharePoint operating without the performance penalty.

share|improve this answer

You could, also, disable the EnableThrottling property for the specific list with too many items as Michael Morrison wrote here:

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

Good luck!

share|improve this answer
    
Thanks! Actually, I mentioned that in a comment, but it might be useful to have a script ready. –  Kobi Aug 6 '13 at 4:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.