CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqldatabase-performance
Alexandr Kurilin
Alexandr Kurilin

Reputation: 7845

ActiveRecord query much slower than straight SQL?

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.

Thanks!

Upvotes: 9

Views: 6122

Answers (1)

iHiD
iHiD

Reputation: 2438

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)

Upvotes: 3

Related Questions