Slow Query Duration

When working with custom code that manipulates lists that contain a large number of fields, the ULS logs will likely be littered with the following entries:

A large block of literal text was sent to sql. This can result in blocking in sql and excessive memory use on the front end. Verify that no binary parameters are being passed as literals, and consider breaking up batches into smaller components. If this request is for a SharePoint list or list item, you may be able to resolve this by reducing the number of fields.

followed by:
Slow Query Duration: [time in milliseconds]

Slow Query StackTrace-Managed: [complete stack trace]

Note: This one’s important as you can look at the stack and identify the exact area of code that initiates the slow query log entries.

and finally:
SqlCommand: [SQL statement responsible for the log entry]

The above can also apply to some of the out of the box WebParts, such as the Content Query WebPart, when configured to query a list with a large number of fields.

But for this post, I’ll concentrate on the code side.

Typically, the above will occur when working with a list item that belongs to a list with numerous fields. For example, take a look at the following code segment:

public SPListItem GetItem(SPList list, int itemId)
    SPListItem item = list.GetItemById(itemId);
    return item;

A very simple method, but the call to GetItemById will result in the ULS logging the slow SQL query messages. Now, SQL does perform caching, so the Slow Query Duration message indicates how slow the query took to execute. If this is the first time a list has been queried for an item, this may be reasonably high but subsequent calls should be a very low millisecond value. So SharePoint still logs the fact that a large block of SQL was sent and that it was slow, when in fact, caching results in the query executing very quickly.

Anyway, how to rewrite your code to stop these log entries in the first place?

You have two options:

1. Replace the GetItemById call with a CAML query.
2. Replace with the GetItemByIdSelectedFields method.

When replacing with a CAML query, the aim here is to return the list item by the id and specify what fields should be returned. So, here you only want to return the fields that you are interested in, not every field associated with the list.

public SPListItem GetItem(SPList list, int itemId)
    var qry = new SPQuery
        RowLimit = 1,
        Query = string.Format("<Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>{0}</Value></Eq></Where>", itemId),
        ViewFields = "<FieldRef Name='ID' /><FieldRef Name='Author' /><FieldRef Name='CustomField' />"
    SPListItemCollection items = list.GetItems(qry);
    return items[0];

The above CAML query will result in the same list item being returned but only with the fields specified by the ViewFields property. As long as the number of fields defined by this property are not excessive, the ULS entries will stop appearing for this statement.

When using GetItemByIdSelectedFields, the implementation is similar:

public SPListItem GetItem(SPList list, int itemId)
    string[] fields = {"ID", "Author", "CustomField" };
    SPListItem item = list.GetItemByIdSelectedFields(itemId, fields);
    return item;

Performance wise all three queries execute in a very similar timeframe, where the first usage against a list will generally be slower than subsequent calls. From my tests, I found no performance improvement from using one of the three implementations over the others. The main difference is that the CAML and GetItemByIdSelectedFields versions do not log large SQL messages in the ULS.

The slow query entries will not always come from this type of call. Accessing other SharePoint methods or properties can also cause this, such as performing anything against the list item’s Versions property.

Of course, a better solution to any of the above would be to ensure the design of the lists and content types in use do not utilise too many fields, although this will not always be feasible.

This entry was posted in SharePoint and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the maths problem shown below before posting: *