ActiveRecord query much slower than straight SQL?

database-performance, postgresql, ruby-on-rails, ruby-on-rails-3

Solution

A couple of things jump out.

Firstly, if this code is being called 2000 times and takes 250ms extra to run, that's ~0.125ms per call to convert the Arel to SQL, which isn't unrealistic.

Secondly, I'm not sure of the internals of Range in Ruby, but `lower..upper` may be doing calculations such as the size of the range and other things, which will be a big performance hit.

Do you see the same performance hit with the following?

sum = Table.
      where(:id => id).
      where(:created_at => "BETWEEN ? and ?", lower, upper).
      sum(:my_column)

Problem

I've been working on optimizing my project's DB calls and I noticed a "significant" difference in performance between the two identical calls below: ``` connection = ActiveRecord::Base.connection() pgresult = connection.execute( "SELECT SUM(my_column) FROM table WHERE id = #{id} AND created_at BETWEEN '#{lower}' and '#{upper}'") ``` and the second version: ``` sum = Table. where(:id => id, :created_at => lower..upper). sum(:my_column) ``` The method using the first version on average takes 300ms to execute (the operation is called a couple thousand times total within it), and the method using the second version takes about 550ms. That's almost 100% decrease in speed. I double-checked the SQL that's generated by the second version, it's identical to the first with exception for it prepending table columns with the table name. - Why the slow-down? Is the conversion between ActiveRecord and SQL really making the operation take almost 2x? - Do I need to stick to writing straight SQL (perhaps even a sproc) if I need to perform the same operation a ton of times and I don't want to hit the overhead? Thanks!

Original source