search

wp-e-commerce / WP-e-Commerce

WP eCommerce - The most popular independent eCommerce platform for WordPress
https://wpecommerce.org
GNU General Public License v2.0
215 stars 217 forks source link

Variation Improvements #100

Open JustinSainton opened 11 years ago

JustinSainton commented 11 years ago

Naturally, variations have improved significantly over the years - it's an area that a lot of carts do a lot of different ways and I think we do it the best. That said, there's a lot we can improve on. I think it would be a noble effort to make iterative improvements in every release. I believe we have a ticket open on Google Code (that I don't think has been migrated here yet) about overall structuring, product options vs. variations, etc.

That's probably outside the scope of what we can do in the 3.8.10 release - but there are some things I would like to see happen.

First off, @joshlevinson has opened several really great bug reports or UX enhancement reports regarding the variations UX - we should aim to resolve these (or at least the ones that are manageable for this release).

Also - I hear constant reports of memory/performance issues when people have tons of variations. We can wag our fingers, tell them their doing it wrong (because most of the time, they are), tell them to use Lee's Product Options plugin, etc. But this is an area we need to at least look into to see where we can improve. My initial instinct is that wherever we're inserting the posts, we're probably doing way more than we need to. We should identify what, if any potential bottlenecks are occurring there and attempt to optimize more.

JustinSainton commented 11 years ago

https://twitter.com/PCDxGeorge/status/290937256452165632 - the most recent time I've heard of this occurring.

JeffPyeBrook commented 11 years ago

I would be interested in contributing to the variation enhancement effort. Our store has 8000+ products and 70000+ variations. I have had to tweaked (and reported as issue) some of the SQL in the variation handling to keep our DB server from falling over, timing out, and worst of all table locking the whole database. So I know where some of the issues live.

Also, our store is built on a custom templating system built on the WPEC variations. The templates control which product options get automatically associated with each created variation. I have some thoughts on how this could be easily generalized and provide useful functionality to everyone.

Jeffrey Schutzman sparkle-gear.com / http://www.sparkle-gear.com Sparkle Gear jeff@sparkle-gear.com (978) 561-9668: <1-978-807-5309> voice/mobile/sms Our profiles [image: LinkedIn]http://www.linkedin.com/pub/jeffrey-schutzman/0/a/145 [image: Google Plus] http://plus.google.com/118269319889151761287/ [image: Twitter] http://twitter.com/jeff5309 [image: Facebook]http://www.facebook.com/pages/Sparkle-Gear/321045851284159 [image: pinterest] http://pinterest.com/sparklegear Contact me: [image: Skype] SparkleGear [image: Google Talk] jeff@schutzman.net IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.

On Mon, Jan 14, 2013 at 5:27 PM, JustinSainton notifications@github.comwrote:

https://twitter.com/PCDxGeorge/status/290937256452165632 - the most recent time I've heard of this occurring.

— Reply to this email directly or view it on GitHubhttps://github.com/wp-e-commerce/WP-e-Commerce/issues/100#issuecomment-12243453.

JustinSainton commented 11 years ago

Jeff,

Your insight into this issue would be invaluable. Anything you've learned in working with variations that we could apply to core would be enormously appreciated. Again, incremental, iterative improvements is the goal - if you've come across something that we can implement at that level - we'd be all ears! This is a constant issue and if we can start to mitigate it without steering the variations ship 180º (rocking the boat here at a systemic level would be sure to break themes, plugins, etc.) - that would be phenomenal.

instinct commented 11 years ago

Thanks Jeff :))

Best, Dan

On 15/01/2013, at 12:23 PM, SparkleGear notifications@github.com wrote:

I would be interested in contributing to the variation enhancement effort. Our store has 8000+ products and 70000+ variations. I have had to tweaked (and reported as issue) some of the SQL in the variation handling to keep our DB server from falling over, timing out, and worst of all table locking the whole database. So I know where some of the issues live.

Also, our store is built on a custom templating system built on the WPEC variations. The templates control which product options get automatically associated with each created variation. I have some thoughts on how this could be easily generalized and provide useful functionality to everyone.

