ActiveRecord查询比直接SQL慢得多？ [英] 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}'")

和第二个版本：

sum = Table.
      where(:id => id, :created_at => lower..upper).
      sum(:my_column)

使用第一个版本的方法平均需要300毫秒才能执行（该操作总共被称为数千次操作），使用第二个版本的方法大约需要550ms。速度几乎降低了100％。

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.

我仔细检查了第二个版本所生成的SQL，它与第一个版本相同，唯一不同的是它在表列前添加了表名。

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.

• 为什么放慢速度？ ActiveRecord和SQL之间的转换是否真的使该操作花费了几乎2倍？


• 如果我需要执行相同的操作，是否需要坚持编写简单的SQL（也许甚至是sproc）？吨的时间，我不想打架吗？

谢谢！

推荐答案

几件事跳了出来。

首先，如果此代码被调用2000次并需要花费250ms的额外时间运行，则每次调用将Arel转换为SQL的时间约为0.125ms，这是不现实的

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.

其次，我不确定Ruby中Range的内部结构，但 lower..upper 可能

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)

这篇关于ActiveRecord查询比直接SQL慢得多？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！