Updating a DB entry without having to instantiate an object

rubyonrails-talk
1

Hi,

I noticed that in order to use update_attributes(), AR firsts needs to make a SELECT query to the DB to fetch the data, then AR creates an object from it, and then it issues the UPDATE statement.

What I am trying to do is update a field in the DB without having to do this preliminary SELECT (thus saving a query on my InnoDB table) and the subsequent ORM that will occur. Is it possible with AR? Or do I need to use my own custom SQL query?

2

I don't know if it's possible, but I'd like to ask: Have you measured the performance of your app and found it to be unsatisfactory and the cause to be update_attributes? If no, then just let update_attributes do it's thing. If yes, then I'm sure you'll receive some helpful advice from those who know.

Regards, Craig

3

Hi Craig, I know what you mean, but I prefer to have my app slightly optimized when it seems it is not yet required. I am sure that the claims about Rails being slow comes from people who rely too much on AR magics to handle SQL, and they don't notice that they are making a pure mess with their DB server being hit too many times.

It is like talking about the n+1 problem, you don't see its drawbacks when the DB is small, but you absolutely must get rid of it from the very beginning even if the current speed measured is satisfactory.

It's by leaving small bits and pieces that will eventually pile up that you get a sluggish application.

4

Have you looked at update_all?

MyModel.update_all(['hair = ?, eye = ?', 'brown', 'green'],                     ['full_name = ?', 'Fernando Perez'])

The first argument is the setting part of the SQL and the second argument is the where conditions. They are run through sanitize_sql. It's just one step away from

MyModel.connection.execute("UPDATE my_models SET hair = 'brown', eye = 'green' WHERE full_name = 'Fernando Perez'")

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

5

Rob is right, update_all will take care of updating without instantiating.

I don't believe, though, that update_attributes itself triggers a select. More likely you're seeing the select from initially retrieving the object on which you are calling update_attributes. That is, if you're looking at a typical update action from rails' scaffolding:

def update # this line triggers the select @my_object = MyObject.find(params[:id])

# this line triggers the update if @my_object.update_attributes(params[:my_object]) ... end

The advantage you get here is that you can have business logic triggered from several different places: after_save callbacks, ActiveRecord observers, etc. You may not need them now but at some point they're likely to provide you some extra help that you'll miss if you make a habit of bypassing update_attributes.

Just a note... in 2.1 there IS a nice performance enhancement related to update_attributes. Currently (2.0.x) update_attributes always updates all the attributes even if you only submit a subset of attributes for updating. Rails 2.1 fixes this, only updating the 'dirty' attributes.

6

AndyV wrote:

Rob is right, update_all will take care of updating without instantiating.

Exactly, thank you Rob.

# this line triggers the update if @my_object.update_attributes(params[:my_object]) ... end

In order to to the update this way, you first must do a SELECT.

I tried also MyModel.update(), and it also issues a SELECT before making the update.

Using Rob's MyModel.update_all() simply does the UPDATE without anything else.

The advantage you get here is that you can have business logic triggered from several different places: after_save callbacks, ActiveRecord observers, etc. You may not need them now but at some point they're likely to provide you some extra help that you'll miss if you make a habit of bypassing update_attributes.

The models in which I need validation or callbacks use the regular update_attributes, in this very particular case, there is no need for initially fetching from DB.

Just a note... in 2.1 there IS a nice performance enhancement related to update_attributes. Currently (2.0.x) update_attributes always updates all the attributes even if you only submit a subset of attributes for updating. Rails 2.1 fixes this, only updating the 'dirty' attributes.

At last they did it! That is excellent. Where did you find the changelog about the new features of 2.1?

Best regards,

7

At last they did it! That is excellent. Where did you find the changelog about the new features of 2.1?

I just follow the guys who are playing with the latest toys: www.ryandaigle.com