The FFW team works with more than just Drupal and WordPress. We've built some award-winning Sitecore solutions as well, and in this Sitecore blog series, we'll be sharing our insights on refining Sitecore's search functionality to enhance the user experience.

Test scenario

Let’s revisit our test scenario from the last Sitecore search blog.

I have created a Sitecore bucketable template called Article with the following fields on it:

• Article Title (Single Line Text)
• Article Description (Multiline Text)
• Field One, Field Two, Field Three, Field Four, Field Five (Single Line Tex)

I also have created a Sitecore bucket which contains 2000 articles. One half of the “Article Title” fields values have been set to “Even“ and the other half to “Odd“. Other fields have been set to random string values. I haven’t created a separate search index but have used the default site core_web_index for my tests.

Here is the ArticleSearchResultItem that I’ve built in my code:

public class ArticleSearchResultItem {
  [IndexField("_group"),
  TypeConverter(typeof(IndexFieldIDValueConverter)), DataMember]
  public virtual ID ItemId { get; set; }

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

  [IndexField("article_title_t")]
  public virtual string ArticleTitle { get; set; }

  [IndexField("article_description_t")]
  public virtual string ArticleDescription { get; set; }

  [IndexField("field_one_s")]
  public virtual string FieldOne { get; set; }

  [IndexField("field_two_s")]
  public virtual string FieldTwo { get; set; }

  [IndexField("field_three_s")]
  public virtual string FieldThree { get; set; }

  [IndexField("field_four_s")]
  public virtual string FieldFour { get; set; }

  [IndexField("field_five_s")]
  public virtual string FieldFive { get; set; }
}

I’ve added a Sublayout on the home page of the website and on each page load I’ve executed a Sitecore search for Article Title = “Even“ AND TemplateID = “Article Template ID“.

Getting all data from the index vs querying the Sitecore database

We won’t apply any paging because we want to receive as many as possible results. We want to test how our search will perform when we load all of the properties from Solr in comparison with loading only Item IDs from Solr and getting all other properties from the Sitecore database.

Here is the code snippet for loading all search result data from the index:

var resultItems = context.GetQueryable<ArticleSearchResultItem>()
    .Where(p => p.TemplateId == articleTemplateID)
    .Where(p => p.ArticleTitle.Equals(text))
    .Select(p => new ArticleSearchResultItem
  {
     ArticleTitle = p.ArticleTitle,
     ArticleDescription = p.ArticleDescription,
     FieldOne = p.FieldOne,
     FieldTwo = p.FieldTwo,
     FieldThree = p.FieldThree,
     FieldFour = p.FieldFour,
     FieldFive = p.FieldFive
   })
   .ToList();

For comparison, here is the code snippet for loading only the Item IDs and then querying the database for all left:

var searchResultIDs = context.GetQueryable<ArticleSearchResultItem>()
   .Where(p => p.TemplateId == articleTemplateID)
   .Where(p => p.ArticleTitle.Equals(text))
   .Select(p => new { p.ItemId })
   .ToList();

var db = Database.GetDatabase("web");
var searchResults = new List();

foreach (var id in searchResultIDs)
{
 var item = db.GetItem(id.ItemId);
 searchResults.Add(new ArticleSearchResultItem
 {
  ArticleTitle = item["Article Title"],
  ArticleDescription = item["Article Description"],
  FieldOne = item["Field One"],
  FieldTwo = item["Field Two"],
  FieldThree = item["Field Three"],
  FieldFour = item["Field Four"],
  FieldFive = item["Field Five"]
 });
}

Here are the results:

• All properties from the index with Select: 830ms
• Only Item ID from the index and all left from Sitecore database: 1800ms

We see that on the first load reading from the Sitecore database is more than twice slower than getting all of the data from the index. But don’t rush the conclusions.

Except for the first load, I’ve also tested the next 3 loads and here are the results:

• 2..n querying: All properties from the index with Select: 370ms
• 2..n querying: Only Item ID from the index and all left from Sitecore database: 290ms

Conclusion

On 2..n load, we gain performance from various Solr and Sitecore caches. What we can see is that loading only Item ID from the index and all left from Sitecore database performs better on 2..n querying. That’s happening because on 2..n querying we already have the items in the Sitecore item cache which makes the loading of all left properties extremely fast. And if you have concerns about the slow first load, you may think about some strategy which will preload the items which will be searched in the Sitecore Item cache. This would make the Item ID from the index and all left from Sitecore database (item cache) the best performing search option.

If you enjoyed this blog post, please don’t forget to check the Sitecore Search Series: Your Complete Guide to Performance Improvement blog series for more good tips! If you need help with a Sitecore site, let us know. Our expert Sitecore team is here to help you.