Ruby On Rails : ActiveRecord : Batch update/create : Updating/Creating Multiple records at the same time

Sometime you may require to update multiple rows in your tables with different data. If you were looking for updating all record with same data then you should go for update_all method.

Batch update

 # Updating multiple records; different col with different data:
  people = { 1 => { "first_name" => "David" }, 2 => { "first_name" => "Jeremy" } }
  Person.update(people.keys, people.values)

This update method is a class method defined in ActiveRecord base class. Its different from the update method in following example

 # Updating multiple records:
  @person = Person.find(params[:id])
  @person.update(person_params)

Example above describes batch updates however if you need to query db for batches of records you can refer to this link  http://api.rubyonrails.org/classes/ActiveRecord/Batches.html#method-i-find_in_batches

Caveat

For every record, there will be a query sent(TCP request) to the Database server. So this is gonna be a costly solution in terms of CPU, Memory and Connection pools.

Efficient solution:

Still searching, please suggest me via comments.

One of the soln in PG database is, we can compile RAW SQL like below and execute the SQL on the database via db_connection like

SQL = <<-SQL 
    update test as t set
    column_a = c.column_a,
    column_c = c.column_c
    from (values
           ('123', 1, '---'),
           ('345', 2, '+++')  
         ) as c(column_b, column_a, column_c) 
    where c.column_b = t.column_b;
    SQL

Book.connection.execute(SQL)

This link can also be helpful 

 


Batch create

# Create a single new object
User.create(:first_name => 'Jamie')

# Create a single new object using the :admin mass-assignment security role
User.create({ :first_name => 'Jamie', :is_admin => true }, :as => :admin)

# Create a single new object bypassing mass-assignment security
User.create({ :first_name => 'Jamie', :is_admin => true }, :without_protection => true)

# Create an Array of new objects
User.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }])

# Create a single object and pass it into a block to set other attributes.
User.create(:first_name => 'Jamie') do |u|
  u.is_admin = false
end

# Creating an Array of new objects using a block, where the block is executed for each object:
User.create([{ :first_name => 'Jamie' }, { :first_name => 'Jeremy' }]) do |u|
  u.is_admin = false
end

Caveat

For every record, there will be a query sent(TCP request) to the Database server. So this is gonna be a costly solution; both in terms of CPU, Memory and Connection pools.

Efficient solution:

You can use activerecord_import gem to get your things done in the most efficient way possible.

Here’s a recursive example using the #import method: Assume that Books has_many Reviews

books = []
10.times do |i| 
  book = Book.new(:name => "book #{i}")
  book.reviews.build(:title => "Excellent")
  books << book
end
Book.import books, recursive: true

Features

Here’s a list of some of the high-level features that activerecord-import provides:

  • activerecord-import can work with raw columns and arrays of values (fastest)
  • activerecord-import works with model objects (faster)
  • activerecord-import can perform validations (fast)
  • activerecord-import can perform on duplicate key updates (requires MySQL or Postgres 9.5+)

 

 

Useful links

http://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql

Advertisements

2 thoughts on “Ruby On Rails : ActiveRecord : Batch update/create : Updating/Creating Multiple records at the same time

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s