Inserting data from CSV into MySQL DB is very slow

Question

Trying to insert data from a CSV file to a MySQL DB using Ruby, and it's very slow. Note that this is not a Rails application, just stand-alone Ruby script.

Here is my code:

def add_record (data1, data2, time)
    date = DateTime.strptime(time, "%m/%d/%y %H:%M")
    <my table>.create(data1: data1, data2: data2, time: date)
end

def parse_file (file)
    path = @folder + "\\" + file
    CSV.foreach(path, {headers: :first_row}) do |line|
        add_record(line[4], line[5], line[0])
    end
end

def analyze_data ()
    Dir.foreach @folder do |file|
        next if file == '.' or file == '..'
        parse_file file
    end     
end

And my connection:

@connection = ActiveRecord::Base.establish_connection(  
  :adapter=> "mysql2",  
  :host => "localhost",  
  :database=> <db>,
  :username => "root",
  :password => <pw>
)

Any help appreciated.

I wrote [This Answer](http://stackoverflow.com/a/32702768) up for a fella. Took his 8 hr import down to a minute or so. Seems extreme, but that is what he said. — Drew, Oct 23 '15 at 13:46

score 0 · Answer 1 · answered Oct 23 '15 at 16:23

0

Use the zdennis/activerecord-import gem. you can insert tons records quickly.

answered Oct 23 '15 at 16:23

rochefort

136
6

score 0 · Accepted Answer · answered Oct 23 '15 at 16:46

Use Load Data Infile.

Here is a nice article on performance and strategies titled Testing the Fastest Way to Import a Table into MySQL. Don't let the mysql version of the title or inside the article scare you away. Jumping to the bottom and picking up some conclusions:

The fastest way you can import a table into MySQL without using raw files is the LOAD DATA syntax. Use parallelization for InnoDB for better results, and remember to tune basic parameters like your transaction log size and buffer pool. Careful programming and importing can make a >2-hour problem became a 2-minute process. You can disable temporarily some security features for extra performance

You might just find your times greatly reduced.

Inserting data from CSV into MySQL DB is very slow

2 Answers2

Linked