SharePoint 2010 - Query to lists having over 5,000 items

Nov 23, 2011 at 8:51 PM

I'm hopping someone has encountered this issue and maybe give me some directions/suggestion as to how you handled this...

In preparing to migrate to SP2010, I have found that it is almost impossible to query a list, through webservices, that contains over 5000 items. SP2010 seems to have introduced a threshold of 5000 items that can be return (at the server level; I'm not part of the IT group), but even when your filter is assured that the return set is smaller than 5000 items, it still does not work, even if you use Indexed columns in your filter.  A search on google has found a few posting on this, none however document a way good way to get around it... other than increase the threshold, give user "supper" user permissions or run queries off hours.

This behavior, however, does not seem to be consistent either... If I use the ID field in the filter, then most of the time I do get back a result set. Try to use a <Or> right after the <Where> clause and it will fail.  but if you start off a query with <And> and include the ID field in the field, then you can get back just about anything as you did back in SP2007...

Here are some examples that fail:

Sample 1:

 $().SPServices({
        operation:	"GetListItems",
        async:	false,
        listName:	"Documents",
        CAMLQuery:	"<Query><Where>"
        +        "<Eq>"
        +		"<FieldRef Name='Release'/>"
        +		"<Value Type='Lookup'>45.0</Value>"
        +	"</Eq>"
        +	"</Where></Query>",
        CAMLRowLimit: 10,
        completefunc: function(){}
    })

Sample 2:

(Any filter that starts with <Or>)

$().SPServices({
        operation:	"GetListItems",
        async:	false,
        listName:	"Documents",
        CAMLQuery:	"<Query><Where>"
        +       "<Or>"
        +	"<Eq>"
        +		"<FieldRef Name='ID'/>"
        +		"<Value Type='Counter'>6219</Value>"
        +	"</Eq>"
        +        "<Eq>"
        +		"<FieldRef Name='Release'/>"
        +		"<Value Type='Lookup'>45.0</Value>"
        +	"</Eq>"
        +       "</Or>"
        +       "<OrderBy>"
        +           "<FieldRef Name='Release' Ascending='True' />"
        +        "</OrderBy>"
        +	"</Where></Query>",
        CAMLRowLimit: 10,
        completefunc: function(){  }
    })

Here are some samples that seem to work:

Sample A

(use ID field only in filter... not the most useful query)

$().SPServices({
        operation:	"GetListItems",
        async:	false,
        listName:	"Documents",
        CAMLQuery:	"<Query><Where>"
        +	"<Eq>"
        +		"<FieldRef Name='ID'/>"
        +		"<Value Type='Counter'>6219</Value>"
        +	"</Eq>"
        +	"</Where></Query>",
        CAMLRowLimit: 10,
        completefunc: function(){}
    })

 

Sample B:

(Wrap all filters in a <And> statement and insure that one of your logicals is always true.)

    $().SPServices({
        operation:	"GetListItems",
        async:	false,
        listName:	"Documents",
        CAMLQuery:	"<Query><Where>"
        +    "<And>"
        +       "<Or>"
        +   	    "<Eq>"
        +	    	"<FieldRef Name='ID'/>"
        +   	    	"<Value Type='Counter'>6219</Value>"
        +	        "</Eq>"
        +           "<Eq>"
        +		"<FieldRef Name='Release'/>"
        +   		"<Value Type='Lookup'>45.0</Value>"
        +           "</Eq>"
        +       "</Or>"
        +       "<Neq>"
        +	    "<FieldRef Name='ID'/>"
        +	    "<Value Type='Counter'>0</Value>"
        +   	"</Neq>"
        +   "</And>"
        +   "<OrderBy>"
        +       "<FieldRef Name='Release' Ascending='True' />"
        +   "</OrderBy>"
        +   "</Where></Query>",
        CAMLRowLimit: 10,
        completefunc: function(){}
    })

The last example seems to be the "work-around"... however, what if I'm just getting around something Microsoft may find to be a Bug.... I'm hopping that someone in this community of expects has encountered this with their use of SPServices and can provide some more information.

Thanks in advance.

Paul T.

 

 

 

Coordinator
Dec 4, 2011 at 7:25 PM

Paul:

Sorry I missed this when you posted it. SharePoint 2010 has a list throttiling capability that kicks in at 5000 items by default, I believe. It's a setting which you can change, but I can't recall on what level.

One suggestion, thoiugh, would be to reconsider your approach if you're trying to get 5000+ items back from the Web Services, as performance is likelty to be an issue, especially with older or underpowered client machines.

M.

Dec 4, 2011 at 8:01 PM

Thanks for the reply...

This List is in fact over 5k items, however, the application I developed is not attempting to get that many items out... in fact, I first noticed this when one of my pages was only trying to get a single row from the List.  All of the queries in my application normally return less than 20 items... they use different criteria to narrow the list of matches (example: Sample 1 above would have returned about 10 rows).  It seems that unless a query is done with ID in the filter, there is no other way to get data out (outside of the 'work-arounds' I listed above... which I am reluctant to implement until I can confirm that this is expected behavior from SP... Will use our IT group to confirm.)

The Throttling you speak of is at the server level, which I don't have permission to and is set across the entire server farm.   I have initiated a conversation with our IT team, being that I was invited to test out existing sites on SP2010... They may open a ticket with microsoft to ask for direction... They are currently unwilling to set this value higher due to the recommendation from microsoft's documentation (to leave it at 5k).

 

Paul.

Dec 23, 2011 at 4:17 PM

Wanted to provide feedback to others on this issue...

I had my company's IT group open a support ticket with Microsoft on this issue (Case # 111121467296419)... Support was able to reproduce the issue and in turn acknolodge that once a List goes over 5,000 items, there is no way to retrieve items from the List via webservices (I don't know about the object model)... unless, the server administrator lifts the threshold on the specific List (since they are most likely unwilling to lift it across the entire SharePoint deployment).

