I have a SharePoint List that contains approx 14K items. I have added a link to a page that redirect to a view in this list with a predifined filter on one field (filter added to the URL).

The view is taking approximately 3 minutes to load (there is paging at 30 items per page). Interestingly, without the filter in the query it only takes few seconds.

I have run the Running DBCC Check with rebuild but this did not have any effect. The obvious answer would be to add a SharePoint index to this field. But this issue is just one example that I have. I don't wont to add indexes to all fields.

The AllUserData table has approximately 800,000 rows.

I would like to know whether this a known issue, or whether I could apply some performance optimization to resolve this issue.

Another problem that I have is that when I use the "Show Filter Options" in a SharePoint column, it takes several seconds to load the filters, and sometimes it even times out.

Any assistance would be greatly appreaciated.



2 Answers 2


There are many ways to get your query optimized (for example in batches). My recommendation is to use some SPQuery object properties. Here goes an example

SPQuery query = new SPQuery();
string view = "<FieldRef Name='Title'/><FieldRef Name='Field1'/><FieldRef Name='Field2'/>"

query.ViewFields = view;
query.ViewFieldsOnly = true;

query.IncludePermissions = false;
query.RowLimit = rowLimitNumber; //for example 1000
query.IncludeAllUserPermissions = false;
query.IncludeAttachmentUrls = false;
query.IncludeAttachmentVersion = false;
query.IncludeMandatoryColumns = false;
query.RecurrenceOrderBy = false;

query.Query = queryString;

In the viewFields you add the fields that you want to set in your query.

Hope this helps. Cheers


I wrote a blog on how to analyze your current access of SharePoint List Items and how to optimize both code but also SharePoint List Configuration. Hope you find this useful: Optimizing SharePoint List Access Performance

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.