Custom ContentProvider performance issue

Vote:
 

Hello

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?

#207861
Oct 07, 2019 14:46
Vote:
 

I have experienced the same performance issue.
Ended up with a folder size of < 25, a couple of years ago.

#207863
Oct 07, 2019 15:03
Vote:
 

Hi,

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.

#207887
Oct 08, 2019 6:21
Vote:
 

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. 

#207902
Oct 08, 2019 11:51
Vote:
 

@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?

#207904
Oct 08, 2019 11:56
Vote:
 

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 

#207905
Oct 08, 2019 11:59
Vote:
 

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?

#208006
Oct 10, 2019 15:15
Vote:
 

I suppose you meant ContentProvider.ResolveContent. Nothing special, we just use lightweight DB calls, and then cache the results.  

#208010
Oct 10, 2019 16:56
Vote:
 

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.

#208011
Oct 10, 2019 17:01
Vote:
 

You should probably look down the trace, it should be called for some thing that you might override with a more optimized method 

#208012
Oct 10, 2019 17:15
Vote:
 

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.

#208018
Oct 10, 2019 17:51
Vote:
 

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?

#208036
Oct 11, 2019 10:09
Vote:
 

More info:

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? 

#208043
Oct 11, 2019 11:57
Vote:
 

@Quan Mai Are you able to offer any insights here?

#208367
Oct 22, 2019 15:46
Vote:
 

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. 

#208369
Oct 22, 2019 15:48
Vote:
 

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?

#208371
Oct 22, 2019 15:59
Vote:
 

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. 

#208375
Oct 22, 2019 16:40
Vote:
 

Here is the profiler trace for the request that fetches 1500 child items.

#208397
Oct 23, 2019 11:08
Vote:
 

What does your content link look like (123_456_Something)? 

#208404
Oct 23, 2019 12:18
Vote:
 

Not sure what you mean by that?

This is what my MappedIdentity looks like.

So, i guess it's  (5355_0_searchfacets)?

#208430
Oct 23, 2019 15:17
Vote:
 

Yes. 5355_0_searchfacets.

Did you also override the ProviderKey to return "searchfacets" in your content provider? 

#208431
Oct 23, 2019 15:45
Vote:
 

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.

#208433
Oct 23, 2019 15:51
Vote:
 

Would still appreciate any insights into this, and how we can avoid those extra SQL calls made by EPiServer.

#208664
Oct 29, 2019 18:24
Vote:
 

Sorry for a late reply but this is something you should reach out developer support service for further assistance 

#208919
Nov 04, 2019 22:43
Vote:
 

Is "developer support service" the one found on support.episerver.com?

#208936
Nov 05, 2019 10:25
Vote:
 

Yes. You will need to create a support ticket 

#208988
Nov 05, 2019 15:22