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

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.

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?

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? 

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

Yes. 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 

Would be good if solutions were not hidden behind internal developer support tickets. Others have similar issues and it would be great if we could find a hint in this forum. Were you be able to solve the issue?

We have also many hits on netMappedIdentityGetById and use a custom ContentProvider for articles so we use the IdentityMappingService to get the original ContentId from a Guid. This is for example needed in the navigation which can display teasers from articles. Is the use of the IdentityMappingService something that should be avoided? Isn't it cached automatically from episerver?

I would love to give some intput, but our case basically resolved to "Just have less data". Good luck.

There are cases when support tickets are absolutely needed, for instances when sensitive information is need for proper investigation.

But I completely agree that once the solution has been found, and if it is beneficial for the wider audience, it should be shared. There is no way to "enforce" that, however.