3
\$\begingroup\$

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
\$\endgroup\$
2
\$\begingroup\$

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.

|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ Thank you for your great suggestions. I'll follow them step-by-step and update the result here. Have a nice day. :) \$\endgroup\$ – Đỗ Tiến Sep 6 '16 at 9:57
  • \$\begingroup\$ I have got noticeable speed improvement after following your suggestions. Thanks again, and have a wonderful day. \$\endgroup\$ – Đỗ Tiến Sep 6 '16 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.