Performance on the product page with attributes

Question

I'm working on a Magento webshop (EE 1.14.2.0) with terrible performance on pages where product attributes are loaded, the worst performing page is the product page because there're multiple attributes shown. It takes 12854ms and 250MB memory to load!

I've installed the AOE Profiler extension for better profiling where I saw the following:

When I disable all the local and community modules and restore the default or rwd theme I've got the same performance (there are no core hacks).

The shown queries are all the same and the complete version of it:

SELECT 
    `main_table`.*, `tdv`.`value` AS `default_value`, `tsv`.`value` AS `store_default_value`, IF(tsv.value_id > 0, tsv.value, tdv.value) AS `value` 
FROM 
    `eav_attribute_option` AS `main_table`
INNER JOIN 
    `eav_attribute_option_value` AS `tdv` 
ON 
    tdv.option_id = main_table.option_id
LEFT JOIN 
    `eav_attribute_option_value` AS `tsv` 
ON 
    tsv.option_id = main_table.option_id AND tsv.store_id = '1' 
WHERE 
    (tdv.store_id = 0) 
ORDER BY 
    main_table.sort_order ASC, value ASC

When I run this query I get 11000 records! All attribute option values. The webshop does have a lot of attribute option values, but why get all these instead of getting the ones needed? For example the attributes.phtml file, this one renders on this product page just 6 attributes. As you can see, the query runs 6 times! It looks like it's getting everything (the collection, correct me if I'm wrong) and filters afterwards.

Is this a bug in Magento or something I can fix easily?

Answer 1

We've contacted Magento Enterprise support and they've created a patch for this: SUPEE-6913. I can't reference to anything because I've just received the patch by email.