Search Query Performance: From 800ms to 70ms

Recently I had to implement new functionality in a project where we imported a large number of items into Sitecore (around 2k – ok maybe it’s not that large, but more than a hundred:) ). Let’s assume that those items were products. I created item bucket, and in my new rendering I created a query using LINQ to Sitecore:

using (var context = index.CreateSearchContext())
{
    var results = context.GetQueryable<SearchResultItem>()
                  .Where(item => item.TemplateId == ProductTemplateId
                                 && item.Paths.Contains(ProductsRootItemId)
                                 && item.Language == ContextLanguage)
                  .ToList();
}

To my surprise, it wasn't very fast. It took around 800ms to got the results. I didn’t know why it was so slow because running the same query directly in Solr admin panel got the results in 10ms.

The Solr query generated by above code looks like this:

/select params={q=((_template:(52516a2c492e497fbf48da94588ffded)+AND+_path:(945c45ea9bcc400e8389127f363458be))+AND+_language:(en))&fq=_indexname:(sitecore_master_index)&rows=2147483647&version=2.2}

Replace SearchResultItem with your custom one

I heard some rumours that creating custom search result class could speed up things, so I created a class like this:

public class ProductSearchResultItem
{
    [IndexField("_path")]
    [TypeConverter(typeof(IndexFieldEnumerableConverter))]
    public virtual IEnumerable Paths { get; set; }

    [IndexField("_template")]
    [TypeConverter(typeof(IndexFieldIDValueConverter))]
    public virtual ID TemplateId { get; set; }

    [IndexField("_language")]
    public virtual string Language { get; set; }

    [IndexField("_name")]
    public virtual string Name { get; set; }
}

The class has only four properties. First three are necessary because I want to filter results by a template, path and language. The Name property is something that I want to get back as a result. I updated the query to something like this:

using (var context = index.CreateSearchContext())
{
    var results = context.GetQueryable<ProductSearchResultItem>()
                  .Where(item => item.TemplateId == ProductTemplateId
                                 && item.Paths.Contains(ProductsRootItemId)
                                 && item.Language == ContextLanguage)
                  .ToList();
}

It looks kind of the same like before but now it uses my own search result class and it’s 2x faster!!! Something around 350ms. The Solr query generated by above code looks exactly the same like before

/select params={q=((_template:(52516a2c492e497fbf48da94588ffded)+AND+_path:(945c45ea9bcc400e8389127f363458be))+AND+_language:(en))&fq=_indexname:(sitecore_master_index)&rows=2147483647&version=2.2}

Back to original SearchResultItem but add a select clause

It was still not very fast. 350ms is not 10ms. A lot of data have to be transferred, parsed and deserialized. If we look at the Solr query generated by Sitecore we can see that it doesn’t limit the number of fields we get in result. The query returns all fields for documents. But we are interested only in product names right? (Or maybe just I am.)

I tried to add a select clause to LINQ query, to limit the number of fields transferred from Solr to Sitecore. I tried with original SearchResutItem class first to compare results. The query looked like this:

using (var context = index.CreateSearchContext())
{
    var results = context.GetQueryable<SearchResultItem>()
                  .Where(item => item.TemplateId == ProductTemplateId
                                 && item.Paths.Contains(ProductsRootItemId)
                                 && item.Language == ContextLanguage)
                  .Select(x => new { x.Name })
                  .ToList();
}

And the Solr query generated by Sitecore looks like this:

/solr path=/select params={q=((_template:(52516a2c492e497fbf48da94588ffded)+AND+_path:(945c45ea9bcc400e8389127f363458be))+AND+_language:(en))&fl=_name,_uniqueid,_datasource&fq=_indexname:(sitecore_master_index)&rows=2147483647&version=2.2

As you can see now, it has &fl= param with our _name field. It also has two additional fields added by Sitecore.

The query was the fastest so far. I got results in 230ms. So it really means that transferring a lot of unnecessary data and then parsing it takes a lot of precious time.

Replace SearchResultItem with your custom one and add select clause

Replacing SearchResultItem with our custom class saved 450ms for us (800ms – 350ms) and adding select clause saved 570ms (800ms - 230m). So 800ms - 450ms – 570ms equals... Never mind...

Let’s see what happened when I used the custom class with select clause. The query looked like this:

using (var context = index.CreateSearchContext())
{
    var results = context.GetQueryable<ProductSearchResultItem >()
                  .Where(item => item.TemplateId == ProductTemplateId
                                 && item.Paths.Contains(ProductsRootItemId)
                                 && item.Language == ContextLanguage)
                  .Select(x => new { x.Name })
                  .ToList();
}

The Solr query generated looks precisely the same like before:

/solr path=/select params={q=((_template:(52516a2c492e497fbf48da94588ffded)+AND+_path:(945c45ea9bcc400e8389127f363458be))+AND+_language:(en))&fl=_name,_uniqueid,_datasource&fq=_indexname:(sitecore_master_index)&rows=2147483647&version=2.2

But now I got results in 70ms. Wow, that’s much faster, and I'm quite happy about that.