0

Hello fellow Magento developers,

What is the most efficient way of getting an attribute value of a custom eav product attribute, by only using its attribute_code and the products sku?

=> The question mainly targets the most efficient way, but answers with maintainability in mind (i.e. readable, more update secure abstractions etc.) would be great!

Background:

The objective is to hook into the method of a PIM connector module via a plugin (interceptor), that is responsible for pushing several hundreds of products into the Magento 2 database. The plugins function itself is not relevant, however it has to run checks on every product by getting the products attribute value (product: by given sku; attribute: by given attribute_code).

The PIM connector module works like this:

  1. handling data for several products in batch (~100 products per batch)
  2. pushing the data from 1.) product entities as well as their attributes into the database (more or less directly: "simplified explanation").

The plugin hooks into the step 1.) and iterates over all 100 products of the batch and needs for every product as product sku i.e. 'STRING_SKU_HERE' the attribute value with attribute_code 'MY_ATTRIBUTE_CODE'.

Because the procedure may apply for thousands of products, it is important that the plugin and custom logic does not significally slow down the process.

Previous attempts:

Although it is not representative, we have counted the time for fetching the needed data by using several different options.

  1. try: using ProductCollection and raw SQL
/** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $collection */
$collection = $this->productCollectionFactory->create();

$collection
    ->getConnection()
    ->query(
        "SELECT e.sku, e.entity_id, e.attribute_set_id, sub.*
            FROM
                `catalog_product_entity` e
            JOIN
                (
                    SELECT row_id, value_id, value
                    FROM `catalog_product_entity_varchar` enityvarchar
                    WHERE enityvarchar.attribute_id = (
                        SELECT attribute_id
                        FROM `eav_attribute`
                        WHERE `attribute_code` = 'MY_ATTRIBUTE_CODE'
                    )
                ) sub
                ON (e.entity_id = sub.row_id)
                WHERE sub.value IS NOT NULL AND e.sku = 'STRING_SKU_HERE'"
        )
        ->fetch();

=> Time consumed: float(0.00023388862609863)

  • (+ +) time
  • (- -) maintainability
  1. try : pure ProductCollection
/** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $collection */
$collection = $this->productCollectionFactory->create();

$collection
    ->addAttributeToSelect('STRING_SKU_HERE')
    ->addAttributeToFilter('sku', ['STRING_SKU_HERE'])
    ->getFirstItem()
    ->getData('MY_ATTRIBUTE_CODE');

=> Time consumed: float(0.0028450489044189)

  • (+) time (~10 times slower than 1.) )
  • (+) maintainability
  • (-) unnecessary product instantiation
  1. try: via ProductResource
/** @var \Magento\Catalog\Model\Product $product */
$product = $this->productFactory->create();

/** @var \Magento\Catalog\Model\ResourceModel\Product $productResource */
$productResource = $product->getResource(); 

$productId = $productResource->getIdBySku('STRING_SKU_HERE');
$productResource->load($product, $productId, ['MY_ATTRIBUTE_CODE']);
$product->getData('MY_ATTRIBUTE_CODE');

=> Time consumed: float(0.0058329105377197)

  • (+) maintainability
  • (-) unnecessary product instantiation
  • (-) time (not very fast, but also not very slow)
  1. try: via ProductRepository
/** @var \Magento\Catalog\Model\ProductRepository $productRepository */
$productRepository = $this->productRepository;

$product = $productRepository->get('STRING_SKU_HERE');
$product->getData('MY_ATTRIBUTE_CODE');

=> Time consumed: float(0.44260311126709)

  • (++) maintainability
  • (-) unnecessary product instantiation
  • (--) time (~ very very slow compared to 1.), 2.) 3.) )

Thanks in advance!

EDIT: Current test 2022-07-11 including variants of comments below (results vary of course (!) , they are not representative but indicators of the time consumption on my machine):

  • Variant 1 (original question): float(0.00022697448730469)
  • Variant 2 (original question): float(0.0026819705963135)
  • Variant 3 (original question): float(0.040131092071533)
  • Variant 4 (original question): float(0.027424097061157)
  • Variant 5 (1. variant by @victor-tihonchuk): float(0.00026488304138184)
  • Variant 6 (2. variant by @victor-tihonchuk): float(0.0004880428314209)

