7

I'm working on a store that has 23k of possible color values. But, they are not used on every product of course.

Now, that we are working on 1.9.2.1, that has native swatches, I see that the produt page tooks 3 minutes to load.

It's because _loadOptionLabels method of Mage_ConfigurableSwatches_Model_Resource_Catalog_Product_Attribute_Super_Collection, that loads all of 23k colors each time a single product page is loaded.

Does anyone experienced the same problem, or have a guess of how to optimize it?

I also tried to clean all unused option values, but still take so many time to load and using Resource_Iterator to speed up foreach, but didn't work. The problem is the big query.

  • I can't answer 'why', but we ran into this as well on a site with some 2k color options. Catalog views were taking a minute or more on account of the color swatches. We ended up disabling the module entirely on account of its unoptimized state, and rolled a custom solution. – Ryan Hoerr Aug 7 '15 at 18:36
7

I've created a module to override \Mage_ConfigurableSwatches_Model_Resource_Catalog_Product_Attribute_Super_Collection::_loadOptionLabels with the following:

protected function _loadOptionLabels()
{
    if ($this->count()) {

        $attributeIds = array();
        $productId = false;
        $filterLabels = false;
        foreach ($this->_items as $item) {
            $attributeIds[] = $item->getAttributeId();
            $id = $item->getProductId();
            $productId[$id] = $id;
        }
        if (is_array($productId) && $attributeIds) {
            $filterLabels = true;
        }

        if ($filterLabels) {
            $productIntTable = 'catalog_product_entity_int';
            $select = $this->getConnection()->select()
                ->from(array('l' => $this->getTable('catalog/product_super_link')), array('i.value'))
                ->join(
                    array('i' => Mage::getSingleton('core/resource')->getTableName($productIntTable)),
                    'i.entity_id = l.product_id'
                )
                ->where('l.parent_id IN (?)', array_keys($productId))
                ->where('i.attribute_id IN (?)', $attributeIds);
            $optionIdsToSelect = $this->getConnection()->fetchCol($select);
        }


        $select = $this->getConnection()->select()
            ->from(
                array('attr' => $this->getTable('catalog/product_super_attribute')),
                array(
                    'product_super_attribute_id' => 'attr.product_super_attribute_id',
                )
            )
            ->join(
                array('opt' => $this->getTable('eav/attribute_option')),
                'opt.attribute_id = attr.attribute_id',
                array(
                    'attribute_id' => 'opt.attribute_id',
                    'option_id' => 'opt.option_id',
                )
            )
            ->join(
                array('lab' => $this->getTable('eav/attribute_option_value')),
                'lab.option_id = opt.option_id',
                array(
                    'label' => 'lab.value',
                    'store_id' => 'lab.store_id',
                )
            )
            ->where('attr.product_super_attribute_id IN (?)', array_keys($this->_items));

        if ($filterLabels) {
            $select->where('opt.option_id IN (?)', $optionIdsToSelect);
        }
        $result = $this->getConnection()->fetchAll($select);
        Mage::getSingleton('core/resource_iterator')->walk(
            $select,
            array(function($args){
                $data = $args['row'];
                $item = $this->getItemById($data['product_super_attribute_id']);
                if (!is_array($labels = $item->getOptionLabels())) {
                    $labels = array();
                }
                $labels[$data['option_id']][$data['store_id']] = $data['label'];
                $item->setOptionLabels($labels);
            })
        );

    }
    return $this;
}

I've filtered the query, and replaced foreach with iterator.

It nows brings me about 24 to 40 records per product, instead 23.000. :)

This module also makes unnecessary calls on product listing when you dont have setup to show anything in product list. Maybe a rewrite of Mage_ConfigurableSwatches_Model_Observer::productListCollectionLoadAfter is required to check if configswatches/general/product_list_attribute has some attribute to load.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.