Microsoft pointed us to this article: http://msdn.microsoft.com/en-us/library/ff798465.aspx

The work around, as detailed there, is to "Override the list view threshold for an individual list by setting the SPList.EnableThrottling property to false.". The article, however, does not talk about how this can be done...  Here is the code my folks ran on the Console at the SP server (replace site URL and list with actual values for your site):

  private static void DisbaleListThrottling()
        {
           using (SPSite spSite = new SPSite("http://YourSiteNameHere.com/TeamSite/"))
            {
                using (SPWeb spWeb = spSite.OpenWeb())
                {
                    SPList spList = spWeb.Lists["MyList1"];
                    spList.EnableThrottling = false;
                    spList.Update();
                }
           }
        }

Once the threshold was lifted, everything started working again for me... Hope others find this information helpful.

 

Paul.

 

 

 


 

 

 

Apr 5, 2013 at 5:26 PM
Hi Paul,

We have run into a similar issue with a large list (~17000 items). I was wondering what the performance implications are of disabling the throttling for one list. Have you noticed any effects on performance from querying such a large list, though you are only returning 20 items at a time,? In addition, we were considering using 4 different lists to get around this issue, as we are also limited by IT. I mention this because I'm wondering about performance implications of querying 4 smaller lists vs 1 large list.

Background: Migration from SP 2007 where querying the large list was not an issue to SP 2010 where it is.

Thanks in advance,
William
Apr 5, 2013 at 6:51 PM
William,
Our migration to SP2010 was placed on hold, but in the test site, I did not notice any degradation in performance... It behaved the same as in PS2007. I'm not a "system's administrator" so I cant speak for the server (like SQL server, etc)... but.. the recommendation above was strait out of the Microsoft support so it "should" be ok...

Like you, following this episode, I started to think about how I can span the data across multiple lists... this increases the complexity of the client side application, but seems to be the only way around the issue (unless IT is able to do the above on all lists that grow this large).

And... like I mentioned in my initial post, Sample B (work-around) seems to work... but makes your code/filters ugly and hard to maintain... :)

Paul.
Apr 15, 2013 at 8:39 PM
Nice EnableThrottling Property. Nice Tip!
Dec 28, 2013 at 11:26 PM
HI Paul,

on the spquery object there is a parameter to let you override the throttle mode. SPQuery.

Can't you use this with the spservice / web service too ?

Kind regards
Stefan
Dec 29, 2013 at 4:26 PM
Thanks Stefan. That might be another option. Since I have not access to servers or admin I can't validate.

If anyone here tries this option - please report back.

Paul.



--

_________
Paul T