I'm curious about more opportunities. :)

1
  • In this case (edit 2022-07-11) variant 1 faster than Variant 5 only because the system doesn't spend time to work with Select instance and you don't have anything like quote or assemble query. MySQL query time in variant 1 slower than variant 5. I made some cyclic test and can confirm that PLAIN hardcoded select like in variant 1 faster than dynamic one. Jul 12, 2022 at 0:38

1 Answer 1

1

I think best one and correct is using following logic:

// better to initialize all related instances in __constructor
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$eavConfig     = $objectManager->get(\Magento\Eav\Model\Config::class);
$resource      = $objectManager->get(\Magento\Catalog\Model\ResourceModel\Product::class);
$metadata      = $objectManager->get(\Magento\Framework\EntityManager\MetadataPool::class)
    ->getMetadata(\Magento\Catalog\Api\Data\ProductInterface::class);
$linkField     = $metadata->getLinkField();
$attribute     = $eavConfig->getAttribute(\Magento\Catalog\Model\Product::ENTITY, '{ATTRIBUTE_CODE}');

$select = $resource->getConnection()->select()
    ->from(['e' => $resource->getEntityTable()], null)
    ->join(
        ['ev' => $attribute->getBackendTable()],
        sprintf(
            'e.%1$s = ev.row_id AND ev.store_id = 0 AND ev.attribute_id = %2$s',
            $linkField,
            $attribute->getAttributeId()
        ),
        'value'
    )
    ->where('e.sku = ?', '{SKU}');
$value = $resource->getConnection()->fetchOne($select);

// you can select and fetch more data if needed also check if attribute can have store value, etc
// of course this applicable only for non static attributes and you need to 
// check if your attribute is not static and implement static logic if needed

MySQL EXPLAIN: enter image description here

Compare with your first point: enter image description here

Also you have a mistake with mapping entity value row_id

Instead of variant 3 you can compare with:

$value = $resource->getAttributeRawValue($resource->getIdBySku('{SKU}'), 'ATTRIBUTE_CODE', 0);

[UPDATE]

If you are care about performance, i've little bit improved 1st one solution and decorated with Resource Model

app/code/Acme/StackExchange/Model/ResourceModel/Catalog/Product/Attribute.php

<?php
declare(strict_types=1);

namespace Acme\StackExchange\Model\ResourceModel\Catalog\Product;

use Magento\Catalog\Api\Data\ProductInterface;
use Magento\Catalog\Model\Product;
use Magento\Catalog\Model\ResourceModel\Eav\Attribute as CatalogAttribute;
use Magento\Eav\Model\Config as EavConfig;
use Magento\Framework\EntityManager\EntityMetadataInterface;
use Magento\Framework\EntityManager\MetadataPool;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
use Magento\Framework\Model\ResourceModel\Db\Context;

class Attribute extends AbstractDb
{
    protected EavConfig               $eavConfig;
    protected EntityMetadataInterface $metadata;

    /**
     * @inheritDoc
     */
    public function __construct(
        Context $context,
        EavConfig $eavConfig,
        MetadataPool $metadataPool,
        $connectionName = null
    ) {
        $this->eavConfig = $eavConfig;
        $this->metadata  = $metadataPool->getMetadata(ProductInterface::class);

        parent::__construct($context, $connectionName);
    }

    /**
     * @inheritDoc
     */
    protected function _construct()
    {
        $this->_init('catalog_product_entity', 'entity_id');
    }

