Addressing Umbraco Performance Issues: A Critical Update for Version 13 and 14

Upgrading your Umbraco CMS can often be a smooth transition, but sometimes unexpected performance issues can arise. This was exactly the situation when our team upgraded from Umbraco 11.5.0 to Umbraco 13.4.1. In this article, we’ll walk you through the critical performance problem we encountered, the steps we took to identify and resolve it, and why it’s essential to update your Umbraco version if you're facing similar issues.

The Issue: Severe Umbraco Performance Degradation#

After updating to Umbraco 13.4.1, we observed significant performance issues, especially when publishing and unpublishing documents.

Our Umbraco system, managing around 325,000 documents, encountered ultra-slow operations and frequent database lock errors.

These issues made the Umbraco CMS unusable for our editors.

 

Symptoms and Initial Observations in Umbraco

  1. Sluggish Operations: Publishing and unpublishing processes within Umbraco were extremely slow.
  2. Database Lock Errors: These errors frequently occurred, causing significant disruptions in the Umbraco workflow.
  3. Upgrade Challenges: The installation process was unusually lengthy, and the Umbraco site required a manual restart to function normally.

Investigation and Troubleshooting in Umbraco#

Identifying the root cause within Umbraco was not straightforward.

We tried multiple strategies to pinpoint the problem:

  • Disabling Nucache Local DB: This was an attempt to mitigate caching-related issues in Umbraco.
  • Rebuilding Caches and Indexes: We forced a rebuild of both the database/memory cache and database indexes in Umbraco.
  • Disabling Custom Handlers: We temporarily disabled all custom handlers, such as ContentPublishingNotificationHandler in Umbraco.
  • Reducing Background Processes: Features like DeliveryApi, Webhooks, and the Content Cleanup Hosted Service were disabled to minimize background load in Umbraco.

You can check more details in initial issue #16803 reported to Umbraco HQ.

 

 

Despite these measures, the problem persisted, indicating a deeper issue within the core Umbraco code.

Regression Bug and Fix#

After a thorough investigation, we discovered a regression bug introduced in a previous Umbraco update. 

The critical flaw was the absence of a WHERE clause in specific SQL count queries, which significantly led to operations miscounting records.

This issue was particularly problematic in Umbraco systems with a large content volume.

We created a pull request #16837 to address this issue.

The fix added the missing WHERE clause, significantly improving the performance of publish/unpublish operations in Umbraco.

The solution was tested on databases with over 320,000 nodes, ensuring it addressed the issue comprehensively.

Here is an example of the T-SQL query that was problematic:

DECLARE @var0 BIT = 1;
DECLARE @var1 BIT = 1;
DECLARE @var2 BIT = 0;
DECLARE @var3 BIT = 1;
DECLARE @var4 UNIQUEIDENTIFIER  = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972';
DECLARE @var5 BIT = 0;
DECLARE @var6 INT = 398650;

