downloaded CSV reports overloading servers, how can I grab thousands of records efficiently?

Question

Say I have a page that lets you download CSV reports of your user account's comments, which can have thousands of records. Is there a faster way to go through each users comments? Right now I'm just doing a simple loop.

@user.comments.each do comment
 # create csv
end

Storing data in a database instead of csv files would be a good start. — Dan Bracuk, Oct 28 '16 at 17:01
@DanBracuk it is in a database. There's page where you can download that data to a CSV file. — cvdv, Oct 28 '16 at 17:03
Which `DBMS` you are using. In `SQL SERVER` we can `BCP` utility to generate CSV. It is very efficient you dont have to loop through each record — Pரதீப், Oct 28 '16 at 17:06
I agree with @Prdp http://stackoverflow.com/questions/467452/dump-a-mysql-database-to-a-plaintext-csv-backup-from-the-command-line execute raw SQL query to dump CSV in one go! — Atul Yadav, Oct 28 '16 at 18:02
You should cut some slack if the database is small as there won't be any performance issues. It will be an extra overhead which dosen't worth your time at this stage. But as DB size grows huge then you can give a serious thought to it. — Atul Yadav, Oct 28 '16 at 18:05

score 0 · Answer 1 · answered Oct 29 '16 at 01:27

Outside of DB specific queries to dump straight to CSV, and assuming you really are only dumping a single user's comments at once I would use pluck to avoid the overhead of instantiating all those AR objects. It also means you could limit the columns returned to only those that truly need to be exported.

@user.comments.pluck(:id, :body).each do |comment|
  # add to csv here
end

score 0 · Answer 2 · answered Oct 29 '16 at 03:24

Something like:

require 'csv'
sql = "Select * from comments"
records = ActiveRecord::Base.connection.execute(sql)
CSV.open("path/to/file.csv", "wb") do |csv|
  records.each do |rec|
    csv << rec
  end
end

Should be a couple of orders of magnitude faster than creating an AR object for each row. Haven't tested this code, but you get the idea.

downloaded CSV reports overloading servers, how can I grab thousands of records efficiently?

2 Answers2