2

I have a CAML query of the form shown below which is called via the Sharepoint web service interface to search the entire Sharepoint site containing 600,000 documents looking for documents:

<Query><Where><Contains><FieldRef Name="References_x0020_which_x0020_can_x0020_view" /><Value Type="Text">A00000</Value></Contains></Where></Query>

The Query options I use are:

<QueryOptions><ViewAttributes Scope="Recursive" /><IncludeMandatoryColumns>False</IncludeMandatoryColumns></QueryOptions>

where A00000 is a reference number (of which we have 80,0000). This works fine in Sharepoint 2007 but breaks the 5,000 item limit in Sharepoint 2010. Any thoughts on how to improve the search?

  • Are you saying the query returns 80000 items, or that there are 80000 reference numbers? – Stu Pegg Apr 23 '13 at 11:14
0

Resource Throttling "List View Threshold" property limits the maximum number of items that a database operation can involve at one time. It is 5000 by default. You can change it in:

Central Administration -> Manage Web Applications -> Select WebApp -> General Settings/Resource Throttling -> Change "List View Threshold".

0

I encountered the same issue and found a few cases where I was able to get around the limitation by using the ID field in the filter. Look here:

http://spservices.codeplex.com/discussions/280642

Paul

0

My team recently faced the same problem about the 5k limit in SP2010. The only difference was that we were querying with Lookup columns. So after tons of research and try-this examples, this article helped a lot: http://social.technet.microsoft.com/forums/en-US/sharepointdevelopmentprevious/thread/c9f15c89-0eb6-427d-97f0-0799a442b5da. So if you can use the ID equivalent instead of the value, I think it could help. Changing the default limit is the easiest you can do and also the most popular response to the problem, but this should not be the case, and most of all - it is temporary and not a good practice.

Hope this helps!

  • People continue to post that this is not a good practice without any hard evidence why, other than the occassional "Microsoft said so". Take a look at the Workflow History list sometime. It routinely has hundreds of thousands of records in it and is quite responsive. I've had lists with a million records in them and used REST or CAML to get at the data immediately. People, do your own research. That 5k limit is well over a decade old and constantly causes problems. Any large company will have 5k items routinely. Make the machine work for you, not the other way around. Up the limit. Done. – Wade Dec 2 '17 at 4:43
  • It is NOT a good practice to change the throttle limit. The limit is in place to prevent lock escalation in SQL Server, which happens once a query returns more than 5000 items. This would lock the whole table for all sites in the DB. -> BAD PRACTICE - If you MUST, isolate the site in its own DB if it is high traffic. use indexes write better querries – Heiko Hatzfeld_MSFT Mar 27 '18 at 22:08
  • Nope. The number 5000 is not some magical, mystical number in SQL server. There are many other factors involved and this article from MSFT for SQL Server 2008 (old technology!) gets into the details. social.technet.microsoft.com/wiki/contents/articles/… From personal experience, which I have a lot, I've had multiple users all simultaneously hitting lists with hundreds of thousands of items and never had an issue. You have to use the technology the way it makes sense based on your own experience, not some theoretical unfounded limits. – Wade Nov 2 '18 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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