Jeffrey Schutzman sparkle-gear.com / http://www.sparkle-gear.com Sparkle Gear jeff@sparkle-gear.com (978) 561-9668: <1-978-807-5309> *voice/mobile/sms

On Mon, Jan 14, 2013 at 5:27 PM, JustinSainton notifications@github.comwrote:

https://twitter.com/PCDxGeorge/status/290937256452165632 - the most recent time I've heard of this occurring.

— Reply to this email directly or view it on GitHubhttps://github.com/wp-e-commerce/WP-e-Commerce/issues/100#issuecomment-12243453.

— Reply to this email directly or view it on GitHub.

garyc40 commented 11 years ago

Let's try to come up with a list of actionable items before giving this ticket a milestone. I have doubts that we can fit this into 3.8.10. But once we have a list (i.e. after we've identified the bottlenecks that Justin mentioned, and after @SparkleGear gave us some feedback and ideas), we can start thinking about which item fits into which milestone.

JustinSainton commented 11 years ago

We probably need a BarackObama tag. Highly aspirational work that may or may not be overly idealistic.

Had a great chat with Jeff the other day (feel free to chime in, @SparkleGear). Sounds like the approach he's had to take was a highly customized route of essentially replacing the current variations routines entirely and using variations utilizing taxonomies as the data object model rather than posts. Naturally, this would be impossible for us to do in one release, and certainly not in this release, if ever.

That said - the performance increases using taxonomies as opposed to posts are pretty incredible. I told Jeff that my goals for this ticket, for this release, are three-fold:

