SQL HAVING 性能 [英] SQL HAVING Performance

问题描述

我正在寻找一种过滤计算列的方法.我只是使用 HAVING 来资助.

Hi I was looking a way to filter an calculated column. I just fund using HAVING.

我想知道是否需要在 have 和 select 上重复计算，我的 SQL 必须计算两次，从而对性能产生影响.

I was wondering if with the need to repeat the calculation on having and select, my SQL have to calculate two times and consequently have performance impact.

即:

USE AdventureWorks2012 ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
ORDER BY SalesOrderID ;

推荐答案

进行计算的费用是安排group by.与按组排列数据相比，执行额外的聚合函数会增加非常非常少的开销.此外，SQL Server 可能足够聪明，可以只计算一次 sum() —— 我不知道，但这将是一个非常合理的优化.

The expense in doing the calculation is in arranging the group by. Doing an additional aggregation function adds very, very little overhead compared to arranging the data in groups. In addition, SQL Server may be smart enough to calculate the sum() only once -- I don't know, but it would be a very reasonable optimization.

注意:传统的聚合函数也是如此.一些，例如 count(distinct) 或统计函数会产生更多的开销，您需要在多次调用它们时更加小心.

Note: this is true of the traditional aggregation functions. Some, such as count(distinct) or the statistical functions incur more overhead and you would want to be more careful about calling them multiple times.

这篇关于SQL HAVING 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！