Activerecord SQL optimization tips

In Rails, it’s all too easy to forget that rails activerecord models sit on top of a database. Don’t.

We tackle big data problems, and queries are usually the performance bottleneck. Here are a couple of simple tips for optimizing rails code without resorting to custom SQL (apologies if some of these seem too obvious to mention, but they can be quite common issues when tackling thousands of records) :

1. Understand ActiveRecord.new vs create. New just instantiates the object, whereas create actually inserts it. Use your .save!’s wisely.
2. Select only what you need from the database. E.g. Mdl.where(“#{attr} is not null”).select(attr).each { |o| do_something(o.attr) }  is SO much better than Mdl.all.each { |o| do_something(o.attr) if o.attr }
3. Use transactions for parallel processing to save on COMMIT time. e.g. self.transaction { mdls.each { |o| o.save! } }.
4. Use update_all … e.g. Mdl.all.update_all(:field => nil) is a lot better than mdls.each {|m| m.field = nil; m.save!}

These types of activities can retain the syntactical sugar of rails, which helps with maintenance, readability, and security. It also leads to less overhead when switching backend databases. Finally it’s more fun.

That said, here at Silne we’re crunching some large data on the backend, so there are times when activerecord just won’t cut it, and you need to optimize your data manipulations directly… but don’t discount the flexibility of activerecord.

Advertisement