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?

user16549
  • 21
  • 1
  • 2

4 Answers4

0

If you have a few fields that you filter on, consider creating indexes on them. https://support.microsoft.com/en-us/office/add-an-index-to-a-list-or-library-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0 Filters will help you overcome the 5K issue. (If you already have more than 5K items, you will have to create the index with the throttle disabled)

dinos.kon
  • 189
  • 8
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".

Andrew
  • 915
  • 6
  • 10
  • 2
    Please don't post answers in bulk. You have posted the identical answer to http://sharepoint.stackexchange.com/questions/66529/ and http://sharepoint.stackexchange.com/questions/66573/. – SPDoctor Apr 23 '13 at 19:09
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

Paul T.
  • 903
  • 4
  • 6
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!

tbatolova
  • 19
  • 4
  • 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. – Newclique Dec 02 '17 at 04: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. https://social.technet.microsoft.com/wiki/contents/articles/19870.sql-server-understanding-lock-escalation.aspx 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. – Newclique Nov 02 '18 at 16:23