Sunday, July 17, 2011

Improving the performance of a Sitecore Item search

Ran into an issue with a third party eCommerce module (ETP) for Sitecore storing tens of thousands of orders as items in the content tree of the master database. As the number of orders increased the inbuilt GetOrder method was getting progressively slower until page requests started timing out.

I traced the delay down to a SelectSingleItem call (using ILSpy to disassemble the code):

// ...
Sitecore.Data.Items.Item ordersItem = database.GetItem(ordersLink);
// ...
string query = string.Format(".//*[@{0}='{1}']", "OrderNumber", orderId);
// typical example query: ".//*[@OrderNumber='4919'] 
Sitecore.Data.Items.Item orderItem = ordersItem.Axes.SelectSingleItem(query);

Running the same query in the Developer Center gives a time of 105 seconds.

My initial attempt to speed this up was to try a Sitecore fast query. This was promising, but still took around 10 seconds.

Solution

The final solution was to create a Lucene Search Index for the Order Template and Order Number field.

In the web.config:

    <indexes>
      <!-- Existing "system" index -->
      <index id="eCommerceOrders" singleInstance="true" type="Sitecore.Data.Indexing.Index, Sitecore.Kernel">
        <param desc="name">$(id)</param>
        <templates hint="list:AddTemplate">
          <!-- Index the Order Template -->
          <template>{2769D69F-E217-4C0A-A41F-2083EC165218}</template>
        </templates>
        <fields hint="raw:AddField">
          <field target="orderNumber">OrderNumber</field>
        </fields>
      </index>
    </indexes>

Add the new index to the master database

        <indexes hint="list:AddIndex">
          <index path="indexes/index[@id='system']"/>
          <index path="indexes/index[@id='eCommerceOrders']"/>
        </indexes>

And then finally override the default OrderProvider to check the Index for the order first before falling back on the base implementation.

   Sitecore.Data.Database database = Sitecore.Configuration.Factory.GetDatabase("master");

   BooleanQuery completeQuery = new BooleanQuery();
   Term term = new Term("orderNumber", orderId);
   completeQuery.Add(new TermQuery(term), BooleanClause.Occur.MUST);

   Index eCommerceOrdersIndex = database.Indexes["eCommerceOrders"];
   if (eCommerceOrdersIndex == null)
   {
    return null;
   }

   IndexSearcher searcher = eCommerceOrdersIndex.GetSearcher(database);

   Hits hits;
   try
   {
    hits = searcher.Search(completeQuery);

    for (int i = 0; i < hits.Length(); i++)
    {
     Item orderItem = Sitecore.Data.Indexing.Index.GetItem(hits.Doc(i), database);
     if (orderItem != null)
     {
      // Existing code to convert Item to Order here...
     }
    }
   }
   finally
   {
    searcher.Close();
   }

See Also: