{"action":"created","issue":{"url":"https://api.github.com/repos/bigbridge-nl/product-import/issues/4","repository_url":"https://api.github.com/repos/bigbridge-nl/product-import","labels_url":"https://api.github.com/repos/bigbridge-nl/product-import/issues/4/labels{/name}","comments_url":"https://api.github.com/repos/bigbridge-nl/product-import/issues/4/comments","events_url":"https://api.github.com/repos/bigbridge-nl/product-import/issues/4/events","html_url":"https://github.com/bigbridge-nl/product-import/issues/4","id":376840410,"node_id":"MDU6SXNzdWUzNzY4NDA0MTA=","number":4,"title":"Two slow queries detected","user":{"login":"haelbichalex","id":15052352,"node_id":"MDQ6VXNlcjE1MDUyMzUy","avatar_url":"https://avatars1.githubusercontent.com/u/15052352?v=4","gravatar_id":"","url":"https://api.github.com/users/haelbichalex","html_url":"https://github.com/haelbichalex","followers_url":"https://api.github.com/users/haelbichalex/followers","following_url":"https://api.github.com/users/haelbichalex/following{/other_user}","gists_url":"https://api.github.com/users/haelbichalex/gists{/gist_id}","starred_url":"https://api.github.com/users/haelbichalex/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/haelbichalex/subscriptions","organizations_url":"https://api.github.com/users/haelbichalex/orgs","repos_url":"https://api.github.com/users/haelbichalex/repos","events_url":"https://api.github.com/users/haelbichalex/events{/privacy}","received_events_url":"https://api.github.com/users/haelbichalex/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-11-02T14:48:23Z","updated_at":"2018-11-02T15:39:42Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"Hi!\r\n\r\nWe have a database with around 2.500.000 products and the importer is getting quite slow. No complaining here, just trying to speed the process up again. ☺️ \r\nI looked into the `Magento2DbConnection.php` class and noticed the `echoSlowQueries` feature, which I used to get the following output (I dumped the `$values` array as well here):\r\n```\r\n0.64743208885193:\r\n            SELECT A.`attribute_code`, O.`option_id`, V.`value`\r\n            FROM eav_attribute A\r\n            INNER JOIN eav_attribute_option O ON O.attribute_id = A.attribute_id\r\n            INNER JOIN eav_attribute_option_value V ON V.option_id = O.option_id\r\n            WHERE A.`entity_type_id` = ? AND A.frontend_input IN ('select', 'multiselect') AND V.store_id = 0\r\n\r\narray(1) {\r\n  [0]=>\r\n  string(1) \"4\"\r\n}\r\n0.6437680721283:\r\n            SELECT A.`attribute_code`, O.`option_id`, V.`value`\r\n            FROM eav_attribute A\r\n            INNER JOIN eav_attribute_option O ON O.attribute_id = A.attribute_id\r\n            INNER JOIN eav_attribute_option_value V ON V.option_id = O.option_id\r\n            WHERE A.`entity_type_id` = ? AND A.frontend_input IN ('select', 'multiselect') AND V.store_id = 0\r\n\r\narray(1) {\r\n  [0]=>\r\n  string(1) \"4\"\r\n}\r\n```\r\n\r\nAll other queries are below 0.00 seconds. I can't figure out why these queries are so slow here, because the `eav_attribute` tables aren't that big.\r\n\r\nAny help is appreciated! 👍 "},"comment":{"url":"https://api.github.com/repos/bigbridge-nl/product-import/issues/comments/435421311","html_url":"https://github.com/bigbridge-nl/product-import/issues/4#issuecomment-435421311","issue_url":"https://api.github.com/repos/bigbridge-nl/product-import/issues/4","id":435421311,"node_id":"MDEyOklzc3VlQ29tbWVudDQzNTQyMTMxMQ==","user":{"login":"patrick-bigbridge","id":17122199,"node_id":"MDQ6VXNlcjE3MTIyMTk5","avatar_url":"https://avatars0.githubusercontent.com/u/17122199?v=4","gravatar_id":"","url":"https://api.github.com/users/patrick-bigbridge","html_url":"https://github.com/patrick-bigbridge","followers_url":"https://api.github.com/users/patrick-bigbridge/followers","following_url":"https://api.github.com/users/patrick-bigbridge/following{/other_user}","gists_url":"https://api.github.com/users/patrick-bigbridge/gists{/gist_id}","starred_url":"https://api.github.com/users/patrick-bigbridge/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/patrick-bigbridge/subscriptions","organizations_url":"https://api.github.com/users/patrick-bigbridge/orgs","repos_url":"https://api.github.com/users/patrick-bigbridge/repos","events_url":"https://api.github.com/users/patrick-bigbridge/events{/privacy}","received_events_url":"https://api.github.com/users/patrick-bigbridge/received_events","type":"User","site_admin":false},"created_at":"2018-11-02T15:39:42Z","updated_at":"2018-11-02T15:39:42Z","author_association":"COLLABORATOR","body":"Hi!\r\n\r\nIt's good that you ask. The query that you mention loads all attribute option values into memory. It's executed once every batch (500 products) in OptionResolver::refresh().\r\n\r\nI suspect that executing the query mainly takes a lot of time because all option value data has to be transferred from the database to the PHP process. I think you may have hundreds of thousands of option values. If you execute the query in a mysql client (replacing the '?' by 4) you can see.\r\n\r\nThere are two types of data that the library reloads every batch: category information and option value information. The reason: it is possible that other processes than the library itself change categories and option values while the importer is running. It seemed like a small overhead. Apparently it is not always so.\r\n\r\nI am going to think about a good solution for your problem. In the meanwhile, for my information, can you tell me something about your import: what types of products do you import? Do you import mainly simple attributes or more complex ones as well? How many categories? Do you support multiple store views? And how much time does the complete import currently take? I'd like to collect some reference information. :)\r\n"}}
{"actor":{"display_login":"patrick-bigbridge"}}