ActiveRecord query much slower than straight SQL?

Question

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!

Tensible

just use .explain and look at the query that was generated, i'm sure it looks different and thats why it takes so much longer – Achorn

I double-checked the query plans, they are both identical, cost and all. Had to substitute .select from .sum in the second version, as you get a Fixnum back from that one and I can't find a way to do a .explain on the query that was used to generate it. – Tensible

Answer 1

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)

Predestinarian

As far as I understand, the .. is simply syntactic sugar that ActiveRecord transforms into a BETWEEN statement if it determines that the operands are Time objects. I tried that version and kept getting the same exact numbers as the slow version. Basically it does sound like that conversion is taking up the time. I'll profile it to gain more granularity. – Tensible

Yeah, it still has to generate a Range object that it passes in, but I guess it doesn't do any hard work because an iterator is not generated. Could AR be making objects for each item before summing them? That might slow it down. Seems unlikely though. – Predestinarian

Based on profiling, the first version jumps straight into executing the query. The second version however spends about an extra 35% of the overall execution time on a 30 levels deep stack of various AR magic. The version slower version can be seen here: pastebin.com/bipTy3c5 The straight SQL version is here: pastebin.com/LysaGUTy – Tensible

On that note, what's a perf lover supposed to do here? Am I to make manual queries whenever I really need to tighten things up? Is there perhaps a more systemic approach to this (maybe a gem or something else that I can apply in mass), or is it all going to be on a per-need basis, as in, rewrite all of the fat loops by hand? – Tensible

You did not mention which database you are using, but "prepared statement" may turn out to be a better option; but be very careful since postgesql, which I use heavily, can sometimes be bone-headed about this by not reevaluating the query plan on subsequent calls to the prepared statement. There is an important discussion of this here: patshaughnessy.net/2011/10/22/…. On the whole, if I am going to execute the same query 2000 times, I tend to move the query into the database as a stored procedure, and call that. YMMV. – Convexoconvex