Update ActiveRecord Entries by Batch in Rails 3

       8/12/2013        MySQL , Ruby On Rails

I've been working in with Ruby on Rails for five years and one usual problem I encounter is the speed cause by multiple lines of code I create that is used for simple logical functions.

Through the years I've been learning a lot of methods to simplify my codes in a such a way that it won't hurt my web page's speed.

One of the usual cases where I used complex lines is when updating some entries on the ActiveRecord that usually comes like this:

# note that this is just a sample code
# ruby_code.1

@accounts = Account.find(:all, :conditions => ["is_login=?", true])
@accounts.each do |account|
 account.is_login = false
 account.save
end

or a simpler block by doing something like this:

# note that this is just a sample code
# ruby_code.2

Account.where(:is_login => true).each do |account|
 account.update_attributes(:is_login, true)
end

Either of the codes above would surely hurt the speed of our pages when it loads especially when we are dealing with huge number of entries. Imagine the lines of SQL requests that is thrown with these codes. First it would load the records then send updates requests per record. It should be something like this when transformed to SQL lines:

-- sql_code.1
-- look for the accounts where condition is true

SELECT * FROM `accounts` WHERE `is_login` = true;

-- then send the following updates 
-- depending on the number of accounts that passed the conditions
-- of the first line of code
UPDATE  `accounts` SET  `is_login` = '1' WHERE `id` = [INSERT ID HERE];

though as SQL syntax it could be simplified as:

-- sql_code.2

UPDATE  `accounts` SET  `is_login` =  '1' WHERE  `is_login` =0;

That line should be simpler and better than the first set of SQL codes. Now using a ruby code for this, we can use the update_all to do the job for us. The first and second ruby code can then be simplified as:

# ruby_code.3

Account.update_all({ :is_login => false }, {:is_login => true })


The first argument, { :is_login => false }, is the column that you want to update. The second, {:is_login => true }, is the conditions that goes to the WHERE parameters of the SQL statement.







3 comments :

  1. Petteri Räty8/12/13, 7:11 PM

    http://api.rubyonrails.org/classes/ActiveRecord/Relation.html#method-i-update_all

    update_all is available on ActiveRecord::Relation so the best way to write your code is:

    Account.where(is_login: true).update_all(is_login: false)

    You are incorrect in saying that this is just a simplification of the original code. The two behave differently because:

    "This method constructs a single SQL UPDATE statement and sends it straight to the database. It does not instantiate the involved models and it does not trigger Active Record callbacks or validations."

    ReplyDelete
  2. Oh yes, thanks for correcting. I did forgot to mention the fallback of this method. You are correct that it does not trigger the ActiveRecord callbacks and validations.

    Thanks for mentioning.

    ReplyDelete
  3. I will serve this as my reference on my journey to ROR AJ! Thanks! By the way, I'm shock that your already coding in ROR for 5 years! That's nice!

    ReplyDelete

Thanks for dropping by and I would really appreciate if you can drop some comments..

Social Connection


۞