CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4
Ravindra Yadav
Ravindra Yadav

Reputation: 35

Ruby on Rails best way to update 100k records

I am in a situation where I have to update more than 100k records in the database with best efficient way Please see below my code:

namespace :order do
  desc "update confirmed at field for Payments::Order"
  task set_confirmed_at: :environment do
    puts "==> Updating confirmed_at for orders starts ...".blue
    Payments::Order.find_each(batch_size: 10000) do |orders|
      order_action = orders.actions.where("sender LIKE ?", "%ConfirmJob%").first if orders.actions
      if !order_action.blank?
        orders.update_attribute(:confirmed_at, order_action.created_at)
        puts "order id = #{orders.id} has been updated.".green
      end
    end
    puts "== completed ==".blue
  end
end

Here I am breaking records into 10000 of each batch size and then try to update the record on the basis of some conditions so could anyone suggest me a more efficient way to do the same task.

Thank you in advance!

Upvotes: 1

Views: 2438

Answers (2)

Alexander Sysuiev
Alexander Sysuiev

Reputation: 721

You can try update_all:

Payments::Order.joins(:actions).where(Payment::OrderAction.arel_table[:sender].matches("%ConfirmJob%")).update_all("confirmed_at = actions.created_at")

So your code will look like this:

namespace :order do
  desc "update confirmed at field for Payments::Order"
  task set_confirmed_at: :environment do
    puts "==> Updating confirmed_at for orders starts ...".blue
    Payments::Order.joins(:actions).where(Payments::OrderAction.arel_table[:sender].matches("%ConfirmJob%")).update_all("confirmed_at = actions.created_at")
    puts "== completed ==".blue
  end
end

Update: I've investigated an issue and found out that bulk update with joined table is a long term issue in rails As set part uses string parameter as it is I suggest to add from clause there.

namespace :order do
    desc "update confirmed at field for Payments::Order"
      task set_confirmed_at: :environment do
        puts "==> Updating confirmed_at for orders starts ...".blue
          Payments::Order.joins(:actions).
                where(Order::Action.arel_table[:sender].matches("%ConfirmJob%")).
                update_all("confirmed_at = actions.created_at FROM actions")
        puts "== completed ==".blue
      end
    end

Upvotes: 4

Fran Martinez
Fran Martinez

Reputation: 3042

You are doing Payments::Order.find_each so your solution will loop for each Payment::Order when you only want to loop for the ones having actions.server like '%ConfirmJob%', so I will go with this solution: 

Payments::Order
  .includes(:actions)
  .joins(:actions)
  .where("actions.server like '%?%'", "ConfirmJob")
  .find_each do |order|
    order_action = order.actions.first
    order.update!(confirmed_at: order_action.created_at)
end

Upvotes: 0

Related Questions