IPublishedContent.Children() - runs a SQL query every execution (1 million in 24 hours)? (V15)

Questions
1

Hi,

I noticed on our Azure SQL diagnostics, that this particular query has ran over 1 million times(!) in the past 24 hours.

The query in question

(@0 int,@1 int,@2 int,@3 int,@4 uniqueidentifier,@5 int,@6 uniqueidentifier)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 [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] = @0) 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]) LEFT JOIN [cmsContentNu] [nuEdit] ON (([umbracoNode].[id] = [nuEdit].[nodeId]) AND [nuEdit].[published] = @2) LEFT JOIN [cmsContentNu] [nuPub] ON (([umbracoNode].[id] = [nuPub].[nodeId]) AND ([nuPub].[published] = @3)) WHERE ((([umbracoNode].[nodeObjectType] = @4) AND ([umbracoNode].[trashed] = @5))) AND (([umbracoNode].[uniqueId] = @6)) ORDER BY [umbracoNode].[level], [umbracoNode].[parentId], [umbracoNode].[sortOrder]

I have isolated this down by running a simple test in a Razor view, to get Homepage by key, and return .Children()

@using Umbraco.Cms.Web.Common.PublishedModels;
@inherits Umbraco.Cms.Web.Common.Views.UmbracoViewPage
@{
    Layout = "~/Views/_Layout.cshtml";

    var hp = Umbraco.Content("f79817b7-1b7c-4f3c-921d-eecc2538e19e");
    var myTest = hp.Children();
}

I then have this query running on SSMS to see executed queries

SELECT TOP 100
    qs.last_execution_time,
    qt.text AS [QueryText],
    qs.execution_count,
    qs.total_elapsed_time/1000 AS total_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE '%0 int,@1 int,@2 int%'
ORDER BY qs.last_execution_time DESC;

Every time i execute hp.Children() - the query in question (thats running 1m times a day in Production) is executed and i see the count go up in the SSMS query I’m running. I can trigger this every time i run the test, the cache should have been fully warmed. It never stops executing this query.

I thought this should NEVER happen under ANY circumstance?

For context, this is on a massively multilngual site with 52 languages.

Does anyone have any ideas?

Thanks

1 Like
2

hi Rishch,

What you’re seeing isn’t Children() hitting the database, it’s the lookup by GUID.
Calling Umbraco.Content(Guid) forces Umbraco to resolve the node from the published snapshot (culture, variants, domains, etc……), and that can result in a SQL query per request , especially on a large multilingual site like this.

Once the node is resolved, Children() just works on the in-memory snapshot.

For hot paths (layouts/partials), it’s best to avoid GUID lookups in Razor. Using Model.Root() / GetAtRoot()

3

Hi Bishal,

Thanks for your inight, I appreciate it!

That code was just to test it in isolation, our actual look up is via:

public IPublishedContent GetHomepage()
 {

     var homeNode = _publishedContentQuery.ContentAtRoot().FirstOrDefault(x => x.ContentType.Alias == "homepage");

     if(homeNode != null)
     {
         return homeNode;
     }

     return null;
 }

However, same effect - soon as .Children() is executed (not before) the query is executed.

Sounds like it’s an issue with multi-lingual sites then?

Thanks!

5

That’s expected on large multilingual sites. Even though your root lookup is in memory, calling .Children() can trigger a SQL query because Umbraco resolves children per culture/variant from the published snapshot.

To reduce this, you can cache the homepage node (and its children) or avoid repeated .Children() calls in hot paths like layouts or partials?

6

Ah ok, I didn’t realise this was the case, I thought everything in front-end services ran from cache.

We are caching, extensively, (lazy on demand per culture), but mostly for Decendants() as these got hideously expensive very quickly. However, when we cache we’re generally just caching the keys from the lookups and rehydrating for subsequent requests. I didn’t think it was safe to cache an actual IPublishedContent directly as it was a complex type? But maybe I can revert to caching these if it’s generally considered best practice?

Thanks again! :slight_smile:

7

Yes :slight_smile: caching the actual IPublishedContent per snapshot/culture is safe and actually best practice for hot paths. .Just avoid caching across snapshots, and you’ll eliminate those extra queries.

goodluck

8

Great ok, thanks!

9

Is there an official Umbraco way that you can create your own caches of IPublishedContent that utiliise the HybridCache, if we are using Azure Managed Redis for IDistributedCache already? I am sure I read somewhere that Umbraco already handles flattening and serialising IPublishedContent in the Hybrid cache. Although not sure what public service might expose this? (If it’s indeed correct)

Thanks!

10

Creating a Custom Seed Key Provider | CMS | Umbraco Documentation

Working with caching | CMS | Umbraco Documentation

???

Adding Redis and Azure Cognitive Search to Umbraco Marketplace
frustratingly

You can see from the following gist how we are using the appropriate implementation based on a configuration value:
but no gist linked… :frowning:

11

Thanks Mike. I was sort of hoping there was something that would act as a drop in replacement for IDistributedCache or IMemoryCache, for our own results of lookups from the Umbraco cache (because of the DB hits we are getting). I think seeding the cache doesn’t get around our specific issue, although, i’d love to find out that I’m wrong and it does! I did play around with using the config settings for Umbraco:Cache:ContentTypeKeys, from what I can tell, doesn’t work in our scenario (because of the multilingual setup?)

12

It looked promising… :person_shrugging:

This example implements a IDocumentSeedKeyProvider which seeds all the children of a node, in this case blog posts.
…
All blogpost will now be seeded into the cache on startup, and will always be present in the cache.

I read that as won’t go to the DB then… but would also need the redis cache to not slow recycles, and/or for loadbalancing.

Also had this in my favorites…
Umbraco 10 - Azure Cache for Redis - DEV Community

13

Thanks, Yeah, def worth a shot!

I thought that is what the ContentTypeKeys array in settings was supposed to do auto-magically, but my somewhat brief testing stopped me going any further with that, I wont close that door just yet then!

I only started looking into this after temporairly using a SQLDistributedCache instance, and noticed it fell over when I did a full DB rebuild (Ran out of Azure SQL worker threads), AI seemed to think that HybridCache is storing some flattened representation of IPublishedContent… if so would be nice to have that exposed via something where I could access it directly.

Tbf I should probably look in the source and verify if that’s even the case !! I’ll do some more digging..

Thanks!

15

var homeNode = _publishedContentQuery.ContentAtRoot().FirstOrDefault(x => x.ContentType.Alias == "homepage");

It sounds like a bug to me? Using publishedcontent shouldnt touch database.

because Umbraco resolves children per culture/variant from the published snapshot.

I would expect it to hit the cache.

But if its true, then I’m curious to know what triggers hitting db when using publishedcontent

16

I strongly recommend against this.

Caching an IPublishedContent across requests is a bad idea. It’s easy to get memory leaks and concurrency issues that way. IPublishedContent is not a POCO/DTO that you can just leave lying around in memory.

You can safely cache an IPublishedContent for the duration of a request, but for every new request you should really fetch from the content cache again.

This is safest. If content lookups aren’t performant, rolling your own index (or using Examine) is a safe bet.

All that having been said…

Have you raised this as an issue on the repo? I think this is something that needs fixing rather than something you should have to work around.

Since v15 is EOL you’ll probably want to upgrade to v16+ first, try and replicate, and then raise an issue.

1 Like
17

Thanks Jason,

I have to admit, those were my initial instincts, as It feels like it violates Umbraco rule #1 - never goto DB on front-end.

Upgrade is on the roadmap, in the near term. I’d love to see if it resolves.

Will post back once I have chance to test.

3 Likes