Paging with ContentSearch and Solr – a small tip for performance

The other day as was doing a performance testing and review on a site. I noticed that the paging through result sets and on category pages didn’t perform to well.

I found the following code that did the actual paging from the result set. The site was also using Solr for all Sitecore indexes. As we work heavily with Solr at Alpha Solutions, my first instinct was to check the Solr logs for the queries actually generated by Sitecore.

if (searchOptions.Size.HasValue)
{
    results = query.Skip(searchOptions.Size.Value*searchOptions.Page)
              .Take(searchOptions.Size.Value)
              .Select(x => x.GetItem())
              .ToList();
}
else
{
    results = query.Skip(searchOptions.Page).Select(x => x.GetItem()).ToList();
}

total = query.Count();

The challenge with the above code is that it performs two queries – and the second query is somewhat nasty. The first query is triggered by the “Select” and generates the following Solr Query:

q=((_path:(0de95ae441ab4d019eb067441b7c2450)+AND+_template:(1557146ecc5d44b9b0ccbc17dece9897))+AND+categories_sm:(db6279712b6f450a86dbd338a2365ed1))&start=0&fq=_indexname:(sitecore_web_index)&sort=publication_date_tdt+desc&rows=12

The above solr query is somewhat OK. Notice the rows=12 that tells Solr to only return the specific 12 records we need for our search result page – and the start parameter that tell Solr the first record in the result set.

The second query is triggered by the query.Count() – very unfortunate – triggers the following Solr Query:

q=((_path:(0de95ae441ab4d019eb067441b7c2450)+AND+_template:(1557146ecc5d44b9b0ccbc17dece9897))+AND+categories_sm:(db6279712b6f450a86dbd338a2365ed1))&fq=_indexname:(sitecore_web_index)&sort=publication_date_tdt+desc&rows=2147483647

Here you should notice the rows parameter again. So if you have a small result-set this might not be a big deal. But if the result set is large, all records will be returned – with the simple purpose of getting the total number of records in the result set.

There is a way better and easier way to achieve the same, with a single Query:

if (searchOptions.Size.HasValue)
{
   var searchResults =
       query.Skip(searchOptions.Size.Value*searchOptions.Page)
          .Take(searchOptions.Size.Value).GetResults();
       results = searchResults.Select(x => x.Document.GetItem()).ToList();
       total = searchResults.TotalSearchResults;
}
else
{
   var searchResults = query.Skip(searchOptions.Page).GetResults();
   results = searchResults.Select(x => x.Document.GetItem()).ToList();
   total = searchResults.TotalSearchResults;
}

The above code will trigger a single Solr Query:

q=((_path:(0de95ae441ab4d019eb067441b7c2450)+AND+_template:(1557146ecc5d44b9b0ccbc17dece9897))+AND+categories_sm:(db6279712b6f450a86dbd338a2365ed1))&fl=*,score&start=0&fq=_indexname:(sitecore_web_index)&sort=publication_date_tdt+desc&rows=12

That’s it. Simple and pretty obvious when you look at the code. The response times where I changed the above code made a huge difference.

As a side note: Looking at the Solr Queries that Sitecore generates could be must better and give further performance boosts. The use of multiple fq instead having long logical queries in the q parameter is better way to query Solr. This opens for better optimization of Solr on various parameters such as caching.

Paging with ContentSearch and Solr – a small tip for performance
Tagged on:             

One thought on “Paging with ContentSearch and Solr – a small tip for performance

  • February 14, 2017 at 4:50 pm
    Permalink

    Hey Roland,

    there is another way to do paging

    var searchResults = context.GetQueryable().Where(query);

    searchResults.Page(pageNumber – 1, pageSize)

    Reply

Leave a Reply

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