Dec 29, 2013 at 6:07 PM
Stefan, this property is only available from managed code both sandboxed and full trust, but not the web services.
Dec 29, 2013 at 6:16 PM
Paul, the reason why part of the queries succeed and others fail is because you use non-indexed fields in query criteria. ID is indexed by default, while the Release lookup is something you should substitute with an indexed field. see list settings page to set up field indices.
Dec 29, 2013 at 6:26 PM
When I hit this problem, the use of indexed fields did not seem to make a difference. I think Release was an indexed Field in that list. Even the use if ID will fail if you use an <Or> agregate in your query.



--

_________
Paul T

Coordinator
Jan 2, 2014 at 6:48 PM
In my case (the one that prompted the recent comments), I decided to simply store the data in a txt file containing JSON rather than trying to force it into a list.
Thanks to @bpmccullough for the suggestion on Twitter.

Blog post coming!

M.
Jan 2, 2014 at 8:41 PM
:) Cool. Thanks Marc for posting back.


_________
Paul T


Jan 2, 2014 at 8:50 PM
Cannot wait to read it because I might get to that limit at a customer soon.

Thanks for the follow up.
Coordinator
Jan 3, 2014 at 1:16 PM
BTW, I was uploading some data from an Excel Spreadsheet. There's a Catch-22 there: if you upload from the spreadsheet you can't index a column (assuming you're over the 5000 item limit), and if you start with an existing list with an index you can't upload from Excel. It would be really good if there were a middle alternative.

M.
Jan 3, 2014 at 2:39 PM
Marc,

Re: "...f you start with an existing list with an index you can't upload from Excel. "

How where you doing it? Data sheet view and Copy and Paste from excel? I thought that worked for me a few years ago (SP2007). Or maybe turn off index, import it, turn index back on?



--

_________
Paul T

Coordinator
Jan 3, 2014 at 3:03 PM
2007 is no problem; create lists as big as you want, though they may not perform well. There was no throttling then. So, yes, in 2007, I used to (and still do) a lot of copy/paste into Datasheet view.

In my specific case this time, it was 19000 cities and states. Copy/paste simply wouldn't work. It was constantly hanging up.

M.
Nov 18, 2014 at 2:37 PM
Hi all -

resurrecting this old thread because I've run into this issue lately (that is, not being able to return results around the 5,000 item threshold), and have finally found a workaround!

Quickly for context; I have a list which has grown to 25,000 items in 2 weeks - we have worked out an archiving process to keep the list at around 65,000 items over time - still too large but given the circumstances it is what we have to deal with. I need to return a subset of items, but which will still be between 10K and 12K items. We reached the threshold very quickly, and I could get nothing out of the list as a result.

Fast forward a few intense days of experimentation, and we have a working solution!

There seem to be several important criteria for both the setup of the list, and the query itself. all of these appear to be mandatory.
1) Indexes must be set up on the list fields for which we are querying

2) The CAML query must make use of an indexed field in its first statement. In other words, if the query filters first on, "QuestionID", as it does ours; the QuestionID field needs to be an indexed field. I have added indexes for all the fields we query on as a precaution, and for performance.

3) Specify a RowLimit ("CAMLRowLimit" in SPServices) of 0

this ensures that the returned results will not be artificially truncated by the default View

4) There MUST be an OrderBy clause included. Additionally, two attributes I didn't know about until today must be included:
UseIndexForOrderBy='TRUE' Override='TRUE'

These appear to 'take over' the query and force the output to be organized by the field supplied. In our case, since we are manipulating the output using jquery anyway, we just use 'ID' (which is an indexed field)

example:
<OrderBy UseIndexForOrderBy='TRUE' Override='TRUE'><FieldRef Ascending = 'TRUE' Name='ID' /></OrderBy>


This appears to be the missing ingredient in our potion - now I can return 12,000 items in ONE call - no paging!

I am also looking into using Paging so that our query doesn't take so long - this one takes a noticeable second or two to return results (big data set to process, plus we are possibly locking the table). In our case this is ok because the use case for even pulling this data at all is very small, and the lag requires a user click, so no UX implications.

I'm so happy we found this today - possibly even larger results are possible. I'm happy with the 12,000 as it is.

Carry on!

