I’m all for keeping the business logic in places where it can be easily isolated and eventually reused. I’m sure I’m not saying anything new, but there are cases where it makes a lot of sense to delegate. Examples of those cases are stored procedures and complex queries. Relational databases are pretty smart in parsing and optimizing SQL and when the database is a separate process on another machine it’s even more efficient to use a different CPU than the main application process.

Today I had the pleasure to apply one fast and simple change that drastically reduced the computation load. Here’s the “before”:

Song.find(:all).select(&:rating).sort_by(&:rating).reverse.first(10)

and here’s the “after”:

Song.find(:all, :joins => [:ratings], :order => "rating DESC", :limit => 10)

Assuming N is the number of songs in the database in the first case you need to load N object instances and issue N + 1 queries to load information about the rating of a specific song. In the second case 1 query is needed and all the filtering is delegated to the database. The nice thing is also the way the SQL string is hidden beautifully in the DSL-ish method call which is very similar to the first version. I remember the old days (for me!) in 2002 when this was just something like connection.executeStatement(“SELECT * BLAH…”) or something and I had to create my own SQLBuilder to reuse and test some of that SQL. O/R mappings tools and fluent interfaces of today are just a huge leap forward.

The take away for the Rails developer: think twice whenever you write or see a find(:all) with no options. Look carefully the console output to see what SQL Rails is generating and how long does it take. If the result set it operates on is reasonably small, no big deal. If the collection is huge, please delegate to the database.

Hey, by the way, do you see what the query is doing? :)