Best performance wise solution for getting SKU's relations and pr

Best performance wise solution for getting SKU's relations and prices

Madis Vellamäe

Vote:

Hi!

We have a business requirement to add SKU's parent product code into Solr index. Also, for parent products we need to add lowest price from child SKU.

At the moment I have overridden CatalogIndexBuilder OnCatalogEntryIndex method. For getting parent product, I tried to use LinksRepository GetRelationsByTarget and then load the parent product using ContentLoader. I also tried with CatalogContext.Current.GetCatalogRelationDto(entry.CatalogEntryId) to check if SKU has relations and then use CatalogContext.Current.GetCatalogEntry(relationRow.ParentEntryId) to load the parent. We have about 10k entries in our catalog and both of these solutions will make IIS app pool use more than 3,5GB of memory, which will make the indexing process run for hours or even halt.

For getting prices I have used ContentLoader and IPriceDetailService.

I would like to know what kind of solution would be the best, regarding memory consumption, for loading SKU's price and it's parent product and same level SKU's under that parent.

We're using Commerce version 8.16

#136643

Sep 17, 2015 10:53

Quan Mai

Vote:

Hi,

This sounds like a place where our Expert Services can shine :)

However, there are a couple of things you can try:

Loading in batch is (almost) always cheaper. Proper caching makes it even more effective.

You can write two custom stored procedures:

- One loads all variations and their products code

- One loads all products and their variations code

Now the tricky parts. At first call to OnCatalogEntryIndex, call those two stored procedures to fill in two dictionaries (with possible cache time out of 30 minutes, you decide).

Now for each OnCatalogEntryIndex, you just use the information cached in the dictionaries, without having to get information from database again.

Regards,

#136722

Sep 17, 2015 14:31

Madis Vellamäe

Vote:

Good idea, Quan, thank you! I'll try it out.

At the moment I was able to reduce the memory consumption by shortening the catalog cache to 5 seconds which was the optimal time considering performance vs memory usage. Since we're listing products/SKU's from Solr anyway, the web didn't get much performance hit.

#136738

Sep 17, 2015 14:41