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 have an SPQuery that has been working fine until my list hit 500 items:

q.Query = "<Where><Eq><FieldRef Name='DefectID' /><Value Type='Counter'>" + DefectID + "</Value></Eq></Where>";

This was successfully returning 1 item where DefectID matched my provided value.

Once the list hits 500 items, the query returns 0 items, no error message, no exception. If I delete 1 item and take the list down to 499 items, it works again.

The list had individual item permissions, but as part of my debugging I have reset the role inheritance of the list and also every item in the list.

I have tried providing a row limit of 2, and 2000, same result.

Have tried Query override throttling, same result. Changed the web app throttle limits to way above my lists number of items, and change the large query window to 24hrs, same result.

The column I am querying has an index on it (I am expecting about 30k+ items per year in this list). If I delete the index the query then works when items in the list are > 500. If I create the index again, the query then returns 0.

share|improve this question
    
have you checked your 14/logs ? –  GillouX Jun 16 '11 at 13:09
    
Can you reproduce the issue on a normal Number field? –  Stuart Pegg Jul 21 '11 at 12:07
    
We have exactly the same issue and don't know how to solve it. Callum, how do you solve it? –  Kai Sep 16 '11 at 14:11
    
As an update, I logged this with Microsoft who said they would look into it but didnt hear back. I have given up on this and removed the index from the list. –  Callum Sep 22 '11 at 4:04

4 Answers 4

I've just been looking around this to see if I could help and came across this article from Microsoft: http://msdn.microsoft.com/en-us/library/ee558807.aspx. It seems to indicate that only under certain conditions are indexed columns used to your advantage. Admittedly this is a slightly different scenario from what you've described (it uses the ContentIterator class) but thought it may add to the mix. :)

share|improve this answer
    
Where does it say indexed columns can't be used? I've never had a problem with an indexed column in a CAML query... –  James Love Nov 29 '11 at 14:46
    
If you re-read what I said, I don't say indexed columns cannot be used, just that only under certain circumstances are they used to your advantage. –  Steve May 4 '12 at 8:48
    
Yeah.. not sure what I was on when I posted that comment. Derp. –  James Love May 4 '12 at 21:02

I solved a similar problem by switching my CAML comparison value type query against the WorkflowInstanceID from Type="Text" to Type="Guid". Perhaps you could change your value type to Integer like so:

    q.Query = "<Where><Eq><FieldRef Name='DefectID' /><Value Type='Integer'>" + DefectID + "</Value></Eq></Where>";

It seems an indexed Lookup is better queried as an Integer: http://blackninjasoftware.com/2009/05/12/why-should-indexing-a-sharepoint-field-break-your-caml-query/

So, why not try the same solution for your Counter field as well?

share|improve this answer

Check the RowLimit on the query. If not set, then set it and see what happens.

The RowLimit Gets or sets a limit for the number of items returned in the query per page.

Ex.

<RowLimit>600</RowLimit>
share|improve this answer
    
Hi mmcglynn, The issue is not the number of rows being returned. My query should be returning only 1 row, but once there are 500 items in the list, it no longer works at all (even queries that worked fine with 499 items). –  Callum Sep 22 '11 at 4:04

We used 'Number' for the type and this resolved the 500 limit issue.

share|improve this answer
1  
Can you add some sample code to the answer to make it more userful? :) –  Robert Lindgren Jun 25 '13 at 5:59

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.