I guess the secret 4th thing we need to do (and maybe it's part of items 2 and 3) is education. We need to make sure we're educating developers and themers on a lot of things - a few come to mind:

  1. The fact that we currently have this widely documented performance limitation, and we're working on it.
  2. The difference between variations, variation groups, variants, etc. I'm not opposed to a naming convention change, as even I get confused.
  3. The roadmaps for our intention first, and the new APIs as they're developed. This would be a potentially great candidate for a new component (like the new payment gateway).

Please forgive the free-flow thought form. Hopefully between Jeff and I (and hopefully others as well) we can identify some actionable improvements for this release while paving a path forward for a brilliant variations system.

JustinSainton commented 11 years ago

Went looking for a list of Google Code tickets that @SparkleGear commented on re: variations. Here's a couple performance enhancement tickets he opened (neither directly related to variations in the admin, unfortunately) that I think would be worthwhile to consider. Probably separate tickets.

benhuson commented 11 years ago

I love Justin's "ideal world" solution :)

Variations is always a tricky one so I want to throw a few considerations into the mix if we're thinking about roadmaps.

I'd love to see someone be able to integrate WPEC with a stock management system such as Sage 50 (or other). It would then nicely span that gap between small/medium sized businesses to larger requirement. I've actually had a few clients interested in this in the past but it would be quite a comprehensive plugin to build and I've not dared tackle it as yet (I don't know if anyone has done this yet, I haven't looked recently).

One important considering if integrating with a stock system would be that such a system would deal with product variations as individual line items with unique SKUs - almost in the same way that all our variations are currently treated as posts.

It's therefore essential that individual product variations can be queried efficiently by SKU and updated - not a problem if we stick to posts but a consideration if moving away from posts and how we store variation SKUs and meta data.

Most other e-commerce systems I have used or had experience of store product variations as simple products grouped together by SKU or ID (a bit like like a post parent).

It might be worth taking a look at Magento. They deals with Grouped Products (essential like Lee's Product options plugin) and Configurable products (like our variations where they also store each variation as a separate product). http://www.magentocommerce.com/knowledge-base/entry/product-type-roadmap http://go.magento.co.uk/support/kb/category/name/product-types/ http://go.magento.co.uk/support/kb/entry/name/creating-a-grouped-product/ http://go.magento.co.uk/support/kb/entry/name/creating-a-configurable-product/

I think it's a bit confusing how Magento makes you choose the product type; Simple, Virtual, Gift Card, Grouped, Configurable, Bundle and I don't think that is necessarily the right way to go although it is an interesting approach - a bit like how it sounds WordPress is going to go with post formats, displaying a different edit screen layout based on the post format you select.

Magento have obviously done it so they can store and query the data in the most efficient manner depending on the type of product. They do try to make it clear to the user that basically the difference between a Bundle and a Configurable product is you should use Configurable if you need stock/inventory tracking at that level. They're quite good at educating the user in that respect.

I wonder if we can learn something from that and maybe where you manage product variations you can initially chose wether you want to create "Product Options" (a simple list of product options) or "Product Variations" but somehow make this a simple non-confusing choice?

I'm 100% for anything we can do to improve speed, stability and optimise variation handling wether this involves changing from using post type or not.

However, I would like to flag that at the moment I feel that the data structure (variations as posts) makes sense for product variations, and wonder how much improvement can be achieved by better querying and not querying stuff that's not needed etc. Using taxonomies doesn't feel like the correct solution (unless offering product options as a solution) although it make give more efficient querying and results for many users.

PS. I'm no database expert :)

garyc40 commented 11 years ago

I believe there are much room for optimization with the current post-as-variation structure. At least we should provide better support for object caching, so that big sites can rely on memcached or APC to optimize db usage. That's the best we can do in terms of db optimization, in my opinion.

Certainly, switching to another db structure (like Justin suggested) would be ideal, but I doubt that it's going to be practical in the next 2 or 3 releases. That structure will potentially have the its own issues, and not to mention db migration for existing users. The best we can do to smooth that out is refactoring our current variation API as a component (like what we did with merchant api v3) and gradually switch it out of the core in favor of a new API which is developed in parallel in the form of a separate plugin. But again, I doubt that it won't have its own set of issues.

I still think where we can make the greatest impact for the average user would be the UI. Most of these sites don't use thousands of variations (if they do, we should educate them to utilize memcached and APC). A better UI that lets them make the most out of our current variation system, while still masks away the ugly aspects (variation generation), would be both ideal and practical.

JustinSainton commented 11 years ago

Another possibility - and not a mutually exclusive one: Work with Jeff (and maybe @leewillis77, too) to create a canonical plugin of sorts - one whose only role is to do exactly what these guys are already doing - having to bypass the entire variations system because they're unable to use it with decent performance.

I could see it even being a fork (or feature) of Lee's Product Options plugin - but perhaps it would be closer to a matter of Jeff (@sparklegear) open-sourcing what he's done and we can build upon that, do it how we'd do it in core - and see if it can be a way to meet the real need of that minority group of people who, for whatever reason, need a ton of variation combinations.

Any thoughts on this approach?

benhuson commented 11 years ago

I agree with Gary that refactoring as an API first is probably the smartest move as it will then make it easier to migrate data structures later if required.

I also like the idea of making the variations functionality pluggable so they could be replaced by a plugin but I guess this would require the above API to be built first with the necessary filters in place.

Here's more details on the direction WordPress is trying to go with Post Formats as I mentioned in my comment above which relates more to educating the user between the difference between Variations and Option if was decided we need to give users a choice as to how to configure a product. http://make.wordpress.org/core/2013/01/18/post-formats-ui-update-117/

JeffPyeBrook commented 11 years ago

In addition to the Google Code issues Justin noted above, there are a couple of other important fixes related to variations that were submitted under @PyeBrook

 GC 1197     save_term_prices crash when using wpsc-variation taxonomy 
 GC 1198     Patch for /tags/3.8.9-beta/wpsc-admin/ajax-and-init.php
JeffPyeBrook commented 11 years ago

Sorry for the delay in getting more information to the group. Made some progress over the last 48 hours getting our store running on WP 3.5 and WPEC 3.8.9.5. The issue that was tripping us up is documented in #136, and is directly related to Product and Variation Scalability.

As soon as our dev system is stable again i'll have some cycles to write some more about what we have learned about variations and options that change product prices.

benhuson commented 11 years ago

Just a thought on the subject of speeding up variations.

At the moment does the query get all products, then as it's looping through them do a query for each set of product variations individually?

Is there any way to do one call for all of the products, then another single call for ALL of the variations based on product IDs, and then just match them in the loop which would only result in a couple of queries rather than one per product?

I think that's how WordPress works for post meta? Could we get variations the same way?

JustinSainton commented 11 years ago

Yep, I pondered the same thing in my essay of a comment a few weeks ago - https://github.com/wp-e-commerce/WP-e-Commerce/issues/100#issuecomment-12508187 - point 1, sub-point 2 :)

I think that would be awesome to have it work that way.

benhuson commented 11 years ago

So you did. Would make huge savings when displaying many products on one page

JustinSainton commented 11 years ago

Absolutely.

JeffPyeBrook commented 11 years ago

In addition to the API/class we might want to think about a set of actions or filters to use inside themes to render the variation controls. I was thinking about this over the last two days so that I can use the built in variations when they make sense, but also use the product options when it may be appropriate.

If we only wanted to allow one possibility to render the variations an action would make sense, a plugin could remove the action and replace it with it's own logic.

I think I prefer a filter that would be invoked before the default processing inside WPEC plugins. If any filter processeed 'wpsc_show_variation_select' it would return the content for the theme, and the default processing inside WPEC to render the controls could be skipped. This approach should't break any existing plugins or themes.

To handle this problem now what I have done in my themes is bracket the variation control rendering with buffering so the controls don't get created when variations are used but child products don't exists. The product options are rendered as part of the form fields actions processing.

<?php $count = 0;?>
<?php ob_start(); ?> 

<?php if (wpsc_have_variation_groups()) { ?>
<div class="wpsc_variation_forms">
<table>
<?php while (wpsc_have_variation_groups()) : wpsc_the_variation_group(); ?>
<tr><td class="col1"><label for="<?php echo wpsc_vargrp_form_id(); ?>">
<?php echo wpsc_the_vargrp_name(); ?>:</label><br />
<?php /** the variation HTML and loop */?>
<select class="wpsc_select_variation_ajax" name="variation[<?php echo wpsc_vargrp_id(); ?>]" id="<?php echo wpsc_vargrp_form_id(); ?>">
<?php while (wpsc_have_variations()) : wpsc_the_variation(); ?>
<option value="<?php echo wpsc_the_variation_id(); ?>" <?php echo wpsc_the_variation_out_of_stock(); ?>><?php echo wpsc_the_variation_name(); ?></option>
<?php $count++;?>
<?php endwhile; ?>
</select></td></tr> 
<?php endwhile; ?>
 </table>
</div><!--close wpsc_variation_forms-->
<?php } ?>
<?php /** the variation group HTML and loop ends here */?>
garyc40 commented 11 years ago

@benhuson

At the moment does the query get all products, then as it's looping through them do a query for each set of product variations individually?

If you're talking about the variation list in Product Edit screen (wp-admin area), then no. Most of the time only 2 queries are run to fetch the variations:

benhuson commented 11 years ago

No, I meant on the front end.

Try did a quick test within the main WPSC_Query - after product IDs received do a single query for all variation products for any of those products. The resulting posts get cached.

Not a huge saving: Adds 1 query and removes 1 query per product on the page, but that adds up if you have a category without pagination showing 40+ products

Worth considering?

instinct commented 11 years ago

Might be a silly question... But what happens with infinite scroll? Fast becoming a popular feature on many a WordPress site?

Sent from my iPhone

On 15/02/2013, at 11:11 PM, Ben Huson notifications@github.com wrote:

No, I meant on the front end.

Try did a quick test within the main WPSC_Query - after product IDs received do a single query for all variation products for any of those products. The resulting posts get cached.

Not a huge saving: Adds 1 query and removes 1 query per product on the page, but that adds up if you have a category without pagination showing 40+ products

Worth considering?

— Reply to this email directly or view it on GitHub.

benhuson commented 11 years ago

Another thought on improvements for speeding up variations...

If a product has variations, when saving that product would it be a good idea to update the main product price to be the lowest variation price and add meta data to the main product to store max variation price? and the same for sale prices? Using meta data to 'cache' these values.

That way if you are just showing images/title/pricing information at a category level it doesn't have to query variations at all, it can just use that meta data to display the to/from prices.

JeffPyeBrook commented 11 years ago

Duplicating that data there would just give one more place data has to be updated when a variation price changes.

IMHO the process of updating prices when the underlying variation prices changes is already likely to have problems when there are a large number of variations. One more will make the issue even more problematic. On our server the update process could only be done when there is no web traffic because it locks the database for over 3 minutes. The update process is also very prone to timeout issues. We started seeing problems at around 10,000 variations, on-tenth of what we have now.

benhuson commented 11 years ago

I was just thinking that if it only had to 'cache' the max-min price of a product when that product is saved it would not have to do all those extra variation calculations on the fly?

JeffPyeBrook commented 11 years ago

You are definitely correct that this is a problem. In my options plugin that changes final product price I need this and other product information often.

What I do is have the color / size combination prices in an array that is attached as post meta to a post that is located with a query of the parent variation terms. The sql to get that singular post is efficient, essentially a simple tax query with the parent variation terms. Because the prices are all together the number of records that you have to look can be less than 5% of what is required when the price is stored using the variations.

To maintain backward compatibility with WPEC whenever the price is changed in the edit screen for the variation terms combinations it updates the WPEC array that is stored in the options table. I also just started using the same technique for product images when I have front/back/left/right views of items.

I also have some ajax that goes through and updates the item prices after a change. The standard WPEC variation price update doesn't work in my environment because of the database size and timeout issues.

This is what my edit variation price meta box looks like for a typical t-shirt in our store: http://www.evernote.com/shard/s188/sh/bbb3864e-ac01-4205-8efc-7cc0a87dd3d8/4cd3ed882e6cc87c484c9b1e2fb10177

It is much easier for the non-techies to understand the price updates they are making as opposed to the variation taxonomy editing.

benhuson commented 11 years ago

For your particular need of colour/size variations that UI works well.
I work on a few sites that have 3 variation groups per product in which case that grid UI wouldn't work :(

My thoughts behind at least caching the minimum variation price and maximum variation price on the parent product was then it would make a huge saving of queries when viewing categories as do would not need to query any of the variations at all (unless showing the variation drop-down on category pages) and also it would mean you could do more efficient price querying - for example sort by price, viewing product by price range etc.

I know it could be seen as duplicating data, but I think that structurally using child products for variations is correct - each should be it's own line item and have the potential to be completely customisable. So I'm just thinking that caching the variation information that is commonly used like min/max price would speed things up.

JeffPyeBrook commented 11 years ago

Your correct that the change would speed things up. But if you really want to see a speed increase get your products down to 2 variations or less. I remember when I initially had three and four variations per product looking at the sql. That sql analysis exercise is what prompted me to build the options plugin.

[image: sparkle gear web site]http://www.sparkle-gear.com/wp-content/themes/bling_2_0_3/images/logo.png

Jeffrey Schutzman sparkle-gear.com / http://www.sparkle-gear.com Pye Brook Company, Inc. jeff@sparkle-gear.com / jeff@pyebrook.com (978) 561-9663: <1-978-807-5309> voice/mobile/sms My profiles: [image: LinkedIn]http://www.linkedin.com/pub/jeffrey-schutzman/0/a/145 [image: Google Plus] http://plus.google.com/118269319889151761287/ [image: Twitter] http://twitter.com/jeff5309 Contact me: [image: Skype] jeff-schutzman IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.

On Fri, May 10, 2013 at 9:28 AM, Ben Huson notifications@github.com wrote:

For your particular need of colour/size variations that UI works well.

I work on a few sites that have 3 variation groups per product in which case that grid UI wouldn't work :(

My thoughts behind at least caching the minimum variation price and maximum variation price on the parent product was then it would make a huge saving of queries when viewing categories as do would not need to query any of the variations at all (unless showing the variation drop-down on category pages) and also it would mean you could do more efficient price querying - for example sort by price, viewing product by price range etc.

I know it could be seen as duplicating data, but I think that structurally using child products for variations is correct - each should be it's own line item and have the potential to be completely customisable. So I'm just thinking that caching the variation information that is commonly used like min/max price would speed things up.

— Reply to this email directly or view it on GitHubhttps://github.com/wp-e-commerce/WP-e-Commerce/issues/100#issuecomment-17719969 .

benhuson commented 11 years ago

In most case the store in question only uses 1 or 2 variations but there are a few that need 3 for some complex products where all 3 affect price :(

We don't show any variation info (other than price) at a category level which is where the main performance hit is. On single product pages it's not so bad - well manageable.