SharePoint 2010 CAML Query fails when indexed columns used

Question

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.

Answer 1

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

Answer 2

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?

Answer 3

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>

Answer 4

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