Episerver Commerce catalog performance optimization – part 4

Recently I worked on a support case where a customer reported deadlocks and timeout exceptions on queries to a specific table – NodeEntryRelation. Yes, it was mentioned in this post. However, there is more to it.

Keeping the indexes healthy definitely help to improve performance and avoid deadlocks and timeout exceptions. However it can only work to a limit, because even if the indexes are in their perfect state (the fragmentation level is 0%), the query will still take time.

Looking in the query we talked about – ecf_Catalog_GetChildrenEntries – what does it do. It lists the entries which are direct children of a catalog. So normally entries belong to categories (nodes), but it’s possible (Although not recommended) to have entries that belong directly to a catalog.

If you look into this query, you’ll find that it’s very hard to optimize

• In Commerce 10.x and before, this query will look into CatalogEntry table to filter by CatalogId, and then filter the entries which have no rows in NodeEntryRelation
• In Commerce 11 and later, the first part is the same, but the second part, it will filter entries which have no rows with IsPrimary = 1 in NodeEntryRelation

If you have a big enough number of CatalogEntry, and then big enough number of rows in NodeEntryRelation, this query will gonna take time. Yes, its result will be cached in application level, but due to the nature of dependencies, the cache will be invalidated anytime an entry, or a node, is updated.

The problem with that support case I mentioned above is that they constantly update their catalog entries, which renders the cache in application layer almost useless (As it gets invalidated very fast), causing the database is hit almost every time.

Is there a way to solve it? yes. Should you do it? depends.

There are two types of optimization that can be done. The first one is safe for all customers, so it’s usually incorporated to the framework. The second one does not work for everyone and can only applied for specific customer(s), when they have the full control of what they do, and are willing to take the risk.

[quads id=2]

As I mentioned before, you should not have entries which are directly belong to a catalog. And if you follow that advice, it would be a waste to hit the database just to return an empty list. Can we make it return empty list in application level? Yes, with a little “hack”:

using System.Collections.Generic;
using EPiServer.Commerce.Catalog.Provider;
using EPiServer.Commerce.Catalog.Provider.Construction;
using EPiServer.Core;
using EPiServer.Framework.Cache;
using EPiServer.ServiceLocation;
using Mediachase.Commerce.Catalog;

namespace EPiServer.Commerce.Sample
{
    [ServiceConfiguration(typeof(CatalogContentLoader))]
    public class CustomCatalogContentLoader : CatalogContentLoader
    {

        public CustomCatalogContentLoader(ICatalogSystem catalogSystem,
            IEnumerable<ICatalogContentBuilder> contentBuilders, ReferenceConverter referenceConverter,
            MetaClassToContentTypeMap metaClassMap, ISynchronizedObjectInstanceCache objectInstanceCache,
            IContentCacheKeyCreator contentCacheKeyCreator)
            : base(
                catalogSystem, contentBuilders, referenceConverter, metaClassMap, objectInstanceCache,
                contentCacheKeyCreator)
        {
        }

        protected override IList<CatalogGetChildrenReferenceResult> GetCatalogEntryReferences(int catalogId)
        {
            return new List<CatalogGetChildrenReferenceResult>();
        }
    }
}

This will override the default CatalogContentLoader and return an empty list directly, because you know for sure that there is nothing in database level. Now, you are stepping into “grey” areas – where you can do something, but that’s not fully supported by Episerver. Episerver even states clearly that CatalogContentLoader should never be used in your code. (So they reserves the right to change it without a major version)

So the final decision is yours – is the performance improvement worth the risk you are making?