{"id":14825,"date":"2017-12-04T09:00:31","date_gmt":"2017-12-04T00:00:31","guid":{"rendered":"http:\/\/www.techscore.com\/blog\/?p=14825"},"modified":"2018-11-14T16:33:42","modified_gmt":"2018-11-14T07:33:42","slug":"fast_and_low-load_processing_method_when_exporting_csv_from_db_with_rails","status":"publish","type":"post","link":"https:\/\/www.techscore.com\/blog\/2017\/12\/04\/fast_and_low-load_processing_method_when_exporting_csv_from_db_with_rails\/","title":{"rendered":"Rails\u3067DB\u304b\u3089CSV\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3059\u308b\u6642\u306b\u9ad8\u901f\u304b\u3064\u4f4e\u8ca0\u8377\u306a\u51e6\u7406\u65b9\u6cd5\u3068\u306f\uff1f"},"content":{"rendered":"<p>\u3053\u3093\u306b\u3061\u306f\u3002\u6a2a\u90e8\u3067\u3059\u3002<br \/>\n\u3053\u308c\u306f <a href=\"\/blog\/2017\/11\/28\/techscore-advent-calendar-2017\/\">TECHSCORE Advent Calendar 2017<\/a> \u306e4\u65e5\u76ee\u306e\u8a18\u4e8b\u3067\u3059\u3002<\/p>\n<p>\u4eca\u56de\u306f\u300cRails\u3067DB\u304b\u3089CSV\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3059\u308b\u6642\u306b\u9ad8\u901f\u304b\u3064\u4f4e\u8ca0\u8377\u306a\u51e6\u7406\u65b9\u6cd5\u300d\u306b\u3064\u3044\u3066\u304a\u8a71\u3057\u3055\u305b\u3066\u3044\u305f\u3060\u304d\u307e\u3059\u3002<\/p>\n<p>\u4f7f\u7528\u3057\u305f\u30d0\u30fc\u30b8\u30e7\u30f3\u306f\u4ee5\u4e0b\u306e\u901a\u308a\u3067\u3059\u3002<\/p>\n<p>Ruby : 2.2.4<br \/>\nRails : 5.1.4<\/p>\n<h2>CSV.generate \u3068 each \u3067CSV\u751f\u6210<\/h2>\n<p>Rails\u3067CSV\u30a8\u30af\u30b9\u30dd\u30fc\u30c8(\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9)\u6a5f\u80fd\u3092\u5b9f\u88c5\u3059\u308b\u969b\u306f\u3001\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u51e6\u7406\u3092\u66f8\u304f\u3053\u3068\u304c\u591a\u3044\u304b\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<pre class=\"lang:ruby decode:true \" >\r\ncsv1 = CSV.generate do |csv|\r\n  csv << User.column_names\r\n  User.all.each do |model|\r\n    csv << model.attributes.values_at(*User.column_names)\r\n  end\r\nend\r\nFile.open(\".\/User1.csv\", 'w') do |file|\r\n  file.write(csv1)\r\nend\r\n# \u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\r\nstat = File::stat(\".\/User1.csv\")\r\nsend_file(\".\/User1.csv\", :filename => \"User1.csv\", :length => stat.size)\r\n<\/pre>\n<p>\u3053\u306e\u51e6\u7406\u65b9\u6cd5\u306f\u5168\u4ef6\u3092\u30e1\u30e2\u30ea\u306b\u5c55\u958b\u3057\u3066\u51e6\u7406\u3092\u884c\u3046\u305f\u3081\u3001\u6570\u767e\u4ef6\u5358\u4f4d\u306e\u5c0f\u898f\u6a21\u306a\u3082\u306e\u3067\u3042\u308c\u3070\u554f\u984c\u3042\u308a\u307e\u305b\u3093\u304c\u3001DB\u306e\u30c7\u30fc\u30bf\u4ef6\u6570\u304c\u81a8\u5927\u306b\u306a\u308b\u3068\u30e1\u30e2\u30ea\u4e0d\u8db3\u3067\u30a8\u30e9\u30fc\u306b\u306a\u308b\u53ef\u80fd\u6027\u304c\u6f5c\u3093\u3067\u3044\u308b\u30b3\u30fc\u30c9\u3067\u3059\u3002<br \/>\n\u3053\u308c\u3092\u9632\u3050\u305f\u3081\u306b\u306f\u3001find_each \u3092\u4f7f\u3044\u307e\u3059\u3002<\/p>\n<h2>CSV.generate \u3068 find_each \u3067CSV\u751f\u6210<\/h2>\n<p>find_each \u3092\u4f7f\u3046\u3053\u3068\u3067\u5206\u5272\u3057\u3066\u30ec\u30b3\u30fc\u30c9\u3092\u53d6\u5f97\u3059\u308b\u3053\u3068\u304c\u53ef\u80fd\u306b\u306a\u308b\u306e\u3067\u3001\u30e1\u30e2\u30ea\u4e0d\u8db3\u3067\u30a8\u30e9\u30fc\u306b\u306a\u308b\u554f\u984c\u304c\u89e3\u6d88\u3055\u308c\u307e\u3059\u3002<\/p>\n<p><a href=\"http:\/\/railsdoc.com\/references\/find_each\">http:\/\/railsdoc.com\/references\/find_each<\/a><\/p>\n<p>\u305f\u3060\u3057\u5b9f\u884c\u901f\u5ea6\u306e\u89b3\u70b9\u304b\u3089\u307f\u308b\u3068\u3001find_each \u306f each \u3088\u308a\u3082\u591a\u5c11\u306e\u9045\u308c\u3092\u751f\u3058\u3055\u305b\u308b\u3088\u3046\u3067\u3059\u3002<br \/>\n\u5b9f\u969b\u306b\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u51e6\u7406\u3092\u4f5c\u6210\u3057\u3066\u5b9f\u884c\u6642\u9593\u306e\u96c6\u8a08\u3092\u884c\u3044\u307e\u3057\u305f\u3002<\/p>\n<pre class=\"lang:ruby decode:true\" title=\"users_controller.rb\" >\r\nclass UsersController < ApplicationController\r\n  require 'csv'\r\n  require 'benchmark'\r\n  def new\r\n    @count = User.count\r\n    @result1 = Benchmark.realtime do\r\n      csv1 = CSV.generate do |csv|\r\n        csv << User.column_names\r\n        # ActiveRecord\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u751f\u6210\u3057\u306a\u304c\u3089\u3001\u5168\u30c7\u30fc\u30bf\u3092\u30e1\u30e2\u30ea\u306b\u5c55\u958b\u3057\u3066\u51e6\u7406\r\n        User.all.each do |model|\r\n          csv << model.attributes.values_at(*User.column_names)\r\n        end\r\n      end\r\n      File.open(\".\/User1.csv\", 'w') do |file|\r\n        file.write(csv1)\r\n      end\r\n    end\r\n    @result2 = Benchmark.realtime do\r\n      csv2 = CSV.generate do |csv|\r\n        csv << User.column_names\r\n        # ActiveRecord\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u751f\u6210\u3057\u306a\u304c\u3089\u300110000\u4ef6\u305a\u3064\u30c7\u30fc\u30bf\u3092\u30e1\u30e2\u30ea\u306b\u5c55\u958b\u3057\u3066\u51e6\u7406\r\n        User.find_each(:batch_size => 10000) do |model|\r\n          csv << model.attributes.values_at(*User.column_names)\r\n        end\r\n      end\r\n      File.open(\".\/User2.csv\", 'w') do |file|\r\n        file.write(csv2)\r\n      end\r\n    end\r\n  end\r\nend\r\n<\/pre>\n<pre title=\"new.html.erb\">\r\n\u4ef6\u6570:  <%= @count %>\u4ef6<br>\r\n\u7d50\u679c1: <%= @result1.round(2) %>\u79d2<br>\r\n\u7d50\u679c2: <%= @result2.round(2) %>\u79d2<br>\r\n<\/pre>\n<p>\u3059\u308b\u3068\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u7d50\u679c\u306b\u306a\u308a\u307e\u3057\u305f\u3002<br \/>\n\u5404\u7d50\u679c\u306f10\u56de\u5b9f\u884c\u3057\u305f\u5834\u5408\u306e\u5e73\u5747\u5024\u3067\u3059\u3002<\/p>\n<pre>\r\n\u4ef6\u6570: 100000\u4ef6\r\n\u7d50\u679c1: 11.55\u79d2\r\n\u7d50\u679c2: 12.11\u79d2\r\n<\/pre>\n<p>\u3053\u306e\u3088\u3046\u306b find_each \u306f each \u3088\u308a\u82e5\u5e72\u306e\u9045\u308c\u304c\u898b\u53d7\u3051\u3089\u308c\u307e\u3059\u3002<br \/>\n\u307e\u305f\u3001each \u3068 find_each \u306f\u5b9f\u884c\u306e\u5ea6\u306bActiveRecord\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u4f5c\u6210\u3057\u3066\u3044\u308b\u305f\u3081\u3001DB\u306e\u30ab\u30e9\u30e0\u3084\u4ef6\u6570\u304c\u5897\u3048\u308c\u3070\u3088\u308a\u8ca0\u8377\u304c\u9ad8\u307e\u308a\u3001\u5b9f\u884c\u6642\u9593\u306f\u9577\u304f\u306a\u3063\u3066\u3044\u304d\u307e\u3059\u3002<br \/>\n\u305d\u306e\u305f\u3081\u3001CSV.generate \u3092\u4f7f\u3063\u305f\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3092\u884c\u3046\u969b\u306f\u3001pluck \u306e\u5229\u7528\u3092\u63a8\u5968\u3057\u307e\u3059\u3002<\/p>\n<h2>CSV.generate \u3068 pluck \u3067CSV\u751f\u6210<\/h2>\n<p>pluck \u306f\u5b9f\u884c\u306e\u5ea6\u306bActiveRecord\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u751f\u6210\u3057\u306a\u3044\u306e\u3067\u3001\u3088\u308a\u9ad8\u901f\u306b\u51e6\u7406\u3092\u884c\u3046\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<br \/>\n\u305f\u3060\u3057\u3001pluck \u81ea\u4f53\u306f each \u3068\u540c\u3058\u3088\u3046\u306b\u5168\u30c7\u30fc\u30bf\u3092\u30e1\u30e2\u30ea\u306b\u5c55\u958b\u3057\u3066\u51e6\u7406\u3057\u3066\u3057\u307e\u3046\u306e\u3067\u3001limit \u3068 offset \u3092\u6d3b\u7528\u3057\u3066\u8ca0\u8377\u304c\u304b\u304b\u3089\u306a\u3044\u3088\u3046\u306b\u5de5\u592b\u3057\u3066\u307e\u3059\u3002<\/p>\n<p><a href=\"http:\/\/railsdoc.com\/references\/pluck\">http:\/\/railsdoc.com\/references\/pluck<\/a><\/p>\n<pre class=\"lang:ruby decode:true\" title=\"users_controller.rb\" >\r\n    @result3 = Benchmark.realtime do\r\n      csv3 = CSV.generate do |csv|\r\n        csv << User.column_names\r\n        pos   = 0     # \u958b\u59cb\u4f4d\u7f6e\r\n        range = 10000 # \u7bc4\u56f2\r\n        loop do\r\n          # ActiveRecord\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u751f\u6210\u305b\u305a\u300110000\u4ef6\u305a\u3064\u30c7\u30fc\u30bf\u3092\u30e1\u30e2\u30ea\u306b\u5c55\u958b\u3057\u3066\u51e6\u7406\r\n          results = User.all.limit(range).offset(pos).pluck(*User.column_names)\r\n          break if results.empty?\r\n          pos += range\r\n          results.each do |result|\r\n            csv << result\r\n          end\r\n        end\r\n      end\r\n      File.open(\".\/User3.csv\", 'w') do |file|\r\n        file.write(csv3)\r\n      end\r\n    end\r\n<\/pre>\n<pre title=\"new.html.erb\">\r\n\u7d50\u679c3: <%= @result3.round(2) %>\u79d2<br>\r\n<\/pre>\n<p>\u5b9f\u969b\u306b\u8a08\u6e2c\u3059\u308b\u3068\u4ee5\u4e0b\u306e\u7d50\u679c\u306b\u306a\u308a\u307e\u3057\u305f\u3002<\/p>\n<pre>\r\n\u7d50\u679c3: 5.49\u79d2\r\n<\/pre>\n<p>each \u3084 find_each \u306b\u6bd4\u3079\u3066\u500d\u8fd1\u304f\u306e\u9ad8\u901f\u5316\u306b\u6210\u529f\u3057\u3066\u304a\u308a\u3001\u6bd4\u8f03\u3059\u308b\u3053\u3068\u3067ActiveRecord\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u751f\u6210\u306b\u5927\u304d\u306a\u8ca0\u8377\u304c\u304b\u304b\u3063\u3066\u3044\u308b\u3053\u3068\u304c\u308f\u304b\u308a\u307e\u3059\u3002<br \/>\n\u3053\u3053\u307e\u3067\u3067\u3001CSV.generate \u3092\u4f7f\u3063\u305f\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u306e\u5834\u5408\u306f\u3001pluck \u3092\u4f7f\u3046\u3053\u3068\u3067\u9ad8\u901f\u5316\u3067\u304d\u308b\u3053\u3068\u3092\u793a\u3057\u307e\u3057\u305f\u3002<br \/>\n\u3053\u308c\u3088\u308a\u3082\u3055\u3089\u306b\u4f4e\u8ca0\u8377\u304b\u3064\u9ad8\u901f\u5316\u3067\u304d\u308b\u65b9\u6cd5\u304c\u5b58\u5728\u3057\u307e\u3059\u3002<\/p>\n<h2>SQL\u3067CSV\u751f\u6210<\/h2>\n<p>\u305d\u308c\u306fSQL\u3067\u76f4\u63a5CSV\u3092\u751f\u6210\u3059\u308b\u3053\u3068\u3067\u3059\u3002<\/p>\n<pre class=\"lang:ruby decode:true\"  title=\"users_controller.rb\" >\r\n    @result4 = Benchmark.realtime do\r\n      sql = \"SELECT * FROM users;\"\r\n      cmd = \"sqlite3 -cmd '.headers on' -cmd '.mode csv' -cmd '.output .\/User4.csv' db\/development.sqlite3 '#{sql}'\"\r\n      system cmd\r\n    end\r\n<\/pre>\n<pre title=\"new.html.erb\">\r\n\u7d50\u679c4: <%= @result4.round(2) %>\u79d2<br>\r\n<\/pre>\n<p>SQL\u3067\u76f4\u63a5\u751f\u6210\u3057\u305f\u5834\u5408\u306f\u3001pluck \u3092\u4f7f\u3063\u305f\u5834\u5408\u306e\u7d0410\u5206\u306e1\u4ee5\u4e0b\u306e\u6642\u9593\u3067\u5b8c\u4e86\u3057\u3066\u304a\u308a\u3001\u30c0\u30f3\u30c8\u30c4\u3067\u65e9\u3044\u51e6\u7406\u3067\u3042\u308b\u3053\u3068\u304c\u793a\u3055\u308c\u307e\u3057\u305f\u3002<\/p>\n<pre>\r\n\u7d50\u679c4: 0.34\u79d2\r\n<\/pre>\n<h2>\u307e\u3068\u3081<\/h2>\n<p>\u60f3\u5b9a\u3055\u308c\u308b\u4ef6\u6570\u304c\u5c11\u306a\u304f\u3001\u901f\u5ea6\u3088\u308a\u3082\u30e1\u30f3\u30c6\u30ca\u30f3\u30b9\u6027\u3092\u91cd\u8996\u3059\u308b\u306e\u3067\u3042\u308c\u3070\u3001CSV.generate \u3067CSV\u3092\u751f\u6210\u3059\u308b\u65b9\u6cd5\u304c\u671b\u307e\u3057\u3044\u3067\u3059\u304c\u3001<br \/>\n\u4ef6\u6570\u304c\u6570\u4e07\u4ef6\u5358\u4f4d\u3092\u8d85\u3048\u3001\u30e1\u30f3\u30c6\u30ca\u30f3\u30b9\u6027\u3088\u308a\u3082\u901f\u5ea6\u3092\u91cd\u8996\u3059\u308b\u5834\u5408\u306f\u3001SQL\u3067CSV\u3092\u76f4\u63a5\u751f\u6210\u3059\u308b\u3053\u3068\u304c\u826f\u3044\u3068\u601d\u308f\u308c\u307e\u3059\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3053\u3093\u306b\u3061\u306f\u3002\u6a2a\u90e8\u3067\u3059\u3002<br \/>\n\u3053\u308c\u306f TECHSCORE Advent Calendar 2017 \u306e4\u65e5\u76ee\u306e\u8a18\u4e8b\u3067\u3059\u3002<\/p>\n<p>\u4eca\u56de\u306f\u300cRails\u3067DB\u304b\u3089CSV\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3059\u308b\u6642\u306b\u9ad8\u901f\u304b\u3064\u4f4e\u8ca0\u8377\u306a\u51e6\u7406\u65b9\u6cd5\u300d\u306b\u3064\u3044\u3066\u304a\u8a71\u3057\u3055\u305b\u3066\u3044\u305f\u3060\u304d\u307e\u3059\u3002<br \/><a href=\"https:\/\/www.techscore.com\/blog\/2017\/12\/04\/fast_and_low-load_processing_method_when_exporting_csv_from_db_with_rails\/\">\u7d9a\u304d\u3092\u8aad\u3080...<\/a><\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[276,18],"tags":[141,22,24],"_links":{"self":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/14825"}],"collection":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/comments?post=14825"}],"version-history":[{"count":14,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/14825\/revisions"}],"predecessor-version":[{"id":14829,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/14825\/revisions\/14829"}],"wp:attachment":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/media?parent=14825"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/categories?post=14825"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/tags?post=14825"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}