Performance issue in Catalog UI when selecting node with more than 100.000 variants

Vote:
 

Hi,

We are experiencing issues with the Catalog UI using all server memory if we access a catalog node with +100.000 variants. The reason for having this node is that the variants must belong to a node to avoid them being placed in the root of the catalog.

Is it possible to avoid that the UI tries to load all items in the view or remove the Variants node from the UI?

#223525
May 29, 2020 8:47
Vote:
 

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.

#223526
May 29, 2020 8:57
Peter Wind - May 29, 2020 9:54
We are looking into splitting up our variant category into sub-categories but it would be much easier for us to just limit how many variants that are loaded when the catalog node is selected.
Vote:
 

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/

#223551
May 29, 2020 12:06
Vote:
 

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.

#223579
May 30, 2020 7:27
Vote:
 

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 

#223580
May 30, 2020 13:09
Peter Wind - May 30, 2020 17:39
I have created support ticket 483793.
Vote:
 

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.

#223687
Jun 02, 2020 14:01
Vote:
 

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.

#223688
Jun 02, 2020 14:13
Vote:
 

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.

#223689
Jun 02, 2020 14:30
Vote:
 

An alternative is to use this to export the metaclasses only

#223695
Jun 02, 2020 15:26
Vote:
 

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

#223704
Jun 02, 2020 17:24
Vote:
 

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?

#223727
Edited, Jun 03, 2020 5:01
Vote:
 

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

#223729
Jun 03, 2020 5:21
Vote:
 

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.

#223741
Jun 03, 2020 11:39
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.