{"data":{"id":"magento2-40463","source":"github-pr","sourceRef":"magento/magento2#40463","title":"Performance fix the analytics_collect_data cron job long run","pr":{"number":40463,"url":"https://github.com/magento/magento2/pull/40463","author":"AlexRapatij","mergedAt":"2026-04-01T18:42:59Z","mergeCommit":"0d7cd7e5e8efc9bbad02a0b5cf5a067dc6dfa360","headCommit":"cc8375f1b63faff46e2921d2baa5fb31637ea115","baseRef":"2.4-develop","diffSha256":"cc40d7d06e00b2367407fe702961a4492d44f42f455e7d0cfaf6118299a05619"},"issues":[{"number":40590,"url":"https://github.com/magento/magento2/issues/40590","title":"[Issue] Performance fix for the analytics_collect_data cron job long run","labels":["Area: Analytics / Reporting","Component: Analytics","Issue: Confirmed","Priority: P2","Progress: PR Created","Progress: done","Reproduced on 2.4.x"],"kind":"pr-derived"}],"fixedIn":"2.4.9","containingTags":["2.4.9"],"reportedOn":null,"codeMatch":{"2.4.6":"clean","2.4.6-p1":"clean","2.4.6-p2":"clean","2.4.6-p3":"clean","2.4.6-p4":"clean","2.4.6-p5":"clean","2.4.6-p6":"clean","2.4.6-p7":"clean","2.4.6-p8":"clean","2.4.6-p9":"clean","2.4.6-p10":"clean","2.4.6-p11":"clean","2.4.6-p12":"clean","2.4.6-p13":"clean","2.4.6-p14":"clean","2.4.6-p15":"clean","2.4.7":"clean","2.4.7-p1":"clean","2.4.7-p2":"clean","2.4.7-p3":"clean","2.4.7-p4":"clean","2.4.7-p5":"clean","2.4.7-p6":"clean","2.4.7-p7":"clean","2.4.7-p8":"clean","2.4.7-p9":"clean","2.4.7-p10":"clean","2.4.8":"clean","2.4.8-p1":"clean","2.4.8-p2":"clean","2.4.8-p3":"clean","2.4.8-p4":"clean","2.4.8-p5":"clean","2.4.9":"conflict"},"affectedVersions":["2.4.6","2.4.6-p1","2.4.6-p2","2.4.6-p3","2.4.6-p4","2.4.6-p5","2.4.6-p6","2.4.6-p7","2.4.6-p8","2.4.6-p9","2.4.6-p10","2.4.6-p11","2.4.6-p12","2.4.6-p13","2.4.6-p14","2.4.6-p15","2.4.7","2.4.7-p1","2.4.7-p2","2.4.7-p3","2.4.7-p4","2.4.7-p5","2.4.7-p6","2.4.7-p7","2.4.7-p8","2.4.7-p9","2.4.7-p10","2.4.8","2.4.8-p1","2.4.8-p2","2.4.8-p3","2.4.8-p4","2.4.8-p5"],"components":["magento/module-analytics"],"files":[{"path":"app/code/Magento/Analytics/ReportXml/DB/ReportValidator.php","change":"modified","package":"magento/module-analytics"}],"stripped":{"tests":["app/code/Magento/Analytics/Test/Unit/ReportXml/DB/ReportValidatorTest.php"],"docs":[],"outsideCode":[]},"linesChanged":6,"mergeBatched":false,"excluded":null,"sections":{"description":"For a fairly large database, the `analytics_collect_data` cron job takes too long to execute. For one real example project, it took last time ~36 hours.\nAccording to the investigation, the reason for that is the validation of the report definition query. By adding a limit of 0, it turns into a final SQL-query without any limits at all. As a result, there is a loading of all tables data.\nThe solution is super simple - to add a limit equal to 1 to the validation query","stepsToReproduce":"1. Add a temporary custom logging before calling `query` (line 55). Unfortunately, the `bin/magento dev:query-log:enable` doesn't log such queries.\n```php\n// Example of logging\ntry {\n    \\Magento\\Framework\\App\\ObjectManager::getInstance()->get(\\Psr\\Log\\LoggerInterface::class)\n        ->info('Report validation query: ' . $query->getSelect()->__toString());\n    $connection->query($query->getSelect());\n} catch (\\Zend_Db_Statement_Exception $e) {\n    return [$name, $e->getMessage()];\n}\n```\n2. Execute the `analytics_collect_data` cron job\n```shell\nphp n98-magerun2.phar sys:cron:run analytics_collect_data\n```\n3. Check the logs\n```shell\ngrep 'Report validation query' var/log/system.log\n```\n\n### Examples\n#### Before change\nThere is queries, before the change was applied\n```sql\nSELECT `catalog_category_entity_text`.`value` AS `content` FROM `catalog_category_entity_text`\nSELECT `catalog_product_entity_text`.`value` AS `content`, `eav_attribute`.`attribute_code` FROM `catalog_product_entity_text`\nSELECT `cms_page`.`content` FROM `cms_page`\nSELECT `cms_block`.`content` FROM `cms_block`\nSELECT `setup_module`.`module` AS `module_name`, `setup_module`.`schema_version`, `setup_module`.`data_version` FROM `setup_module` \nSELECT `store`.`store_id`, `store`.`code`, `store`.`group_id`, `store`.`name`, `store`.`is_active` FROM `store` \nSELECT `store_website`.`website_id`, `store_website`.`code`, `store_website`.`name`, `store_website`.`default_group_id`, `store_website`.`is_default` FROM `store_website` \nSELECT `store_group`.`group_id`, `store_group`.`website_id`, `store_group`.`name`, `store_group`.`default_store_id` FROM `store_group` \nSELECT `catalog_product_entity`.`entity_id`, `catalog_product_entity`.`sku` FROM `catalog_product_entity` WHERE (catalog_product_entity.created_in <= '1748965680') AND (catalog_product_entity.updated_in > '1748965680') \nSELECT `magento_banner_content`.`banner_content` AS `content` FROM `magento_banner_content`\nSELECT `quote`.`entity_id`, `quote`.`customer_id`, `quote`.`store_id`, `quote`.`created_at`, `quote`.`converted_at`, `quote`.`is_active`, `quote`.`items_count`, `quote`.`items_qty`, `quote`.`orig_order_id` FROM `quote` \nSELECT `review`.`review_id`, `review`.`created_at`, `review`.`entity_pk_value` FROM `review` \nSELECT `rating_option_vote_aggregated`.`primary_id`, `rating_option_vote_aggregated`.`entity_pk_value`, `rating_option_vote_aggregated`.`store_id`, `rating_option_vote_aggregated`.`rating_id`, `rating_option_vote_aggregated`.`percent_approved` FROM `rating_option_vote_aggregated` \nSELECT `sales_order`.`entity_id`, `sales_order`.`created_at`, `sales_order`.`customer_id`, `sales_order`.`status`, `sales_order`.`base_grand_total`, `sales_order`.`base_tax_amount`, `sales_order`.`base_shipping_amount`, SHA1(`sales_order`.`coupon_code`) AS `coupon_code`, `sales_order`.`store_id`, `sales_order`.`store_name`, `sales_order`.`base_discount_amount`, `sales_order`.`base_subtotal`, `sales_order`.`base_total_refunded`, `sales_order`.`shipping_method`, `sales_order`.`shipping_address_id`, SHA1(`sales_order`.`customer_email`) AS `customer_email`, `sales_order`.`base_total_online_refunded`, `sales_order`.`base_total_offline_refunded`, `sales_order`.`base_currency_code`, `sales_order`.`billing_address_id` FROM `sales_order` \nSELECT `sales_order_item`.`item_id`, `sales_order_item`.`created_at`, `sales_order_item`.`name`, `sales_order_item`.`base_price`, `sales_order_item`.`qty_ordered`, `sales_order_item`.`order_id`, `sales_order_item`.`sku`, `sales_order_item`.`product_id`, `sales_order_item`.`store_id` FROM `sales_order_item` \nSELECT `sales_order_address`.`entity_id`, `sales_order_address`.`customer_id`, `sales_order_address`.`city`, `sales_order_address`.`region`, `sales_order_address`.`country_id` FROM `sales_order_address` \nSELECT `customer_entity`.`entity_id`, `customer_entity`.`created_at`, SHA1(`customer_entity`.`email`) AS `email`, `customer_entity`.`store_id` FROM `customer_entity` \nSELECT `wishlist`.`wishlist_id`, `wishlist`.`customer_id` FROM `wishlist` \nSELECT `wishlist_item`.`wishlist_item_id`, `wishlist_item`.`added_at`, `wishlist_item`.`qty`, `wishlist_item`.`store_id`, `wishlist_item`.`wishlist_id`, `wishlist_item`.`product_id` FROM `wishlist_item` \n```\n\n#### After\nAfter the limitation was added\n```sql\nSELECT `catalog_category_entity_text`.`value` AS `content` FROM `catalog_category_entity_text`\nSELECT `catalog_product_entity_text`.`value` AS `content`, `eav_attribute`.`attribute_code` FROM `catalog_product_entity_text`\nSELECT `cms_page`.`content` FROM `cms_page`\nSELECT `cms_block`.`content` FROM `cms_block`\nSELECT `setup_module`.`module` AS `module_name`, `setup_module`.`schema_version`, `setup_module`.`data_version` FROM `setup_module` LIMIT 1 \nSELECT `store`.`store_id`, `store`.`code`, `store`.`group_id`, `store`.`name`, `store`.`is_active` FROM `store` LIMIT 1 \nSELECT `store_website`.`website_id`, `store_website`.`code`, `store_website`.`name`, `store_website`.`default_group_id`, `store_website`.`is_default` FROM `store_website` LIMIT 1 \nSELECT `store_group`.`group_id`, `store_group`.`website_id`, `store_group`.`name`, `store_group`.`default_store_id` FROM `store_group` LIMIT 1 \nSELECT `catalog_product_entity`.`entity_id`, `catalog_product_entity`.`sku` FROM `catalog_product_entity` WHERE (catalog_product_entity.created_in <= '1748965680') AND (catalog_product_entity.updated_in > '1748965680') LIMIT 1 \nSELECT `magento_banner_content`.`banner_content` AS `content` FROM `magento_banner_content`\nSELECT `quote`.`entity_id`, `quote`.`customer_id`, `quote`.`store_id`, `quote`.`created_at`, `quote`.`converted_at`, `quote`.`is_active`, `quote`.`items_count`, `quote`.`items_qty`, `quote`.`orig_order_id` FROM `quote` LIMIT 1 \nSELECT `review`.`review_id`, `review`.`created_at`, `review`.`entity_pk_value` FROM `review` LIMIT 1 \nSELECT `rating_option_vote_aggregated`.`primary_id`, `rating_option_vote_aggregated`.`entity_pk_value`, `rating_option_vote_aggregated`.`store_id`, `rating_option_vote_aggregated`.`rating_id`, `rating_option_vote_aggregated`.`percent_approved` FROM `rating_option_vote_aggregated` LIMIT 1 \nSELECT `sales_order`.`entity_id`, `sales_order`.`created_at`, `sales_order`.`customer_id`, `sales_order`.`status`, `sales_order`.`base_grand_total`, `sales_order`.`base_tax_amount`, `sales_order`.`base_shipping_amount`, SHA1(`sales_order`.`coupon_code`) AS `coupon_code`, `sales_order`.`store_id`, `sales_order`.`store_name`, `sales_order`.`base_discount_amount`, `sales_order`.`base_subtotal`, `sales_order`.`base_total_refunded`, `sales_order`.`shipping_method`, `sales_order`.`shipping_address_id`, SHA1(`sales_order`.`customer_email`) AS `customer_email`, `sales_order`.`base_total_online_refunded`, `sales_order`.`base_total_offline_refunded`, `sales_order`.`base_currency_code`, `sales_order`.`billing_address_id` FROM `sales_order` LIMIT 1 \nSELECT `sales_order_item`.`item_id`, `sales_order_item`.`created_at`, `sales_order_item`.`name`, `sales_order_item`.`base_price`, `sales_order_item`.`qty_ordered`, `sales_order_item`.`order_id`, `sales_order_item`.`sku`, `sales_order_item`.`product_id`, `sales_order_item`.`store_id` FROM `sales_order_item` LIMIT 1 \nSELECT `sales_order_address`.`entity_id`, `sales_order_address`.`customer_id`, `sales_order_address`.`city`, `sales_order_address`.`region`, `sales_order_address`.`country_id` FROM `sales_order_address` LIMIT 1 \nSELECT `customer_entity`.`entity_id`, `customer_entity`.`created_at`, SHA1(`customer_entity`.`email`) AS `email`, `customer_entity`.`store_id` FROM `customer_entity` LIMIT 1 \nSELECT `wishlist`.`wishlist_id`, `wishlist`.`customer_id` FROM `wishlist` LIMIT 1 \nSELECT `wishlist_item`.`wishlist_item_id`, `wishlist_item`.`added_at`, `wishlist_item`.`qty`, `wishlist_item`.`store_id`, `wishlist_item`.`wishlist_id`, `wishlist_item`.`product_id` FROM `wishlist_item` LIMIT 1 \n```","expectedResult":null,"actualResult":null,"source":"pr"},"signatures":["} catch (\\Zend_Db_Statement_Exception $e) {"],"labels":{"area":["Analytics / Reporting"],"component":["Analytics"],"priority":"P2","severity":null,"reportedOn":[]},"categories":["Reports"],"triage":{"model":"@cf/cloudflare/clef","requestHash":"c678fb8fa00d12692ecf5b36e42146fd572c08fc0a5a5cd68c7b0a9773752897","isBugfix":0.8175,"changeKind":{"choice":"bugfix","probabilities":{"bugfix":0.9069,"feature":0.0136,"refactor":0.0524,"tests_only":0.0144,"docs_only":0.0058,"dependency":0.0069},"confidence":0.7908},"scope":{"score":0.3969,"probabilities":{"0":0.6496,"1":0.3038,"2":0.0466},"confidence":0.2747},"risk":{"score":0.1924,"probabilities":{"0":0.8583,"1":0.091,"2":0.0507},"confidence":0.6213},"area":{"choice":"other","probabilities":{"catalog":0.0451,"checkout":0.0315,"customer":0.0204,"admin":0.1025,"graphql_api":0.0152,"framework":0.3305,"frontend":0.0214,"other":0.4334},"confidence":0.2134},"securityRelevant":0.0068,"reportedVersion":{"choice":"unspecified","probabilities":{"2.4.0":0.0033,"2.4.0-p1":0.003,"2.4.1":0.0029,"2.4.1-p1":0.0028,"2.4.2":0.0035,"2.4.2-p1":0.0038,"2.4.2-p2":0.0053,"2.4.3":0.0049,"2.4.3-p1":0.005,"2.4.3-p2":0.0073,"2.4.3-p3":0.0044,"2.4.4":0.0077,"2.4.4-p1":0.0048,"2.4.4-p10":0.0045,"2.4.4-p11":0.0049,"2.4.4-p12":0.0047,"2.4.4-p13":0.0047,"2.4.4-p14":0.0058,"2.4.4-p15":0.0039,"2.4.4-p16":0.006,"2.4.4-p17":0.0044,"2.4.4-p18":0.0035,"2.4.4-p2":0.0023,"2.4.4-p3":0.0023,"2.4.4-p4":0.0025,"2.4.4-p5":0.0028,"2.4.4-p6":0.0036,"2.4.4-p7":0.0033,"2.4.4-p8":0.0032,"2.4.4-p9":0.0029,"2.4.5":0.0095,"2.4.5-p1":0.007,"2.4.5-p10":0.0053,"2.4.5-p11":0.0112,"2.4.5-p12":0.0077,"2.4.5-p13":0.0066,"2.4.5-p14":0.0074,"2.4.5-p15":0.0054,"2.4.5-p16":0.0076,"2.4.5-p17":0.006,"2.4.5-p2":0.0028,"2.4.5-p3":0.0043,"2.4.5-p4":0.0044,"2.4.5-p5":0.004,"2.4.5-p6":0.0051,"2.4.5-p7":0.0046,"2.4.5-p8":0.0045,"2.4.5-p9":0.0036,"2.4.6":0.0227,"2.4.6-p1":0.016,"2.4.6-p10":0.008,"2.4.6-p11":0.0128,"2.4.6-p12":0.0109,"2.4.6-p13":0.0097,"2.4.6-p14":0.0104,"2.4.6-p15":0.0071,"2.4.6-p2":0.004,"2.4.6-p3":0.0062,"2.4.6-p4":0.0055,"2.4.6-p5":0.0056,"2.4.6-p6":0.0078,"2.4.6-p7":0.008,"2.4.6-p8":0.0055,"2.4.6-p9":0.0048,"2.4.7":0.0248,"2.4.7-p1":0.0158,"2.4.7-p10":0.0102,"2.4.7-p2":0.0061,"2.4.7-p3":0.0134,"2.4.7-p4":0.0152,"2.4.7-p5":0.0125,"2.4.7-p6":0.0136,"2.4.7-p7":0.0133,"2.4.7-p8":0.01,"2.4.7-p9":0.0061,"2.4.8":0.0482,"2.4.8-p1":0.0385,"2.4.8-p2":0.0259,"2.4.8-p3":0.0248,"2.4.8-p4":0.0196,"2.4.8-p5":0.0148,"2.4.9":0.0354,"unspecified":0.2758},"confidence":0.0769},"backportWorthy":{"score":1.6642,"probabilities":{"0":0.0693,"1":0.1972,"2":0.7335},"confidence":0.3726}},"curated":null,"tests":{"2.4.8-p5":{"before":"not-runnable","after":"error","adapted":false,"runAt":"2026-10-06T09:20:20.087Z","releaseCommit":"870a22c63d9d9b68fa3297962e2d5d5841115814","suites":{"unit":{"before":"not-runnable","after":"error","runAt":"2026-10-06T09:20:20.087Z"}}},"2.4.7-p10":{"before":"not-runnable","after":"error","adapted":false,"runAt":"2026-10-06T09:29:28.233Z","releaseCommit":"72561bf80652f57cc642a03e2c9a51d74a285b14","suites":{"unit":{"before":"not-runnable","after":"error","runAt":"2026-10-06T09:29:28.233Z"}}}}},"_documentation":"https://magento.watch/api","_description":"Upstream fix magento2-40463 details"}