CAML Query : Where clause not working with View tag & RowLimit

Question

I've reached the maximum view threshold limit on a SharePoint List, with a little more than 5000 item in it.

When I raise the limit in the webapp's settings, everything is working fine, but I would like to avoid doing that, as it will ultimately affect performance.

In order to do that, I tried to set a RowLimit value (of 100 items) to my CAML Query. This limit is working, but the where clause of my query is being ignored, thus returning me the first 100 items of the list.

If I add an OrderBy clause, it gets ignored aswell.

Here is my code :

SPList list = web.Lists[new Guid("xxxx")];
SPQuery query = new SPQuery();
query.RowLimit = 100;

// My previous query, which is ignoring the RowLimit statement and is triggering the limit threshold error.
// query.Query = "<Where><Eq><FieldRef Name=\"Author\" /><Value Type=\"Integer\"><UserID /></Value></Eq></Where>";

// The current query, working with rowlimit, ignoring everything inside the view tag
query.Query = "<View Scope='RecursiveAll'><Where><Eq><FieldRef Name=\"Author\" /><Value Type=\"Integer\"><UserID /></Value></Eq></Where></View>";

SPListItemCollection listItemsCollection = list.GetItems(query);

I've tried a lot of things, for example putting the rowlimit statement as a tag inside the query, or adding a query tag inside the view tag, but nothing seems to work.

How am I supposed to use a RowLimit and a Where clause at the same time ? If that's not possible, what's the best approach with my threshold limit issue ?

Answer 1

In order to avoid issue with the >5k item query limit you need to create index on the columns you are using in the Where clause.

Go to library settings from there you will have link to indexed columns create index and test the query (you might need to increase the query limit in the CA for the purpose of creating index and then reduce it back to 5k)