Developer forum

Hi there,

Can someone advice me how to best cache products? I need to find a way to quickly retrieve specific products without a lot of overhead. Here's my scenario:

I need to display a list of products, where for each product the "preferred variant" must be shown. Preferred in this case means it contains a specific color. I can't show the original product as I need variant data such as price, options and custom fields.

Here's my current logic:

foreach (Product product in Products)
{
  foreach (VariantCombination vc in product.VariantCombinations)
  {
     if (vc.IsMatch())
    {
      productCollection.Add(vc.Product);
    }    
  }
}
return productCollection;

IsMatch is a (fake) Extension method that determines if the product is "preferred".

This works fine, but suffers from the N+1 issue; I fire one select statement for the list of products, and then fire a separate query for each product to get the associated variants as they are lazy loaded. Performance tests show that this is slowing things down considerably.

What I am thinking about is loading all products on application startup and loop through the variants to lazy load them, and then cache the results. I can do in-line querying against this cached collection. Alternatively, I could build up the cache for each product I loop over, so I only take the performance hit once.

Is this a good idea? I can potentially have up to 100 products, each with around 20 to 40 variants, leading to 4000 cached products. Is that a bad idea? Any experience with this amount of cached data?

Or are there better solutions available?

Kind regards,

Imar