List Performance

Question

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.

Thanks,

Joseph

Answer 1

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

Answer 2

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