-Rob D
Nov 19, 2014 at 10:02 AM
Rob,
That excellent work. I have this thread bookmarked because I keep coming back to it from time to time. The information you posted helps even more to understand what might be going on behind the scenes.
One thing you did not mention: what version of SP? And if sp2013, is it on premises? Or on O365?

Thanks again for sharing.
Coordinator
Nov 19, 2014 at 11:21 AM
Yes, this is excellent, Rob!

I'd also be curious about the version of SharePoint. The other questions are around whether you happen to have hit on a hack that works just in your case or if it is generally usable.

But this is really great info!

M.
Nov 21, 2014 at 6:47 PM
Edited Nov 21, 2014 at 6:48 PM
Thank you so much Rob D!!!
After litterally weeks of searching, you helped me find the solution to my problem.
Using the <OrderBy UseIndexForOrderBy='TRUE' Override='TRUE' /> was the key.
But in my case I had to use it on its own without any fields specified inside of it.
Here is my code to get the highest Branch Specific ID field, for a given branch field, without running into the list view threshold.
(Branch and Branch Specific ID are both indexed columns)
string rowLimit = "1";
System.Xml.XmlElement query = xmlDoc.CreateElement("Query");
System.Xml.XmlElement viewFields = xmlDoc.CreateElement("ViewFields");
System.Xml.XmlElement queryOptions = xmlDoc.CreateElement("QueryOptions");
            
query.InnerXml = "<Where><Eq><FieldRef Name=\"Branch\" /><Value Type=\"Integer\">146</Value></Eq></Where><OrderBy UseIndexForOrderBy='TRUE' Override='TRUE' />";
viewFields.InnerXml = "<FieldRef Name=\"Branch_x0020_Specific_x0020_ID\" />";
queryOptions.InnerXml = "";
            
//Dont bother supplying a view because it will be completely overridden by the query parameters being passed.
System.Xml.XmlNode nodeListItems = listService.GetListItems(listName, null, query, viewFields, rowLimit, queryOptions, null);
Nov 25, 2014 at 3:39 PM
Edited Nov 25, 2014 at 3:43 PM
Hi all -

Apologies for leaving out some important details!

I have had the opportunity to test this scenario out on both 2010 and 2013 environments (all on premises - I have not had to try this with my one customer who uses O365), and it seems to work fine in both. In 2007 obviously this wasn't a problem.

The specific example I gave above is a 2010 environment. Whenever possible, I try to use the search feature to return large lists of items like this - create a scope just to my single list and then search with a very precise query directly, but in the example above I was trying to rewrite something from scratch and just didn't have time to get everything set up. It can also take weeks for the Farm Admin to turn on features and get search items set up (I'm just a developer for this particular customer), and it wasn't worth it.

Given the time and tools, however - search is several order of magnitudes faster for such a large list.

So necessity creates our solution. Glad to hear others have had success with this too!!!
Nov 26, 2014 at 2:14 PM
Thanks for sharing, however I'm not able to make it work....

See my request below and tell me where it could be wrong:
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
     <GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
       <listName>My List</listName>
       <viewName></viewName>
       <query>
         <Query>
           <Where>
             <Gt><FieldRef Name="ID"></FieldRef><Value Type="Number">1</Value></Gt>
           </Where>
           <OrderBy UseIndexForOrderBy="TRUE" Override="TRUE">
             <FieldRef Name="ID" Ascending="true"></FieldRef>
           </OrderBy>
         </Query>
       </query>
       <viewFields>
         <ViewFields Properties="True"><FieldRef Name="ID"></FieldRef></ViewFields>
       </viewFields>
       <rowLimit>0</rowLimit>
       <queryOptions>
         <QueryOptions>
           <DateInUtc>False</DateInUtc>
           <Paging ListItemCollectionPositionNext=""></Paging>
           <IncludeAttachmentUrls>True</IncludeAttachmentUrls>
           <IncludeMandatoryColumns>False</IncludeMandatoryColumns>
           <ExpandUserField>False</ExpandUserField>
           <ViewAttributes Scope="Recursive"></ViewAttributes>
         </QueryOptions>
       </queryOptions>
     </GetListItems>
   </soap:Body>
</soap:Envelope>
Thank you
Nov 28, 2014 at 5:16 PM
You havent' given us much to go on here. First of all, your request doesn't seem to be using SPServices at all, so I can't guarantee it's going to work the same way, and I think it would be a bit more appropriate if you used the tools this site is intended for when asking for help.

