Thousands of SQL queries to generate Product pages for products with 100+ variations

[TamaEaston]

We've been having major performance issues with a Silverstripe project. As products have been loaded with multiple variations performance time has dropped substantially.

The following is for the Product page.

Using ?showqueries=1 on a product with 10 x 5 x 5 = 250 variations returned over 5,000 SQL queries.

Reducing the product to 10 x 5 = 50 variations reduced the query number to around 750.

Increasing the product to 10 x 5 x 5 x 5 = 1250 variations increased the query number to over 30,000.

Here is the output for 10 x 5 x 5: https://drive.google.com/file/d/0B_doMj_pm-5zMGFDLUNVYlc2M2c/edit?usp=sharing

[frankmullenger]

Ouch. Thanks for reporting this, we'll have to look into doing variations a bit smarter.

[frankmullenger]

@TamaEaston started having a look at this during the hack fest, there are a few areas where we can definitely improve the number of queries. In the mean time you might be able to look into static caching to circumvent this problem?