Additional info:

It seems that the problem also arise when a variant is opened through a search in the UI. 

The reason for moving the variants to the "Variants" category was that without such a category, all variants were placed in the root, resulting in performance degradations.

Do any have any tips on how to group variants? It doesn't really make sense to group them in the same categories as the associated products - We have a high number of variants per products, so that would propably result in 10.000-20.000 variants per category.

It should only load a few (200?) variants, until you scroll down. Yes it will need to count the variants but it's only the relations, not the variants itself.

If you want to hide all variants, you can override GetNodeEntryReferences and return an empty list for that specific category id. More information can be found here:

https://vimvq1987.com/episerver-commerce-catalog-performance-optimization-part-4/

Hi Quan. 

It seems that the UI correctly only loads a few variants (200 sounds about right), but it seems that a lot is still loaded behind the scene. We se a very large number of calls to the stored procedure mdpsp_sys_LoadMultiValueDictionary. As far as we can see, the only field in our model that uses a multi value dictionary is the "Excluded in markets" standard field.

We would be interested in looking into this. Can you please contact developer support and provide the catalog so we can investigate? that SP should be called once per multi value dictionary property, so if it is called too much, it might be a bug in our side. But we can't know for sure until we look into it 

Hi again Quan.

It actually looks like we can reproduce the bug just by opening the Commerce UI without even hitting the node with all the variants. It results in a massive number of calls to mdpsp_sys_LoadMultiValueDictionary (+100.000 within an hour on our production environment). I have updated ticket 483793 with the same info.

Thanks. I have been trying to follow the ticket but it's a tight schedule for me. If you can export your catalog through Commerce Manager so it contains all metaclasses (instead of having to build those ourselves), that'd be a great time saver.

Hi Quan.

We are actually having trouble exporting the catalog from Commerce Manager - The export progress box opens but never reports any progres. We will see what we can do, but otherwise I think the only way forward is by using the current metaclasses that we associated with the ticket.

An alternative is to use this to export the metaclasses only

Ahh that worked. I have attached the metaclasses to the support request.

Hi Quan.

An update on our progress. We have now moved all variants away from the top-most variant node and into a number of separate sub categories. There is therefore no longer any category with an excessive number of variants below it. The problem is that the performance issue persists. When we access the commerce catalog through the Commerce UI or the Commerce widget in the CMS UI, an excessive number of calls are still made to the SP mdpsp_sys_LoadMultiValueDictionary (it seems that there is one call per variant in our system - +200.000). 

When i look at Application Insight it seem that the call pattern to the different SPs are:

1. Call to GET /EPiServer/cms/Stores/contentstructure/?references=-2147483646__CatalogContent&query=getpagedchildren&allLanguages=true&typeIdentifiers=episerver.commerce.catalog.contenttypes.productcontent&typeIdentifiers=episerver.commerce.catalog.contenttypes.variationcontent&typeIdentifiers=episerver.commerce.catalog.contenttypes.packagecontent&typeIdentifiers=episerver.commerce.catalog.contenttypes.bundlecontent&sort()&dojo.preventCache=1591158329729.
   
2. ecf_CatalogNode_CatalogParentNode
3. ecf_Catalog_GetChildrenEntries
4. ecf_CatalogNode_ChildEntryCount
5. ecf_CatalogEntry_List
6. ecfVersion_List
7. ecf_NodeEntryRelations
8. CatalogContentProperty_LoadBatch
9. mdpsp_sys_LoadMultiValueDictionary - And then it repeats this call again and again and again....

Would a quick fix be to hide all variants? and if so, could the method you provided in https://vimvq1987.com/episerver-commerce-catalog-performance-optimization-part-4/ be used? We never navigates directly to variants through the category three, so we don't really need to see the variants.

It seems that i can now only trigger it by clicking on the catalog root - So even though we have moved the variants to subcategories, the root node still seems to perform some unwanted action. I can read from the blog post that the current SP looks at "it will filter entries which have no rows with IsPrimary = 1 i n NodeEntryRelation" - We have imported our entries through the catalog import API, and as far as I can read from the documentation, i cannot set IsPrimary when importing?

I think I have found one of the problems - When I look at one our our variants I can see that it does not have any Primary category, and as I can read from https://vimvq1987.com/episerver-commerce-catalog-performance-optimization-part-4/ it means that it is shown in the root catalog.

Is there any way I can ensure that the primary category is set during import (we use the standard XML-based service API https://world.episerver.com/documentation/developer-guides/Episerver-Service-API/working-with-bulk-operations-using-tasks/catalog-service/#ww)? 

It seems that we have now solved the problem - After we moved our high number of variants into separate product categories (one per product) we no longer see the same problems.

It still seems that all variants directly below a category are iterated when the category is selected in the UI which results in a high number of calls to the database - We consider this a bug as it doesn't really makes sense, and it would be easy to, by mistake, to bring the solution to its knees. In the Commerce Manager this is handled by paging, and it would be nice if the same applied to the newer Commerce UI and the Commerce Widget.