    public function getValueBySku(string $sku, string $attributeCode, int $storeId = null): ?string
    {
        /** @var CatalogAttribute $attribute */
        $attribute  = $this->eavConfig->getAttribute(Product::ENTITY, $attributeCode);
        $connection = $this->getConnection();

        $select = $connection->select()
            ->from(['e' => $this->getMainTable()], null)
            ->where('e.sku = :sku');
        $bind = [
            ':sku' => $sku,
        ];

        if ($attribute->isStatic()) {
            $select->columns($attribute->getAttributeCode(), 'e');
        } else {
            $linkField = $this->metadata->getLinkField();
            $select->join(
                ['vd' => $attribute->getBackendTable()],
                sprintf('e.%s = vd.row_id AND vd.store_id = 0 AND vd.attribute_id = :attribute_id', $linkField),
                []
            );

            $bind[':attribute_id'] = (int)$attribute->getAttributeId();

            if ($attribute->isScopeGlobal() || $storeId === null || $storeId === 0) {
                $select->columns('value', 'vd');
            } else {
                $select->joinLeft(
                    ['vs' => $attribute->getBackendTable()],
                    sprintf('e.%s = vs.row_id AND vs.store_id = :store_id AND vs.attribute_id = :attribute_id', $linkField),
                    [
                        'value' => $connection->getCheckSql('vs.value_id IS NULL', 'vd.value', 'vs.value'),
                    ]
                );
                $bind[':store_id'] = $storeId;
            }
        }

        return $connection->fetchOne($select, $bind);
    }

    public function getValueBySkuRaw(string $sku, string $attributeCode, int $storeId = null): ?string
    {
        /** @var CatalogAttribute $attribute */
        $attribute  = $this->eavConfig->getAttribute(Product::ENTITY, $attributeCode);
        $connection = $this->getConnection();

        $select  = sprintf('SELECT %%columns%% FROM %s AS e', $this->getMainTable());
        $columns = [];
        $bind = [
            ':sku' => $sku,
        ];

        if ($attribute->isStatic()) {
            $columns[] = 'e.' . $attribute->getAttributeCode();
        } else {
            $linkField = $this->metadata->getLinkField();
            $select .= sprintf(
                ' INNER JOIN %s AS vd ON e.%s = vd.row_id AND vd.store_id = 0 AND vd.attribute_id = :attribute_id',
                $attribute->getBackendTable(),
                $linkField
            );

            $bind[':attribute_id'] = (int)$attribute->getAttributeId();

            if ($attribute->isScopeGlobal() || $storeId === null || $storeId === 0) {
                $columns[] = 'vd.value';
            } else {
                $select .= sprintf(
                    ' LEFT JOIN %s AS vs ON e.%s = vs.row_id AND vs.store_id = :store_id AND vs.attribute_id = :attribute_id',
                    $attribute->getBackendTable(),
                    $linkField
                );
                $columns[] = $connection->getCheckSql('vs.value_id IS NULL', 'vd.value', 'vs.value');

                $bind[':store_id'] = $storeId;
            }
        }
        $select .= ' WHERE e.sku = :sku';
        $select = strtr($select, ['%columns%' => implode(', ', $columns)]);

        return $connection->fetchOne($select, $bind);
    }
}

Using recommended way:

$attributeResource->getValueBySku('{SKU}', '{DYNAMIC_ATTRIBUTE}');
$attributeResource->getValueBySku('{SKU}', '{DYNAMIC_ATTRIBUTE}', {STORE_ID});
$attributeResource->getValueBySku('{SKU}', '{STATIC_ATTRIBUTE}');

Using performance optimized way:

$attributeResource->getValueBySku('{SKU}', '{DYNAMIC_ATTRIBUTE}');

Speed Tests (100 random SKUs)

Test Result Variant #1 (from question)
Min: 0.00024199485778809
Max: 0.001270055770874
Avg: 0.00029866647720337

Test Result Variant #2 (getValueBySku)
Min: 0.00034093856811523
Max: 0.00078010559082031
Avg: 0.00038370656967163

Test Result Variant #3 (getValueBySkuRaw)
Min: 0.00019192695617676
Max: 0.00078105926513672
Avg: 0.00026803112030029

Test Result Variant #4 (plain SQL from 2/3 without placeholders and quote(sku))
Min: 0.00018095970153809
Max: 0.00065398216247559
Avg: 0.00023439168930054
2
  • Thank you very much for your suggestions @victor-tihonchuk. Comparing it to the other methods, it seems like your first mentioned method is about as fast as my variant 1, while using abstractions (which is of course better). I really like your second variant and haven figured that one out before ( O_o). Right now it looks like its a very nice solution for most "generic" use cases. In my specific use case the first suggestion seems suitable for now.
    – Emanuel
    Jul 11, 2022 at 17:55
  • 1
    See [UPDATE] section in answer. This is maintainable and fast. It should be faster in cycle with hundred products Jul 11, 2022 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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