ActiveRecord查询比直接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.

使用第一个版本的方法平均需要300毫秒才能执行(该操作在其中总共需要几千次),而使用第二个版本的方法需要大约550毫秒。这几乎是速度下降100%。

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.

我仔细检查了第二个版本生成的SQL,它与第一个版本的第一个相同,但是它使用表名添加表列。

Why the slow-down? Is the conversion between ActiveRecord and SQL really making the operation take almost 2x?

为什么减速? ActiveRecord和SQL之间的转换是否真的使操作几乎耗费了2倍?

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?

如果我需要执行相同的操作很多次并且我不想达到开销,我是否需要坚持直接编写SQL(甚至可能是sproc)?

Thanks!

1 个解决方案

#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.

首先,如果这个代码被调用了2000次并且需要额外运行250ms,那么每次调用将这个代码转换为0.125毫秒就可以将Arel转换为SQL,这不是不现实的。

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.

其次,我不确定Ruby中的Range的内部结构,但是较低的..上层可能正在进行计算,例如范围的大小和其他事情,这将是一个很大的性能影响。

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)

#1