We're writing a custom ContentProvider to enable editors to reference data from an external system in the page properties.
It seems that when there are a lot of child nodes for a content item, then expanding that item will take a very long time to open.
Even if we cache our entire content tree in memory, EPiServer will take minutes to expand a node with 1500 children.
What is the best practice here? Do we need to divide our content into smaller folders? If so, what is the optimal folder size for such a folder?
Looking at a SQL profiler, EPiServer is doing a lot of SQL calls when expanding a tree. Is that to be expected?
I have experienced the same performance issue.Ended up with a folder size of < 25, a couple of years ago.
As per my knowledge, Episerver only recommends a maximum of 100 children under a node so I suggest you divide the content in a smaller folder structure so that it will be more organize and maintainable.
I'm not sure where that recommendation comes from (I might be oblivious, of course), but Catalog content provider, which is a "custom" content provider itself (the difference is it is built by us) can handle much bigger number of children (a few trousands or even more) without problem. everything comes down to how you cache things and avoid loading things unnecessarily.
@Quan Mai do you have any resources I can read about this?
Currently, even if I store everything in memory, and allow EPiServer to cache the results, then it takes around 45 seconds for EPiServer to expand a folder with 1500 children the first time I open it.
It seems to be because expanding the tree causes EPiServer to do a SQL Insert + a stored procedure call for every child item? Is there a way to avoid that?
I don't, but it is very strange that you see insert command. As usual you can use some profiler like dotTrace to see what code is calling that, and then find out a solution
I think I've identified the bottleneck.
In the "LoadContent()" method of the ContentProvider we need to call "IdentityMappingService.Get(contentLink)" in order to get the ExternalIdentifier for our data source.
So when expanding a folder with 1500 nodes, this method will be called 1500 times, causing 1500 database queries.
Is there a workaround for this?
@Quan Mai How do you handle this in the Catalog content providers LoadContent method?
I suppose you meant ContentProvider.ResolveContent. Nothing special, we just use lightweight DB calls, and then cache the results.
No, I'm talking about ContentProvider.LoadContent
That's an abstract method, so it has to be implemented. And that is called once for every child in the tree.
You should probably look down the trace, it should be called for some thing that you might override with a more optimized method
Well, there is a LoadContens (plural) method. But even that takes only 200ms to return the 1500 items.
I've eliminated the SQL queries now with caching, but it still seems to be taking forever to get those items to the UI somehow.
I could really use some insight as to where to look to improve this for the editors.
A bit more information on this:
Even if I have zero calls to the IdentityMappingService when a folder is expanded in my provider, the following SQL is still executed somewhere for each child node:
declare @p1 dbo.ContentReferenceTable
insert into @p1 values(5351,0,N'searchfacets')
exec netMappedIdentityGetById @InternalIds=@p1
This seems to be done by EPiServer? Any idea what would be executing this?
The sql statements are made during serialization of the response. The transformers end up calling PermanentContentLinkMapper.FindInternal().
This methods loops through the IContentResolvers and calls ResolveContent(), and for the first resolver that does not return null, it saves that as the preferred resolver for that ProviderName.
Since the DefaultIdentityMappingService is asked to resolve the content before our content provider, then that will go to the database to look for that reference, and return a result.
The ResolveContent method is never hit on our own provider here.
How can we change this?
@Quan Mai Are you able to offer any insights here?
As I said, you would really need to look at the profiler result to see what you can override. You haven't posted that here so I don't have any other suggestion.
What do you mean? My previous post explains pretty detailed which EPiServer class (DefaultIdentityMappingService) is making the sql calls.
If you do not have this issue in the Catalog content provider, you must have done something to avoid it since the actual ContentProvider implementation has no influence on this performance?
I was expecting something like this
Without it it is very hard to know why you have that kind of issue when CatalogContentProvider does not, as we do not actively "fix" it.
Here is the profiler trace for the request that fetches 1500 child items.
What does your content link look like (123_456_Something)?
Not sure what you mean by that?
This is what my MappedIdentity looks like.
So, i guess it's (5355_0_searchfacets)?
Did you also override the ProviderKey to return "searchfacets" in your content provider?
We provide the "searchfacets" key as the "name" param on the Initialize() call, which sets the ProviderKey property.
And we call the Initialize() before adding it to the ProviderMap.
Would still appreciate any insights into this, and how we can avoid those extra SQL calls made by EPiServer.
Sorry for a late reply but this is something you should reach out developer support service for further assistance
Is "developer support service" the one found on support.episerver.com?
Yes. You will need to create a support ticket