Dreadfully slow CSV generation

Question

Preface: We are working hard to upgrade our apps Ruby and Rails versions, but need to work with what we're on at the moment.

We have functionality to export data to CSV in our RoR app.

We recently upgraded to Ruby 1.8.7, Rails 3.1, and the performance of this CSV output slowed incredibly. We're talking about over a minute for ~2000 records, which seems a bit ridiculous.

Also, when a request for this is processing, it basically brings down the server for other requests to this app and others.

I have:

Upgraded from CSV to the FasterCSV gem (no difference or even slower!)
Made sure that we are eager-loading everything possible in the AR query
Verified that it is not query time, but the actual call to FasterCSV to generate the CSV from the AR collection, that is taking vast majority of response time

I'm pretty sure that getting to Ruby 1.9.x and higher Rails versions will allow for better performance, but really need to be able to achieve acceptable response times (<30 seconds, at least) now.

Any help/guidance much appreciated.

score 2 · Accepted Answer · answered Aug 16 '15 at 08:14

Had the same problem with slow CSV export while using the "standard" csv dump

which looked like this:

CSV.generate do |csv|
  csv << self.column_names

  self.all.find_in_batches(:batch_size => 10000).with_index do |batch,batch_index|
    puts (self.model_name.human.to_s + ": batch " + batch_index.to_s)
    batch.each do |row|
      csv << row.attributes.values_at(*column_names)
    end
  end
end

using batches unfortunately didn't help. what I noticed that the performance was hurt by the Rails ActiveRecord which is not needed while making a simple db dump

the solution was using select_all SQL queries while bypassing the activerecord objects creation, like this:

CSV.generate do |csv|
  csv << self.column_names
  self.connection.select_all("select * from #{self.table_name}").each do |row|
    csv << row.values
  end
end

what improved the performance dramatically from 120 seconds into about 2 seconds for about 130K rows export on a very basic server

Hope it helps

How would that SQL query work with active record associations (Parent <-> child)? Thanks a lot! — CottonEyeJoe, Jul 26 '17 at 07:54
@CottonEyeJoe you will have to modify the SQL query to use left or inner join on the child or parent table in order to make data from there to appear, no simple solution unfortunately — Vitali Mogilevsky, Jul 26 '17 at 10:08

Dreadfully slow CSV generation

1 Answers1