In a previous post, I discussed how accurate testing of SQL Server round-trip performance is an important way to optimize a Kentico site. Today, I’d like to explain another optimization tip: how to improve performance by reducing the number of SQL Server query round trips.

One common way to unwittingly increase SQL Server round trips is when accessing collection properties. For example, if code that enumerates many TreeNode objects also hits collection properties like Categories, DocumentTags, and Attachments, there could be an order-of-magnitude round-trip performance cost. If the code enumerates 20 TreeNode objects, and touches each of those three properties, it results in 60 additional SQL Server round trips. How would we reduce them in this scenario?

Leverage the ObjectQuery API

There are many ways to optimize this performance. For example, one could use a search index, with tags and categories added as fields. In this example, a great way to reduce SQL server round trips is to use Kentico’s ObjectQuery API. Instead of using TreeNode collection properties to retrieve child objects one node at a time, the child data for a whole set of TreeNodes can be queried in one round trip.

To illustrate how SQL round-trip performance can be optimized, here’s a before-and-after example:

Imagine code that’s creating a ViewModel to render a list of pages that includes the categories of each page. Before optimization, a simplified version of the code might look like this:

var tree = new TreeProvider();

var treeNodes = tree.SelectNodes()

     .Types("Acme.Article")

     .OnSite("Acme")

     .Culture("en-us")

     .Path("/Articles/%")

     .ToList();




foreach (var treeNode in treeNodes)

{

    // Do something with the tree node

    foreach (var category in treeNode.Categories)

    {

        // Do something with the category

    }

}

If rending a list of 20 pages, this would result in 20 extra round trips because the Categories property is accessed for each TreeNode.  In some cases, this might not be bad. However, if the server load is demanding, and other child collections like DocumentTags and Attachments are being hit too, this process would be worth optimizing. So, instead of using the Categories collection property, you could create an ObjectQuery in the code to get the Categories for all the needed TreeNodes at once.

The sample code below returns the same pages and Categories using the following steps:

1. Create and execute the query of TreeNodes.
2. Extract the DocumentIDs and use them to create an ObjectQuery of all Categories that are related to the Documents through the DocumentCategory object.
3. Group the returned Categories by DocumentID.

Notice that it is necessary to use the underlying DataTable object when grouping the Categories, so that fields from both the Category and DocumentCategory objects will be accessible. However, after performing the GroupBy, the code will be able to use a Dictionary containing lists of strongly-typed CategoryInfo objects.

var tree = new TreeProvider();

// First create a query to get the TreeNodes

var treeNodeQuery = tree.SelectNodes()

    .Types("Acme.Article")

    .OnSite("Acme")

    .Culture("en-us")

    .Path("/Articles/%");

var treeNodes = treeNodeQuery.ToList();

// Get the DocumentIDs for these TreeNodes, so that we can use them in

// a query for Categories.

var documentIds = treeNodes.Select(n => n.DocumentID).ToList();

// Create a query for Categories, but use a Join to include DocumentCategories,

// so that we can filter by DocumentID.

var categoryQuery = CategoryInfoProvider.GetCategories()

    .Source(s => s.Join<DocumentCategoryInfo>("CategoryID", "CategoryID"))

    .Columns("DocumentID", "CMS_Category.CategoryID", "CategoryName", "CategoryDisplayName")

    .WhereIn("DocumentID", documentIds);




// Use the underlying DataTable that includes both Category and DocumentCategory fields,

// so that we can group by a field in the DocumentCategory object.

var categoryTable = categoryQuery.Tables[0];

var categoriesByDocumentId = categoryTable.AsEnumerable()

    .GroupBy(row => row.Field<int>("DocumentID"))

    .ToDictionary(g => g.Key, g => g.Select(row => new CategoryInfo(row)).ToList());




// RESULT: A dictionary with key for each document that has Categories.

// The key is the DocumentID, and the Value is a list of CategoryInfo.

Measuring the improvement

When working with multiple pages, querying categories and other child objects using the ObjectQuery API creates a dramatic improvement in SQL Server round trips. For example, SQL Server Profiler verifies that when the above sample code is used, both the pages and categories are returned with only two round trips.

This opportunity to optimize querying child data is an example of how valuable it is to test SQL Server round-trip performance. When this testing is performed early on in the development process, it can help prevent performance problems before they have a business impact. And with Kentico’s robust ObjectQuery API, developers can create even more powerful high-performance solutions.

Have questions on how to get the most out of your Kentico implementation? Drop me a line on Twitter (@HeyMikeWills), or contact our BlueModus team of experts today.