- Early access to new free plugin which speeds up imports even more! - October 14, 2024
- ChatGPT really sucks at SQL - August 9, 2024
- We’ve launched our newsletter again - July 24, 2024
Ok, so I love WooCommerce, and I love speed, but this one has been on the back burner for far too long. Almost all of the refund requests for Scalability Pro that I get are because users were hoping to see their sites with 1000s or 10000s or even 100,000s of variations per product speeded up.
If you’ve used the WooCommerce variations system manually, you might wonder how anyone could have 100,000+ variations since it gets so unwieldy and slow to use the editor above even 50 variations. The answer is imports. WP All Import makes it easy to import variations to your site from your datafeed you generated elsewhere.
Table of Contents
Understanding why variations are slow
The database structure used matters, and the mechanism WooCommerce uses to fetch the data matters too. Here’s an example using a sofa:
- Parent product: Deluxe ABC 2 seater sofa
- Variation 1: Material (Leather, cloth, linen)
- Variation 2: Colour (Blue, black, green, etc – imagine 30 colours)
- Variation 3: Finish (Walnut, Birch etc – imagine 20 of these)
- Variation 4: Size (measured in inches or centimetres, let’s say 3 options here)
Ok – so to calculate the total number of variations the above 4 options create, we multiple the number of options by each other. So – 3 x 30 x 20 x 3 = 5400 variations just from these 4 options.
WooCommerce actually creates all 5400 products as rows in your wp_posts table in the database and sets the post_parent to the ID of the parent product.
Now, technically, reading 5,400 products from a database should not be slow, but that’s not quite what WooCommerce does – it reads them one-by-one – and there’s a reason for it.
Why does WooCommerce read product variations one at a time?
The reason for this seems to be because of possible PHP filters. It’s possible for anyone to add extra PHP code to WooCommerce hooks and filters, and some of these hooks may, for example, exclude some of the variations based on the user, or they may adjust the price based on the country or any myriad of things.
Additionally, you may have filters enabled – e.g. just show me ‘blue’ couches, so WooCommerce needs to check each of the variations and filter them out so that it can show the correct price range and decide whether it should show the parent product at all.
So – if you load your archive page containing this sofa product above, using Query Monitor, you’ll see two important things.
Firstly – you’ll see a query that fetches 5,400 rows. This query will be fast, e.g 0.05 seconds or something, because that’s how fast databases normally are.
Then, if you group the queries by ‘component’, you’ll see WooCommerce making upwards of 5,400 SQL queries. That’s one query for the parent product, then one each for each child plus any other standard queries made by WooCommerce. On top of this, after each query it is then parsing that data using PHP and figuring out what should be displayed on the page.
What solutions are available?
The first, and most obvious solution is to avoid using product variations in this way. That may sound silly, but if it’s possible for you to adjust your product to use product rules instead of product variations then you will get your fast performance back.
For example, with 3 sizes: small, medium and large, we could have product rules of:
- Small (no change)
- Medium (+ £5)
- Large (+ £7)
So – if someone chooses a medium t-shirt, £5 will be added to the price. That’s far simpler than product variations and far faster too.
Ok, but most of you don’t have this option – all of your prices are unique – so what to do? Caching?
Caching can help – if proper object caching were used, WooCommerce could remember the final product displayed for this variation + this combo of search filters. But, there are a *lot* of products and a *lot* of possible search filter combinations, so this would result in a massive object cache which most of you would not be able to use on your teeny tiny servers.
The ultimate solution – if we ignore the hooks and filters – would be to alter the SQL query based on the search parameters and then filter in the database and use the database to tell us the price range. Again – this would skip any filters that were added to prices, although we could theoretically then run the filters on the final 2 prices (min and max price range).
An alternative solution, presuming it’s only the price range that variations care about, would be to remove the range altogether. I’m not convinced it’s a massive issue for consumers to see the top-end price range and in fact I think they’re used to seeing things like ‘Prices from £1,500’. The only way this could work, without altering the SQL query to filter based on the search query and category and attribute filters, would be to store the price-from against the parent product and then accept that sometimes this cheapest price would be wrong.
For example, if you have a t-shirt with red (£10), blue (£15), white (£20) for some reason, but then the user searched for ‘blue’ the ‘price from’ would still say £10. That’s not really ideal, BUT when the user clicks through to the product detail page the filters are no longer active anyway and the ‘red’ shirt would still be a selectable option, so it’d probably be understood by the users. This approach would be by far the quickest and easiest to implement.
What else do variations need?
I don’t use them much myself – it tends to be affiliated products I use which don’t have variations – but from all the client work I’ve done and all the sites I’ve seen and all the archives I’ve seen, the only thing I think it needs is the price range plus potentially filters on those prices.
It would be great if you could all answer in the comments below and help me flesh this out so I can fix WooCommerce product variations speed once and for all.
When it comes to the actual product detail page, that’s a lot easier to optimise through the use of Ajax and post meta values – the min and max price of the product do not change when there are no filters active, so we can just store them at admin-time and display on front end, and then changing the price per variation happens through speedy ajax callbacks.
Anyway, let me know your thoughts – I’d like to get moving on getting this fixed as it’s been a major bugbear for a while now.
If object cache can help product variations performance, I wonder what would be the simplest implementation to cache product variations to speed up single product page rendering?
The object cache already does this. The real issue is how WooCommerce implemented variations as complete individual products when normally all that changes in a variation is sku, price, stock and image. They have added some speed ups for archive pages, because previously archive pages – e.g. if they were calculating price ranges – had to go grab all the underlying variations rather than just fetch from an array and quickly display.
Curious to know which are the go-to Product Options plugins? Paid or free? I’m currently building a store and the variations are starting to get to be a bit much. It’s mostly apparel, some products have as many as 80 color options, then sizes some up to 10, and then to top it off 21 imprint variations. I already use object caching with Redis, but I can see where this is going to destroy performance even with object caching, this is clearly not an efficient way of handling this. While searching for what are some realistic max number of variations for a site to handle I came across this post. I’m thinking the colors and prints should be options and then only sizes be used for variations since 90% of the time only the size affects the price. I’ve just never used any product option before so I just don’t know what is considered to be the “standard” or can I just create them myself with Metabox. Are they easily stylable like with CartFlows Variation Swatches? Thanks
All of the product options plugins I’ve tried have been ok, the different architecture where there are no multiples of multiples makes them inherently fast.
Either Yith or Acowebs are good choices, probably best to avoid WooCommerce official plugins, they tend to be naff.
If you don’t need prices to change (e.g. fixed +€10 for large or +X%) then the free version of Acoweb would probably do what you need https://wordpress.org/plugins/woo-custom-product-addons/
I know this is a slightly old thread, but variations are really burning us.
We have per-variation SKUs, stock and pricing: It’s the way our whole product operations works, so there’s no way of using attributes.
Reading the code in detail and there’s no way this could ever be performant without using caching as sticky-tape to cover up the problems in the architecture. But we’re also multi-server to cope with scale which leads to a shared cache which itself becomes a bottleneck.
One thing I’d like to try is to use a redis cluster with readonly replicas in the same container as the webserver apps.
Anybody tried anything like that?
If I can shave off a few ms per call to get_post it will produce a noticeable effect, though the method is such a mess of concerns that even a cache-hit takes milliseconds per call!
I’ve used memcached when multiple web servers are involved before. Yes, one of the issues with moving multi-server with WordPress is that there are so many database calls so network latency and all the overhead of network calls becomes a real issue.
Why would it be read only though? You want the web servers to be able to update the redis cache if they need to, so why not just use a regular Redis cluster which is writeable by the web servers?
I take it you also have databases on a different server to the web servers then? Are you not seeing high latency from that? How many DB queries do you tend to get per page?
Hey,
Here is the next guy with the same problem. Can anyone give me a hint what addon / option plugin I can take a look at?
I have about 20k variations, all with an own price based on 8 attributes. I don’t need stock management., so options might be a solution?
I’ll also try redis and let you know here. What do you think how big this object cache an be?
Anyone some experiences with the siteground object cache? This is one thing I also got in mind.
Best regards!
If they all truly have their own unique prices, you’ll have to stick with variations.
If the prices change a relative amount, e.g. small +£0, medium +£10, large + £20 then you can use product options. There are a few of these plugins around.
Re: Object cache and siteground, I’m sure they can handle giving you a Redis Object Cache. This will probably give you your best speed boost. Once a product has been loaded once in e.g. product detail or in the archive, the product object is cached so next time on product detail or in the archive, there’s no need to refill everything. This works best if you have a few popular products which are viewed more than others.
That’s all fine until you need a separate sku for each variation. Attributes simply will not work alone.
We are experiencing painful delays when using the Wholesale Prices Plugin. This adds pricing levels to each variation. We’ve tried everything to get the pages to load faster, deactivating all of the plugins except for the woocommerce and wholesale prices plugin (by Rymera) and still nothing. When the wholesale prices plugin in deactivated, the site is super fast, with over 11,000 variations and 76 products. Not to mention 24 other plugins active.
This sounds like an issue with the Wholesale Prices Plugin. If you can enable Query Monitor and capture the queries from the wholesale prices plugin there may be optimisations I can implement through my Scalability Pro plugin.
Do that, grab the screenshots, then come paste those screenshots into our Discord Server so I can help you https://discord.gg/AqSpnCeQnf
I had this issue but I solved it an alternative way.
My variables was up to 7000 and after generating all variations I need to duplicate the product maybe 40x times with different image and text. So that is a lot of variation. Slow speed and ect…
The solution:
I created a single product so the add to cart button always active. I found a custom section addon so I added custom fields to the product page. Then I added a value to each option.
This addon isnt added the value to the price, but placed “additional price” so I overvrite with css and the single product price is 0,1 so this isn’t showed on frontend. If it is, then hide with css.
With this solution I have a multiple choise with price but zero variation. 🙂
I had to use a little css but thats okay.
Can you tell me which AddOn you used? Seems interesting.
Hi Dave,
Great to see someone talking about load speed issues for Woo – it’s painful.
I wonder if you have any clever ideas for how I could structure our website.. I’m building a bulk farm feed website where customers can either buy products by the truck load (a ‘unit load’ varies based on how many can fit on truck – typically 48-80 products) or by single product if the customer arranges pickup / delivery themselves.
I tried plenty of ways, then reached out to Woo team.. their suggestion was to create a single product, then a seperate bundle product of the single (for truck loads), then a composite product to choose between.. but it’s running crazy slow and very buggy.
Do you have any potential solutions I could try? Really appreciate it!
An almighty quest for sure! I’m reading many comments mentioning “product options” but I feel I’m missing something obvious here. I can’t see any plugins or native features for adding product options. Perhaps it’s a reference to “Add-ons”?
If that’s the case, as far as I know, it’s not possible to manage stock levels of combinations with add-ons. Is that the case?
Thanks for the efforts!
Yes if you need stock options per combo then you need variations. If the options do not affect stock levels then you should options – add ons, options, called different things by different plugins and yes this functionality is unfortunately not native. Unfortunately you only get variations as native functionality for handling this stuff which leads to performance issues when people use them for things that would be better served by product options or product addons.
Apologies for the lag, thanks so much for your response! I need to be able to manage inventory for each variation, so will need to work out the best way of using variations.
I’ll try the redis plugin that has been mentioned many times, and I was also wondering if there would be a way to load the product page, THEN load the variations… however I can’t see how to do this. It seems when you click a link to the product page there is a lot of queries in the pre-load phase. I have a lot to learn!
Thanks again!
Hello Dave !
We lunched a Woocomerce website ; https://secondemain-brand.com/
As you can see on mobile version espacially the loading is really slow 🙁
I think it’s because variation product, each piece is unique so we had to install this plug in.
We have already used WP-Rocket but not to much effect. Do you thinkg buying scalability pro is a solution ?https://www.superspeedyplugins.com/product/scalability-pro/
Thanks !
Richad
The best things you can do right now is to try and reduce how many variations you have and use an object cache. The object cache helps with variations a lot, but if you have > 1000 variations then things will still be slow.
If a variation changes the price consistently and you do not have different images for that variation, then probably this could be a product option or product addon.
Scalability Pro does not help with product variations. It helps with many other areas, but not product variations.
please tell us more about woocommerce product rule and how to achieve that on our site
If you have variations where a variation changes the price by a specific amount – e.g. medium + £10 or large + £20 then you can have them as rules rather than variations.
If you have varitions where the price doesn’t change and the images don’t change, you can have them as product addons. It’s a shame variations are the only default included in woo because people overuse them and end up with a slow site.
Hi Dave, I’ve been keeping an eye on this.. got an ETA?
No firm ETA yet, sorry. Schedule pretty much looks as follows: This week & next is bug & perf fixing + FWW testing. FWW should be released next week I think.
After FWW is released, I’ll have a sprint dedicated to Scalability Pro – there are quite a few potential things to include in that upgrade.
I haven’t prioritised this as high as maybe I should since there are workarounds that really help – reduce how many variations you have (e.g. using product options or product addons) and use a persistent object cache (e.g. redis or memcached). On top of that, recent versions of Woo have included ‘helper’ tables to improve performance, so I need to take time with a proper Scalability Pro sprint to examine how I can use those tables to further optimise, or whether I actually need to go the whole hog and create my own tables to improve performance.
Has there been any further progress in Scalability Pro to speed up variations? Are there any new alternatives apart from the ones mentioned (Redis, Product Options etc)?
Any further improvements require custom coding – but in most cases, you can make it work although sometimes you need to customise the code a little.
For example, there is some code in a lot of themes which alters the ‘sales flash’ to show a ‘percentage discount’ – this code got copied from the businessbloomer website where it had been submitted very badly coded. Basically, they looped through all child variations and got the prices from them. This involved loading each product variation. The code rewrite for this involves using a function available on the parent product to get child variation prices in an array. This is maintained at the parent level and is far faster, uses less RAM etc.
So – if you have a performance issue due to product variations, there’s a high chance that your codebase (some plugin feature or some theme feature) suffers from this bad coding pattern.
If you search for get_children() in your code base and then look at that code, if you see the code is performing a loop and then creating an object with each child variation then this code could probably be rewritten.
Hi – We have this exact issue on our dev site with a few products that have 500 plus variants. Any update on this would be great, but in the meantime would there be a benefit in installing Scalability Pro anyway re this issue? Thanks, this article was really helpful, not least because I know we are not alone!
Hi Peter,
I see your site is fast now, what solution did you find?
Any new headway on Scalability Pro? I recently purchased it and am having the same issue. Currently I have about 800 different variations on a particular product and my page takes ~10 seconds to load. I am Scalability Pro and still not fully happy with the speed.
I am using Product Options for products that allow it due to pricing but unfortunately we do have some products that really need the pricing flexibility of variable products because certain options do not have fixed price mark up depending on variations.
This is my example page where I am running into the issue currently: https://deltakon.com/product/additel-adt-681-digital-pressure-gauges/
Hi – the update for variations is not out yet and not in the current sprint – you can see the Trello card for it here: https://trello.com/c/WXbeCZ21/88-scalability-pro-speed-boost-for-product-variations
Make sure you have an object cache (Redis is best, but Memcached is also good). It’s not a perfect solution but it does help a bit.
Aiming to have this fix implemented in February.
Hi Dave!
I’m currently running a prize draw website and we use product variations for ticket numbers, which means the pages for prizes with over 200 ticket numbers run very slowly. I’ve seen on the Trello board under the current backlog there is “scalability pro – speed boost for product variations”. Do you think once looked into this would help with variations so large (up to 800). Or any advise as to whether there is any sort of other way round this to boost the speed for these pages?
Do you have Redis object cache installed and configured? That’s the number 1 thing to boost variation speeds currently.
Hi Dave,
Thanks for this. My search for speeding up woocommerce continues :-).
I am using variations for size and colour of clothing. Each size and colour of a T-shirt has a unique sku and image which is needed for ordering from our supplier.
The front end site using cache for non logged in users is very fast, however caching is not enabled for logged in users so the site runs like a dog for them. When I login to my Admin panel it is also severely slow.
Would you have you any advice on this?
Also – with variations, there are plugins and theme features I’ve seen a lot where the code loops through each product variation on the archive page. For example – if you have functionality to change ‘ON SALE’ to ‘-20%’ or similar then that is very frequently badly coded.
I’ll be adding some functionality to Scalability Pro to scan the plugin and theme codebase to identify potential causes of slowdown.
Make sure you have an object cache in use – those can be used by logged in and logged out users and help a lot with product variations.
Hi Dave! great website with great information and plugins. This article drew my attention particularly as I‘m having the exact problem described above.
I wouldn‘t mind prices stated „from 100$“ at all, even when filters are active. The load time is just not acceptable if one has over 200 variations.
When do you think the plugin to fix this will be ready for sale?
Faster Woo Widgets already improves this quite a bit, as does Scalability Pro, and also adding an object cache helps.
I’m not sure when I’ll get the upgrade to fix product variations permanently, but it’ll definitely be this year.
Hi! Is there a fix for this yet?
Hi, yes, to be fair it’s been fixed for a while. Just when you’re logged out only, so when you’re looking at status = published rather than status in (published, private) then it’s quite a bit faster with scalability pro.
I’m working on some case studies to demo this difference, will publish soon. In any case, if it doesn’t help you enough we have a 60-day money back guarantee.
hi dave,
using woocommerce composite products will be better at handling this no?
https://woocommerce.com/products/composite-products/
Yes – if it’s possible with your config. Product Options can help too. Variations are really intended for anything that changes the price in a unique way. So – if you ave product options that when enabled add £10 consistently for option A, +£30 for option B etc then options are a better choice than variations.
Variations give the advantage that the specific combination of options can have its own totally unique price, but the major downside that they really slow down woo.
I’ll get back to working on the fix for this soon – got other updates coming out shortly.
I am in the same boat. I have wp-all-import plughin with 55,000 variations on a product which just drowns any server I have put it on. I currently have upgraded to an XL VPS – still crashing. So please tell me about “The ultimate solution”…sql query…kindly elaborate in laymans terms
Is it possible for you to change any of your variations to product options? e.g. if there are any variations which don’t alter the price at all, they should be made product optoins, and if you have variations that change the price by a consistent amount across all other variations then they should be product options too. E.g option 1 +£0, Option 2 + £15, Option 3 + £30 etc
I don’t think so, these products are all unique and have different prices. Well they are the same product but all different variations of customisation.
Ok, then so far, the best approach to speeding these up is to add Redis to your site and the Redis plugin by Till Krus. My upgrade to fix this further is not yet ready.
Do you mind sharing a link to this redis plugin so I can ensure we are talking about the same thing? Also, will this resolve my issue then?
https://wordpress.org/plugins/redis-cache/
You can also use W3 Total Cache – it’s in the Object Cache settings page there. In fact, W3 Total Cache gives a bit more control but the one above I use a lot.
yah.. understanding why variations are slow.. you already add more things .. and absolutely correct you are! your content is really expressed your merit .. this post help to search the topic.. thanks for your information.. your example is more helpful to clear the topic.
Wow, finally a post about someone who has the same problem as I have.
I had no idea that the slowness of my site was due to the variable products.
Almost all my products are variable products (I sell everything in 6 finishes) and my stock management is far more important than the prices.
In this case showing the starting price “Prices starting from £10” would well do it already.
If this was implemented though, I’m having some doubts how accurately the search could pick up the right variation price as with the color example, and if it would have much actual benefit for the customer (store largely selling clothes). Looking at search statistics there’s barely any searches (<1%) for a color, or size for that matter that could narrow down the search like this. Also, often if there's a color option there's a size option too so it might add complexity to this logic.
The shop does not have separate images for variations so the parent images are always used.
This has been the major pain on a site I’ve been working on (10000+ products, some with 1000+ messy variations migrated from long running Magento shop) and got to similar conclusion. For my case it would be enough to have “prices starting from” shown on archive pages as transforming product data to use product attributes/rules would take a lot of manual effort.
Thank’s for the post and your continuing efforts!
Ok cool – and what about the scenario where there is a blue (£10), red (£15) and green (£20) variation and the user searches for red?
I am fairly certain I can actually filter this quickly at the DB level and get the prices from and eliminate the need for the per-variant code, and in that case, I’ll be able to show: £15.
But would you want the ‘red’ variation image to show or the parent product variation image?
And would you want it to show price: £15 or ‘Prices from £10’ in this case too?
Hi Dave,
thanks for that interesting post. We have a ticket open so I guess we`ll be communicating through that.
Best regards
Sven
One possible solution is just using Attributes, depending on the business data management needs:
– https://iconicwp.com/products/woocommerce-show-single-variations/
– https://iconicwp.com/products/woocommerce-linked-variations/
– https://iconicwp.com/products/woocommerce-attribute-swatches/
Yeah attributes are useful IF the price doesn’t change per variation, but I have many clients who have real need of the price changing per option-combo.