SELECT [umbracoNode].[id] AS [Id], [umbracoNode].[uniqueId] AS [Key], [umbracoNode].[level] AS [Level], [umbracoNode].[path] AS [Path], [umbracoNode].[sortOrder] AS [SortOrder], [umbracoNode].[parentId] AS [ParentId], [umbracoNode].[createDate] AS [CreateDate], [umbracoNode].[nodeUser] AS [CreatorId]
, [umbracoContent].[contentTypeId] AS [ContentTypeId]
, [umbracoDocument].[published] AS [Published], [umbracoDocument].[edited] AS [Edited]
, [umbracoContentVersion].[id] AS [VersionId], [umbracoContentVersion].[text] AS [EditName], [umbracoContentVersion].[versionDate] AS [EditVersionDate], [umbracoContentVersion].[userId] AS [EditWriterId]
, [umbracoDocumentVersion].[templateId] AS [EditTemplateId]
, [pcver].[id] AS [PublishedVersionId], [pcver].[text] AS [PubName], [pcver].[versionDate] AS [PubVersionDate], [pcver].[userId] AS [PubWriterId]
, [pdver].[templateId] AS [PubTemplateId]
, [nuEdit].[data] AS [EditData]
, [nuPub].[data] AS [PubData]
, [nuEdit].[dataRaw] AS [EditDataRaw]
, [nuPub].[dataRaw] AS [PubDataRaw]
FROM [umbracoNode]
INNER JOIN [umbracoNode] [x]
ON (([umbracoNode].[id] = [x].[id]) OR ([umbracoNode].[path] LIKE concat([x].[path],',%')))
INNER JOIN [umbracoContent]
ON ([umbracoNode].[id] = [umbracoContent].[nodeId])
INNER JOIN [umbracoDocument]
ON ([umbracoNode].[id] = [umbracoDocument].[nodeId])
INNER JOIN [umbracoContentVersion]
ON (([umbracoNode].[id] = [umbracoContentVersion].[nodeId]) AND [umbracoContentVersion].[current] = @var0)
INNER JOIN [umbracoDocumentVersion]
ON ([umbracoContentVersion].[id] = [umbracoDocumentVersion].[id])
LEFT JOIN [umbracoContentVersion] [pcver]
INNER JOIN [umbracoDocumentVersion] [pdver]
ON (([pcver].[id] = [pdver].[id]) AND ([pdver].[published] = @var1))
ON ([umbracoNode].[id] = [pcver].[nodeId])
LEFT JOIN [cmsContentNu] [nuEdit]
ON (([umbracoNode].[id] = [nuEdit].[nodeId]) AND [nuEdit].[published] = @var2)
LEFT JOIN [cmsContentNu] [nuPub]
ON (([umbracoNode].[id] = [nuPub].[nodeId]) AND ([nuPub].[published] = @var3))
WHERE ((([umbracoNode].[nodeObjectType] = @var4) AND ([umbracoNode].[trashed] = @var5)))
AND (([x].[id] = @var6))
ORDER BY [umbracoNode].[level], [umbracoNode].[parentId], [umbracoNode].[sortOrder]

-- This query returns 324324 records instead of 2
SELECT COUNT(*) FROM (
SELECT [umbracoNode].[id] AS [Id]
FROM [umbracoNode]
INNER JOIN [umbracoContent]
ON ([umbracoNode].[id] = [umbracoContent].[nodeId])
INNER JOIN [umbracoDocument]
ON ([umbracoNode].[id] = [umbracoDocument].[nodeId])
INNER JOIN [umbracoContentVersion]
ON (([umbracoNode].[id] = [umbracoContentVersion].[nodeId]) AND [umbracoContentVersion].[current] = 1)
INNER JOIN [umbracoDocumentVersion]
ON ([umbracoContentVersion].[id] = [umbracoDocumentVersion].[id])
LEFT JOIN [umbracoContentVersion] [pcver]
INNER JOIN [umbracoDocumentVersion] [pdver]
ON (([pcver].[id] = [pdver].[id]) AND [pdver].[published] = 1)
ON ([umbracoNode].[id] = [pcver].[nodeId])
WHERE ((([umbracoNode].[nodeObjectType] = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972') AND ([umbracoNode].[trashed] = 0)))
) npoco_tbl

The lack of proper filtering in this query caused severe performance issues in our large-scale Umbraco setup.

SQL query causing performance issues in Umbraco CMS due to incorrect record count

This SQL query example illustrates a performance issue in Umbraco CMS where a query returns 324,324 records instead of 2, highlighting the need for proper filtering and optimization

As a result, the QueryPaged method (located in NPocoDatabaseExtensions.cs) had to iterate over 324,324 items instead of two, due to the incorrect sqlCount query.

This method executes during application cache refresh operations, such as after publish or unpublish actions:

Code snippet of the QueryPaged method in Umbraco's NPocoDatabaseExtensions.cs file

The QueryPaged method in Umbraco's NPocoDatabaseExtensions.cs file, which iterates over paged query results. Due to a faulty SQL count query, this method had to process 324,324 items instead of the expected two, affecting performance during cache refresh operations like publish/unpublish.

Conclusion: Update Your Umbraco to a Fixed Version#

If you're using Umbraco 13.4.1 and experiencing similar issues, you must update to a version that includes this fix.

Regular updates provide new features and fix critical bugs impacting your system's usability and performance.

Be aware the problem affects Umbraco v10, v13 and v14, so it's recommended to upgrade to the latest version containing the fix.

What's Next?#

Looking for Umbraco experts to help optimize your CMS or resolve issues?

Contact us today!

Our experienced team is ready to assist you with all your Umbraco needs.

Let's ensure your website performs at its best.

↑ Top ↑