import
import copied to clipboard
techdivision
A lot of repeated sql select queries are fired
probably in these steps:
general/general/global-data > general/catalog_product/collect-data
SHOW GLOBAL VARIABLES LIKE 'innodb%';
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (2);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (2);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (3);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (2);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (3);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (4);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (2);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (3);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (4);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (5);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (2);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (3);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (4);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (6);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (2);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (3);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (4);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (7);
SELECT t1.* FROM catalog_category_entity AS t0 INNER JOIN catalog_category_entity_varchar AS t1 ON t1.row_id = t0.row_id INNER JOIN eav_attribute AS t2 ON t2.entity_type_id = 3 AND t2.attribute_code = 'name' AND t1.attribute_id = t2.attribute_id AND t1.store_id = 0 AND t0.entity_id IN (2);
and for a 2-3 rows simple product import, there are easily 3000+ sqls queries making everything slower than it should have been.
PS: using redis as a cache doesnt seem to help either.
My guess is: it could be N+1 problem compounded. For each new category it sends query for older category.
For context: Magento = 2.3.3 EE entityTypeCode = catalog_product shortcut = add-update
Sorry @adhocore, I'm actually working on some other projects and didn't find the time to have a look at your issue. You're right, that there is a potential to improve performance significantly by compound the queries. We decided once to implement it in that way, well knowing that there will be a way to improve performance. So far, we get feedback from the community, that the community edition is fast enough for many use cases out there, whereas there is no need to be faster. For those cases, where the performance comes into focus, e. g. if 1.000.000 and more SKUs have to be processed, we provide a professional edition that supports batch SQL processing as well as extended caching functionality to reduce read and write DB access.
As this is a community project, any ideas, and PRs from the community, that'll improve performance are welcome and appreciated. Maybe someday we'll add functionality from the professional to the community edition. Actually, we have not decided if that will happen at all, and if yes, when and what functionality this will be.
Thanks for understanding.
i dont know because technically it is still an issue. i understand it could be big task to fix. can we maybe keep it in backlog for future revisit?
We can leave it open for sure. One more question. If you're you working for a company that provides Magento solutions, you may be interested in our solution partner program, which is free (see https://www.techdivision.com/technologien/pacemaker.html). This would give you access to our commercial solution where we've additional packages and add ons for M2IF. Do not hesitate to ask me, if you're interested :-)
i will think about it - in addition we also need to discuss in team as well :)