{"action":"opened","issue":{"url":"https://api.github.com/repos/Maatwebsite/Laravel-Excel/issues/1926","repository_url":"https://api.github.com/repos/Maatwebsite/Laravel-Excel","labels_url":"https://api.github.com/repos/Maatwebsite/Laravel-Excel/issues/1926/labels{/name}","comments_url":"https://api.github.com/repos/Maatwebsite/Laravel-Excel/issues/1926/comments","events_url":"https://api.github.com/repos/Maatwebsite/Laravel-Excel/issues/1926/events","html_url":"https://github.com/Maatwebsite/Laravel-Excel/issues/1926","id":385467037,"node_id":"MDU6SXNzdWUzODU0NjcwMzc=","number":1926,"title":"Performance issues for ods and xlsx","user":{"login":"bugzbrown","id":190893,"node_id":"MDQ6VXNlcjE5MDg5Mw==","avatar_url":"https://avatars1.githubusercontent.com/u/190893?v=4","gravatar_id":"","url":"https://api.github.com/users/bugzbrown","html_url":"https://github.com/bugzbrown","followers_url":"https://api.github.com/users/bugzbrown/followers","following_url":"https://api.github.com/users/bugzbrown/following{/other_user}","gists_url":"https://api.github.com/users/bugzbrown/gists{/gist_id}","starred_url":"https://api.github.com/users/bugzbrown/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bugzbrown/subscriptions","organizations_url":"https://api.github.com/users/bugzbrown/orgs","repos_url":"https://api.github.com/users/bugzbrown/repos","events_url":"https://api.github.com/users/bugzbrown/events{/privacy}","received_events_url":"https://api.github.com/users/bugzbrown/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-11-28T21:36:16Z","updated_at":"2018-11-28T21:36:16Z","closed_at":null,"author_association":"NONE","body":"<!--\r\n\r\nPLEASE READ: FILLING IN THE TEMPLATE IS REQUIRED!\r\nIssues that do not include enough information might not be picked up.\r\n\r\nHave you read Laravel-Excel's \r\ncontributing guidelines (https://laravel-excel.maatwebsite.nl/docs/3.1/getting-started/contributing)\r\nand Code Of Conduct (https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/CODE_OF_CONDUCT.md)?\r\nBy filing an Issue, you are expected to comply with it, including treating everyone with respect.\r\n\r\nPlease prefix your issue with: [PROPOSAL].\r\n\r\n-->\r\n\r\n### Prerequisites\r\nI did a performance study using ODS and XLSX with very large files and found some interesting things.\r\nI used a table with +- 20 columns (all in text format) and about 40K records.\r\nI changed the chunk to 10000 records (since mysql was not the bottleneck here and I wanted to has as few jobs as possible)\r\nI am running horizon and redis to control my queues.\r\n\r\n### Versions\r\n\r\n<!-- Please be as exact and complete as possible when proving version numbers -->\r\n\r\n* PHP version: 7.2\r\n* Laravel version: 5.7\r\n* Package version: 3.1\r\n* Redis version: 4.0.11\r\n\r\n### Description\r\n\r\nWhat I did was change the file:\r\n\r\nJobs/AppendQueryToSheet.php\r\n\r\nI added a couple of debug messages to view how much time was spent on each process of the job:\r\n```\r\n public function handle(Writer $writer)\r\n    {\r\n        $executionStartTime = microtime(true);\r\n        $writer = $writer->reopen($this->filePath, $this->writerType);\r\n        $executionEndTime = microtime(true);\r\n        $seconds = $executionEndTime - $executionStartTime;\r\n        echo  \"Open took $seconds to execute\\n\";\r\n\r\n        $sheet = $writer->getSheetByIndex($this->sheetIndex);\r\n        $executionStartTime = microtime(true);\r\n        $sheet->appendRows($this->query->execute(), $this->sheetExport);\r\n        $executionEndTime = microtime(true);\r\n        $seconds = $executionEndTime - $executionStartTime;\r\n        echo  \"Append took $seconds to execute\\n\";\r\n\r\n        $executionStartTime = microtime(true);\r\n        \r\n        $writer->write($this->sheetExport, $this->filePath, $this->writerType);\r\n        $executionEndTime = microtime(true);\r\n        $seconds = $executionEndTime - $executionStartTime;\r\n        echo  \"Write took $seconds to execute\\n\";\r\n\r\n    }\r\n```\r\nWhat I found was that : \r\nXLSX \r\n- files take very little time to READ (between 1 - 5seconds)\r\n- append took little time (between 1 - 4sec)\r\n- write took progressively longer at each iteration. Starting at 3s - adding about 12s per job executed (during which PHP consumes 100% of the CPU)\r\n\r\nODS\r\n- read is progressively longer at each iteration. Starting at 1s - adding about 6 - 7s per job executed (during which PHP consumes 100% of the CPU)\r\n- append took little time (between 1 - 4sec)\r\n- files take very little time to write (between 1 - 9seconds) - CPU also hits 100% during this\r\n\r\nCSV\r\n- reads are very fast - between 1 - 8secs\r\n- append does not seem to vary between formats (1 - 4sec)\r\n- writes are a bit slower, but range between the 1 - 10s (about 20% slower than opening times)\r\n\r\nSince there is an apparently clear advantage of using CSV over the other file formats for queues, I was wondering if it would not be the case of, when using queues, run everything as a CSV file, and after all appending is done, run one final task of converting the file to its final format.\r\nI haven't had the time to play around with this just yet, but, just by switching from XLSX to CSV I've gained significant performance. I guess \"CloseSheet\" could do this conversion. I am not sure of how much it would help.\r\n \r\n### Example\r\n\r\nNo examples just now... I'll try to get around doing a couple of samples.\r\n\r\n### Additional Information\r\n\r\nI'll try to add this on my fork and show off a couple of live tests."}}
{"actor":{"display_login":"bugzbrown"}}