10

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.

4
  • have you checked your 14/logs ?
    – GillouX
    Jun 16, 2011 at 13:09
  • Can you reproduce the issue on a normal Number field?
    – Stu Pegg
    Jul 21, 2011 at 12:07
  • We have exactly the same issue and don't know how to solve it. Callum, how do you solve it? Sep 16, 2011 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, 2011 at 4:04

4 Answers 4

2

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

3
  • 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, 2011 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, 2012 at 8:48
  • Yeah.. not sure what I was on when I posted that comment. Derp.
    – James Love
    May 4, 2012 at 21:02
1

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?

0

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>
1
  • 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, 2011 at 4:04
0

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

2
  • 1
    Can you add some sample code to the answer to make it more userful? :) Jun 25, 2013 at 5:59
  • Value type = number
    – Roman
    Sep 7, 2017 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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