Imported data from CSV successfully, but slow

Question

Currently I am using the gem activerecord-import to seed more than 55,000 records into my database, and it works fine. However, it takes too much time. I am wondering if there are any other ways to shorten the import time. Any suggestions would be appreciated.

ocean_routes_columns = [:origin_location_id, :destination_location_id, :n1st_lane_id, :n1st_pol_id, :n1st_pod_id, :n1st_skd_dir, :n2nd_lane_id, :n2nd_pol_id, :n2nd_pod_id, :n2nd_skd_dir, :n3rd_lane_id, :n3rd_pol_id, :n3rd_pod_id, :n3rd_skd_dir, :created_by_id, :updated_by_id]
ocean_routes = []
CSV.foreach(Rails.root.join('db/seeds/ocean_routes.csv'), headers: true) do |row|
 origin_location = Location.find_by(code: row[0])
 destination_location = Location.find_by(code: row[1])
 n1st_lane = ServiceLane.find_by(code: row[2])
 n1st_pol = Location.find_by(code: row[3])
 n1st_pod = Location.find_by(code: row[4])
 n1st_skd_dir = OceanRoute.to_bound_code_value(row[5])
 n2nd_lane = ServiceLane.find_by(code: row[6])
 n2nd_pol = Location.find_by(code: row[7])
 n2nd_pod = Location.find_by(code: row[8])
 n2nd_skd_dir = OceanRoute.to_bound_code_value(row[9])
 n3rd_lane = ServiceLane.find_by(code: row[10])
 n3rd_pol = Location.find_by(code: row[11])
 n3rd_pod = Location.find_by(code: row[12])
 n3rd_skd_dir = OceanRoute.to_bound_code_value(row[13])
 val = OceanRoute.new(origin_location: origin_location, destination_location: destination_location, n1st_lane: n1st_lane, n1st_pol: n1st_pol, n1st_pod: n1st_pod, n1st_skd_dir: n1st_skd_dir, n2nd_lane: n2nd_lane, n2nd_pol: n2nd_pol, n2nd_pod: n2nd_pod, n2nd_skd_dir: n2nd_skd_dir, n3rd_lane: n3rd_lane, n3rd_pol: n3rd_pol, n3rd_pod: n3rd_pod, n3rd_skd_dir: n3rd_skd_dir)
 val.created_by = User.find_by(name: 'yo')
 val.updated_by = User.find_by(name: 'yo')
 ocean_routes << val
end
OceanRoute.import ocean_routes_columns, ocean_routes, validate: false

Answer 1

I would try a couple of improvements.

1. Don't call Location.find_by(code: row[0]) on each iteration. Load all the locations once at the beginning with locations = Location.pluck(:id, :code) and then use origin_location_id = locations.find { |l| l[1] == row[0] }.

2. Do the same with the other attributes where you need a location: destination_location_id, n1st_pol_id, etc...

3. Do the same with OceanRoute and ServiceLane

4. Load the user user = User.find_by(name: 'yo') before the cycle and use it in every iteration: val.created_by = user

5. Use the fastest implementation of ActiveRecordImport: https://github.com/zdennis/activerecord-import/wiki/Examples#import-using-columns-and-arrays

Do these steps one by one to improve your performance.

• Fatima: Thank you for your great suggestions. I'll follow them step-by-step and update the result here. Have a nice day. :)
• Fatima: I have got noticeable speed improvement after following your suggestions. Thanks again, and have a wonderful day.