Is ActiveRecord query much slower than direct SQL?

Question

I am working on optimizing my project project calls, and I noticed a “significant” performance difference between 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) 

A method using the first version takes an average of 300 ms to complete (an operation is called the sum of several thousand times in it), and a method using the second version takes about 550 ms. This is an almost 100% reduction in speed.

I double-checked the SQL generated by the second version, it is identical to the first with an exception for it, adding table columns with the table name.

• Why slowdown? Is the conversion between ActiveRecord and SQL really doing an almost 2x operation?
• Do I need to write direct SQL (maybe even sproc) if I need to perform the same operation several times and I don’t want to hit the overhead?

Thanks!

Answer 1

A couple of things jump out.

Firstly, if this code is called 2000 times and requires 250 ms to run, then ~ 0.125 ms per call to convert Arel to SQL, which is unrealistic.

Secondly, I'm not sure about the Ruby internals, but lower..upper can do calculations such as range size and other things, which will be a big hit.

Do you see the same action with the following?

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