Summary

When working with product collections I found out that getting attribute values is very slow because of using method \Magento\Eav\Model\Entity\Attribute\Source\Table::getSpecificOptions. This method is not optimized to work with product collections because it loads data from Database every time attribute value is requested.
Taking in mind that \Magento\Eav\Model\Entity\Attribute\Source\Table is a default source model for any attribute which has no source_model defined in eav_attribute table it brings a siriouse performance degradation. For example, when working with attribute of text frontend_input type it uses getSpecificOptions() method which returns empty response every time.

To get attribute value there 2 possible loading stacks.

Stack 1. Performance degradation is detected for only attributes of select, multiselect frontend_input type

1. \Magento\Eav\Model\Entity\Attribute\Frontend\AbstractFrontend::getValue
2. \Magento\Eav\Model\Entity\Attribute\Frontend\AbstractFrontend::getOption
3. \Magento\Eav\Model\Entity\Attribute\Source\Table::getOptionText
4. \Magento\Eav\Model\Entity\Attribute\Source\Table::getSpecificOptions

Stack 2. Performance degradation is detected for all attribute regardless of frontend_input type

1. \Magento\Catalog\Model\Product::getAttributeText
2. \Magento\Eav\Model\Entity\Attribute\Source\Table::getOptionText
3. \Magento\Eav\Model\Entity\Attribute\Source\Table::getSpecificOptions

Examples

See Summary section with explanations

Proposed solution

Use \Magento\Eav\Model\Entity\Attribute\Source\Table::getAllOptions call in getSpecificOptions method, so it will minimize the number of SQL queries to 1 per attribute per product collection because option values in getAllOptions method are cached

public function getSpecificOptions($ids, $withEmpty = true) {
    $allOptions = $this->getAllOptions(false);
    $specificOptions = [];

    if (is_string($ids) && strpos($ids, ',') !== false) {
        $ids = explode(',', $ids);
    }

    if (!is_array($ids)) {
        $ids = (array)$ids;
    }

    if (count($allOptions) > 0) {
        foreach ($allOptions as $option) {
            if (isset($option['value']) && in_array($option['value'], $ids)) {
                $specificOptions[] = $option;
            }
        }
    }

    if ($withEmpty) {
        $specificOptions = $this->addEmptyOption($specificOptions);
    }

    return $specificOptions;
}

Release note

No response

Triage and priority

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.