Gold R10 What decides if variants are written to disk?

[AbleMods]

We've got a client who uses variants extensively. Many products have dozens of possible variants.

Yet I look in the ac_ProductVariants table and I see 1 record. There should be thousands of records.

How is that possible? I thought all variants were persisted to disk? If not, they what's the deciding factor?

We've seen noticeable performance degradation on product pages loading when the product has a lot of variants. We can't pin it down.

We also tried loading all the variant skus in the product.SearchKeywords field so shoppers could search by variant sku. All that did was kill the speed of SQL FTS search, some products had huge SearchKeywords values because there were so many variants.

Any suggestions would be a great help.

[mazhar]

In order to make performance better variants are only saved in database when you change some field values to other then defaults. Try using different SKU and then check database.

[AbleMods]

I see. So as long as the default matrix is unchanged, variants are always memory-based.

Is that why the SQL FTS doesn't include variant sku?

[AbleMods]

So the more I think about this, the more concerned I am.

Based on the design you've described, how does anyone do a search on for a particular variant? Say I want to search by variant sku, or perhaps something in a variant name.

Normally we would do an nHibernate query against the ProductVariant class. Does that still work even though they're in memory now and not persisted to the disk?

[jmestep]

I'm interested in the variant sku search question also.
Joe, we had a client who needed a variant id for a number of reasons. I found out what field in the variant they weren't using (COGS in this case) and put a .01 in that value every time options were saved so that the variant id would be created.

[mazhar]

So the more I think about this, the more concerned I am.

Based on the design you've described, how does anyone do a search on for a particular variant? Say I want to search by variant sku, or perhaps something in a variant name.

Normally we would do an nHibernate query against the ProductVariant class. Does that still work even though they're in memory now and not persisted to the disk?

By default generated variants are not saved in database unless you manually trigger save. You can create a sample product with few options and then check the behavior by running query against ac_ProduvtVariants table. Admin searches for standard SQL provider do support variant SKU. In our application since search always lists the base product so if sku is found any of its variants or product itself it will be listed.

I just confirmed if you trigger save without modifying any variant data from Mange Variants page it still saves them in database.

[AbleMods]

Mazhar I understand the behavior, that's not my concern.

Let's try this a different way.

How do I search for a variant sku in a store? Just call the regular AdvancedSearch() method like we always do?

Judy's idea makes sense. But that's a horrible way for a programmer to have to handle the situation.

This entire app is driven by data physically stored in tables. Except (now) for variants. Deviating from such a core design standard is going to come with a big price later in life. It limits what we can customize with the app. It limits how we can integrate the app with outside systems. It will forever have to be treated differently than all of the other entities.

This should be handled with a flag in store settings "Optimize Variant Performance" so each store can decide for themselves how it should work. Not every store has the need for this variant optimization. As a developer, I need the ability to access variant data in a manner consistent with the rest of the app.

[Katie]

Joe,

I totally agree, and for the record, I had no idea this was happening or when the change was implemented. I'm wondering if all Gold versions behave this way, or if it's something we changed in a later version. Obviously, someone was trying to improve performance, but perhaps they didn't think it all the way through...

Thanks
Katie

[AbleMods]

Hi Katie.

Thanks for the feedback. I've been wondering the exact same thing...how did I miss this for as long as I did.

I almost want to say "Well, it's because most of the clients are Able 7.x upgrades and the variant records already existed. So the Able Gold system just kept using them."

But my most recent client was an Able 7.x upgrade. And they have thousands of variants. Yet the ac_ProductVariants table has only 1 record.

So that blows my theory rather quickly

Either the default variant matrix records are deleted as part of the upgrade to Gold. Or a part of regular maintenance. Or, this was the situation in Able 7.x and I simply never knew.

[jmestep]

Katie, I don't remember how far back this was happening, but I know it was happening in AC7. I remember I had to do a work-around on an AC7 site that wanted to be able to bulk enter UPC codes for variants for a customization to Googlebase feed when Google started requiring it. I (think) I remember that when I first started testing sites with Red-Gate performance monitor that building the variants into memory through the site was the slowest. It seems like it might have been something that was the slowest on checkout, but I could be remembering incorrectly.

[Katie]

Thanks Judy. Well, maybe I'm not losing my mind after all.

[deverill]

It is purely anecdotal but we have a store that is GOLD R8 and it has 922 products and 3689 Product Variants and each product has 4 variants so that seems like it is not optimizing at all. The database was updated from AC 7. The store is so customized we may be doing something "accidentally" that's forcing a save but I don't think so.

For what it's worth,
Jim

[jmestep]

I've been thinking-dangerous.
I think that someone in the past had mentioned that the reason variants are not always stored in the database is because of how large the table could get if someone has several options with several choices.
I just did a quick test on an R9 site I had open and added 4 options with 4 choices each and on the manage variants page, it showed 256 variants.
(I also clicked to save on the manage variants page without changing any of the data, and it did not create entries in the product variants table.)
So I guess the test here would be to find out whether accessing the database when loading variants is faster than building them on the fly.

[AbleMods]

I've got a client with 7,000,000+ records in the variants table. Performance is just fine.

Building in memory would very likely be faster. But the improvement would barely be noticeable in the typical use scenario like a product page. When accessing variants for display on a product page, you're dealing with a very, very small subset of the total data. Even in the magnitude of millions of records, you would barely be able to tell the difference. What you would see is less hits to SQL.

So whether it's built in memory, or queried via SQL...it's not going to be all that much different. One less hit to the database just isn't a huge savings in a database-driven app like Able.