FIX Variations Slow Loading or Not Exporting

[grantg]

I have spent a lot of time importing/exporting CSV and SQL files to copy variations from one site to another and vice versa. Over time, I found a lot of redundant data is generated.

This is the SQL I have often used to fix issues with the variations tab and subsequent pages loading slowly. Please back up the database sufficiently.

Run the first SQL step:

Code: Select all

DELETE FROM isc_product_variation_combinations WHERE vcvariationid IN (SELECT `variationid` FROM `isc_product_variations` WHERE `variationid` NOT IN (SELECT DISTINCT prodvariationid FROM isc_products))

Then the second:

Code: Select all

DELETE FROM isc_product_variation_options WHERE vovariationid IN (SELECT `variationid` FROM `isc_product_variations` WHERE `variationid` NOT IN (SELECT DISTINCT prodvariationid FROM isc_products))

And finally...

Code: Select all

DELETE FROM `isc_product_variations` WHERE `variationid` NOT IN (SELECT DISTINCT prodvariationid FROM isc_products)

These SQL statements must be run in order, do not try to run them all at the same time.

What this shall do is clear all unused variations and combinations, which I will explain briefly.

You initially had Product X which had variation options of A, B, or C.

Product Y also uses the same variation 'set'.

Product X is removed from your website, or you remove the variations as these are no longer required.

In the isc_product_variations_options and isc_product_variation_combinations tables, deleting Product X or unchecking 'This product will use variations', the data is not cleared from these tables and so becomes redundant. All the data of these tables is searched to show the appropriate variations for the product you are editing. Obviously the more redundant data there is, the slower this will load.

If this still does not help, I would advise you to contact your host as there may be limits on the database or php memory limits. A quick and easy way to test this is to go to Products > Export Products and use the Default export template and select all the available options (in particular, the variation options) to export. If the export gives a Memory Limit error and so does not create the CSV as expected, the server memory is probably the problem. Ultimately the real problem is poor software in this department from Interspire, but the variations (especially to Export) do require a hell of a lot of memory.

Hopefully this solves issues for some folks

[grantg]

Sorry, can't edit the OP any more (?), but thought it would be worth mentioning I have tested this and used this method from 5.0.6 to 5.5.1 through 5.5.4.

[Martin]

Thanks for sharing Grant... much appreciated...

Think the limitation on editing/deleting was down to problems I've seen in the past where people go off in a huff and delete tons of vital info'...

[grantg]

No problemo. This forum ain't too busy and generally the Search function works well, so thankfully Bumping threads with another post ain't too much of an issue yet. Hopefully, this activity means V6 is coming soon and will eventually erradicate a lot of issues, but also, unfortunatley, bring new problems to the table.