Eliminating N+1 Queries in Optimizely CMS: A Case Study

The Hidden Cost of Nested ContentAreas

In Optimizely CMS (formerly Episerver), the flexibility of ContentArea often leads to nested architectures. While great for editors, it frequently introduces the N+1 query problem. We recently audited a Store Page where loading a single page triggered ~181 database queries. The culprit? Iterating through nested loops and calling _contentLoader.Get<T>() for every individual item.

The Anti-Pattern: Individual Loading

The original implementation loaded content one-by-one inside nested loops. This creates a massive performance bottleneck as each call results in a separate database roundtrip.

// ❌ The N+1 Anti-pattern
foreach (var item in storePage.ContentArea.Items)
{
    var content = _contentLoader.Get<IContent>(item.ContentLink); 
    if (content is LayoutRowBlock row)
    {
        foreach (var rowItem in row.ContentArea.Items)
        {
            var child = _contentLoader.Get<IContent>(rowItem.ContentLink); // ❌ Query per child
            // ... further nesting
        }
    }
}

The Solution: Batch Loading with GetItems()

The strategy to eliminate N+1 is simple: Collect references first, load once, then iterate in memory. Optimizely’s IContentLoader.GetItems() is specifically designed for this purpose, fetching multiple content items in a single batch operation.

Optimized Batch Implementation

// ✅ 1. Collect all references
var contentLinks = storePage.ContentArea.Items
    .Where(i => i?.ContentLink != null)
    .Select(i => i.ContentLink)
    .ToList();

// ✅ 2. Single batch query for all items
var allContent = _contentLoader.GetItems(contentLinks, CultureInfo.CurrentUICulture);

// ✅ 3. Iterate in-memory
foreach (var content in allContent)
{
    if (content is LayoutRowBlock row)
    {
        var childLinks = row.ContentArea.Items.Select(x => x.ContentLink);
        var rowChildren = _contentLoader.GetItems(childLinks, CultureInfo.CurrentUICulture);
        // Process rowChildren...
    }
}

Strategic Results

By shifting to batch operations, we achieved significant performance gains:

• Database Queries: Reduced from ~181 down to ~11.
• Response Time: Dropped from 800ms+ to under 150ms.
• Scalability: The page performance no longer degrades linearly with the number of products.

Key Takeaways

Always profile your CMS pages using tools like MiniProfiler. If you see repetitive SELECT statements for tblContent, you likely have an N+1 issue. Remember: _contentLoader.Get<T> is for single items; GetItems is for collections.