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?

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
–
antpawJun 18 '12 at 9:11

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.
–
Alexandr KurilinJun 18 '12 at 9:28

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.
–
Alexandr KurilinJun 18 '12 at 9:35

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.
–
iHiDJun 18 '12 at 9:47

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
–
Alexandr KurilinJun 18 '12 at 10:21

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?
–
Alexandr KurilinJun 18 '12 at 10:23

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.
–
thisfellerJun 18 '12 at 14:48