Check the case on your <FieldRef Name="ID" Ascending"="true" >statement = I believe this may need to be "TRUE" - I think it is case sensitive.

Also, you have not given us any indication of your environment, what version you are using. Your example is only using the ID field for its query, which differs from my example in that we were using a specific field which had been indexed as well. The ID field is indexed, yes, but this is not the same scenario. Add an index to one of your fields and then try to get it to work with that field first. Also, the "ID" field is technically of field type "Counter" (although I think number should work as well).

try some of that stuff and report back.
Nov 28, 2014 at 6:39 PM
Thanks for the reply.

SPServices or not, it's the same at the end. SPServices creates the SOAP query. I copied the SOAP query that is sent to the server and that I've built myself to test all the different parameters. SPServices is just a way to build it for you.

"True" is not case sensitive, at least this query works if I have less than 5,000 items.

I've also tried to use another indexed column, but same result....

I use SP 2010.

Can you share your full query that is sent to the server as I did, so I could try to exactly replicate it, and maybe I'll be able to find something that is different.
Or at least can you share the exact command you use with SPServices, with all the parameters, so I can try it on my side?

Thank you !
Dec 1, 2014 at 8:17 PM
Sure thing - here's the full query and the SPServices call... nothing super fancy in the initial call really! Note that I'm not calling the ID field first, I'm using one of our custom fields which has been indexed. I was not able to use the ID field alone in order to return results.
var CamlQ = "<Query><Where><And><Eq><FieldRef Name='QuestionID' /><Value Type='Number'>" + QID +  "</Value></Eq>" +
                "<Geq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Geq></And></Where><OrderBy UseIndexForOrderBy='TRUE' Override='TRUE'><FieldRef Ascending = 'TRUE' Name='ID' /></OrderBy></Query>";


 

                $().SPServices({
                    operation: "GetListItems",
                    async: false,
                    listName: "MyListName",
                    CAMLQuery: CamlQ,
                    CAMLRowLimit:0,
                    CAMLQueryOptions: "<QueryOptions><ViewAttributes Scope='RecursiveAll' /></QueryOptions>",
                    CAMLViewFields: "<ViewFields><FieldRef Name='QuestionID' /></ViewFields>",
                    completefunc: function (xData, Status) {
        //we do stuff here
}
});


Dec 2, 2014 at 9:57 AM
Edited Dec 2, 2014 at 10:14 AM
Thanks! That helped me a lot :-)

So it seems the mandatory criteria are:
  1. To have "ID > 0 AND Another_Index_Column_Filtered" in the WHERE Clause (so at least two filters), and then we can add some other WHERE (even the not indexed columns)
  2. To have an ORDERBY on an indexed column
  3. To use the attributes UseIndexForOrderBy='TRUE' Override='TRUE' in the ORDERBY
Note : in the WHERE clause I tried ID > 0 AND Date >= '2014-08-02', it worked. I also tried ID > 0 AND Date <= '2014-10-31', and it worked. However ID > 0 AND Date >= '2014-08-02' AND Date <= '2014-10-31' doesn't work ^^
May 10 at 3:38 PM
FYI, it doesn't seem to work in Sharepoint 2013 On-Premise... :-(
Coordinator
May 10 at 4:19 PM
@AymKdn:

Are you looking for help on something here? What doesn't work in 2013?

M.
May 10 at 9:50 PM
@sympmarc, I'm referring to this quote from @rdoyle78 « I have had the opportunity to test this scenario out on both 2010 and 2013 environments (all on premises - I have not had to try this with my one customer who uses O365), and it seems to work fine in both. In 2007 obviously this wasn't a problem.»

If you look at my message in December 2014 (just above), the trick from @rdoyle78 was working on SP2010. Now I'm using SP2013 so I tried it again, but it doesn't seem to work anymore, so I have to deal with the 5,000 items limit again. Except if there is another trick that permits to query a list without restrictions?
Coordinator
Jun 7 at 5:50 PM
AFAIK, there's never been a "trick" to get around the 5000 item limit other than making multiple calls. It depends on where you are hitting it. Using indexed columns can help, as long as the first column in your query is indexed and the filter will ensure fewer than 5000 items are returned.

M.