Ruby on Rails: Model.all.each vs find_by_sql("SELECT * FROM model").each ?

Question

I'm fairly new to RoR. In my controller, I'm iterating over every tuple in the database. For every table, for every column I used to call

SomeOtherModel.find_by_sql("SELECT column FROM model").each {|x| #etc }

which worked fine enough. When I later changed this to

Model.all(:select => "column").each {|x| #etc }

the loop starts out at roughly the same speed but quickly slows down to something like 100 times slower than the the find_by_sql command. These calls should be identical so I really don't know what's happening.

I know these calls are not the most efficient but this is just an intermediate step and I will optimize it more once this works correctly.

So to clarify: Why in the world does calling Model.all.each run so much slower than using find_by_sql.each?

Thanks!

Answer 1

Both calls do make the same SQL call, so they both should be roughly the same speed. Model.all does go through an extra level of indirection. I think that Rails in all collects all the rows from the DB connection, then calls that collection as a param to another method that loops over that collection and creates the model objects. In find_by_sql it does it all in 1 method. Maybe that is a little faster. How large is your data set? If the set is over 100 or so records you shouldn't use all, but use find_in_batches. It uses limits and offsets to run your code in batches and not load the entire table at once into memory. You do need to include the primary key in the select since it uses that to do the order and limit. Example:

Model.find_in_batches(:batch_size => 100) do |group|
  group.each {|item| item.do_something_interesting }
end