Performance - Loading attribute options from DB #36174
Description
Summary (*)
When using $product->getAttributeText('some_attribute_using_table_source'), this is executed :
public function getAttributeText($attributeCode)
{
return $this->getResource()->getAttribute($attributeCode)->getSource()->getOptionText(
$this->getData($attributeCode)
);
}
And getOptionText get its data from database.
The issue is that if we call getAttributeText() several times, a call to database will occur every time. I know query cache will help, but this is still DB calls, so DB load (with networks time for each request, etc..) which is not negligible with high loads.
Examples (*)
$productFactory = \Magento\Framework\App\ObjectManager::getInstance()->get(\Magento\Catalog\Model\ProductFactory::class);
$product = $productFactory->create()->load(21673);
var_dump($product->getData('color'));
$time = microtime(true);
$product->getAttributeText('color');
echo (microtime(true)-$time) . "<br>";
$time = microtime(true);
$product->getAttributeText('color');
echo (microtime(true)-$time) . "<br>";
$time = microtime(true);
$product->getAttributeText('color');
echo (microtime(true)-$time) . "<br>";
$time = microtime(true);
$product->getAttributeText('color');
echo (microtime(true)-$time) . "<br>";
$time = microtime(true);
$product->getAttributeText('color');
echo (microtime(true)-$time) . "<br>";
$time = microtime(true);
$product->getAttributeText('color');
echo (microtime(true)-$time) . "<br>";
$time = microtime(true);
$product->getAttributeText('color');
echo (microtime(true)-$time) . "<br>";
results as
0.0025439262390137
0.00037407875061035
0.00033903121948242
0.0003349781036377
0.00032901763916016
0.00039505958557129
0.00045204162597656
So 2ms for the first request, and then 0.3-0.4ms thanks to query cache. And that's on my local environment, without any network and load involved.
Let's say you're displaying 4 attributes on a product list page, with 50 products per page, that's (assuming each product has different attribute values, so without any query cache) : 50*4*2ms = 400ms and 200 DB calls.
Proposed solution
With a simple cache with class variables, we get this result :
0.0021991729736328
1.6927719116211E-5
6.9141387939453E-6
5.9604644775391E-6
7.1525573730469E-6
5.9604644775391E-6
5.0067901611328E-6
I'm not sure which way is the best for caching. Probably caching the whole table with both backend cache (like redis) and class-cache (class static variable) would be the best way to limit DB calls.
Something like this (pseudo-code just to show the idea) :
public function getOptionText($value) {
if(self::$cache[attributeCode][$value] ?? false) {
return self::$cache[attributeCode][$value];
}
$options = $this->cache->load(attributeCode-storeId-options);
if($options) {
self::$cache[attributeCode] = options;
return $options[value];
}
$options = loadOptions();
self::$cache[attributeCode] = $options;
$this->cache->save(attributeCode-storeId-options, $options);
return $options
}
Please provide Severity assessment for the Issue as Reporter. This information will help during Confirmation and Issue triage processes.
- Severity: S0 - Affects critical data or functionality and leaves users with no 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”.