SQL Server是否仅在SELECT列表中执行一次计算？

请看以下示例：

SELECT <CalculationA> As ColA,
       <CalculationB> As ColB,
       <CalculationA> + <CalculationB> As ColC
FROM TableA

是否可以将CalculationA和CalculationB分别计算两次？结果两次？

我想进行测试以查看结果，但是，我不确定如何检查这样的结果。

我的假设是它将执行两次计算。
在哪种情况下，取决于所涉及的计算，使用派生表或嵌套视图会更好吗？请考虑以下情况：

SELECT TableB.ColA,
       TableB.ColB,
       TableB.ColA + TableB.ColB AS ColC,
FROM(    
      SELECT <CalculationA> As ColA,
             <CalculationB> As ColB
      FROM TableA
    ) As TableB

在这种情况下，我希望计算仅执行一次？

请有人确认或反驳我的假设？
或指导我如何自己测试类似的东西？

谢谢。

#1 楼

您将需要的大多数信息都将包含在执行计划（以及计划XML）中。

执行以下查询：

SELECT COUNT(val) As ColA,
       COUNT(val2) As ColB,
       COUNT(val) +  COUNT(val2) As ColC
FROM dbo.TableA;

执行计划（已使用sendryone计划浏览器打开）显示了它经历了哪些步骤：

流汇总了EXPR1005和EXPR1006的值

如果我们想知道它们是什么，我们可以从查询计划XML中获得有关这些表达式的确切信息：

<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="COUNT([Database].[dbo].[TableA].[val])">
<Aggregate AggType="COUNT_BIG" Distinct="false">

第一个计算标量计算ColA & ColB：

最后一个计算标量是一个简单的加法运算： />
这是在数据流动时读取它，理论上，如果要进行逻辑执行，则应该从左到右读取它。

在这种情况下，EXPR1004正在调用其他表达式EXPR1002和EXPR1003。这些依次称为EXPR1005和EXPR1006。

能否分别计算两次CalculationA和CalculationB？或者
优化器是否足够聪明，可以一次计算并使用两次
结果？定义为ColC和ColA的计算。

因此，ColB和ColA仅计算一次。

按200个不同的值分组

如果按分组显示的200个不同值（val3）相同：

SET STATISTICS IO, TIME ON;
SELECT SUM(val) As ColA,
       SUM(val2) As ColB,
       SUM(val) +  SUM(val2) As ColC
FROM dbo.TableA
GROUP BY val3;

在ColB中汇总到这200个不同值

对val和val2求和，然后将它们加到ColC中：

即使我们对除一个非唯一值以外的所有值进行分组，对于计算标量，应该看到相同的加法。

向ColA＆ColB添加函数

即使我们将查询更改为：

SET STATISTICS IO, TIME ON;
SELECT ABS(SUM(val)) As ColA,
       ABS(SUM(val2)) As ColB,
       SUM(val) +  SUM(val2) As ColC
FROM dbo.TableA

聚合仍然不会被计算两次，我们只是将val3函数添加到聚合的结果集中，只有一行：

当然，运行ABS()和SUM(ABS(ColA)将使优化器无法使用相同的表达式进行计算SUM(ABS(ColB))。

如果您想在发生这种情况时更深入，我将带您进入Paul White的Query Optimizer Deep Dive-Part 1（直到第4部分）。

通过添加以下提示，可以更深入地研究查询执行阶段：

OPTION 
(
    RECOMPILE, 
    QUERYTRACEON 3604,
    QUERYTRACEON 8605
);

这将公开由优化器创建的输入树。

将先前的两个计算值相加得到ColC，然后转换为：

AncOp_PrjEl COL: Expr1004 

ScaOp_Arithmetic x_aopAdd

    ScaOp_Identifier COL: Expr1002 

    ScaOp_Identifier COL: Expr1003

此信息已经存在于输入树中，甚至在简化阶段已经完成ace，表明优化器立即知道它不必两次执行相同的计算。

感谢您的详细解释。我将需要一些时间来进行消化，因为大部分时间都困扰着我。但是，它给了我一些进一步的研究领域。综上所述，如果我理解正确，那么优化器将最好只执行一次计算，除非它不能保证计算结果相同。

–重心
19年11月6日15:46

@Gravitate总结一下！抱歉，我倾向于在其中放置过多不需要的东西，乐于助您:)。

–兰迪·沃顿根（Randi Vertongen）
19年11月6日在17:29

不，没有什么不必要的，我只是想确保在深入研究细节之前我已经理解了结论。再次感谢。

–重心
19年11月7日在10:39

具有相同的Expression标签并不能保证基础计算仅执行一次。有关此dba.stackexchange.com/a/30947/3690的一些讨论，请参见此答案

–马丁·史密斯
19年11月7日在17:20

#2 楼

如果计算的第一部分是实际计算（Col1 + Col2）而不是函数，则将对每个“计算”步骤执行单独的计算。

SELECT <CalculationA> As ColA,
       <CalculationB> As ColB,
       <CalculationA> + <CalculationB> As ColC
FROM TableA

如果我们使用表中的<CalculationA>和ColA用有效的计算替换您语句中的ColB，并在随后的每个<CalculationB>,...步骤中重复执行此操作，则将对每个步骤分别执行计算结果的实际任务。

要重现我的语句，请将以下代码段粘贴到SQL Server Management Studio中并运行。确保已打开选项包括实际执行计划。

它创建一个数据库，一个表，填充该表并执行计算以产生执行计划。 >
查询将运行并生成类似于以下内容的图形执行计划：

添加值的图形执行计划

与Randi一样答案，我们将重点关注计算标量运算符。

如果您单击SSMS中的查询执行计划，然后右键单击以显示实际计划：

..您会发现以下XML（着重于Compute Scalar部分）：

CREATE DATABASE Q252661
GO
USE Q252661
GO

CREATE TABLE dbo.Q252661_TableA  (
    ColA  INT,
    ColB  INT,
    ColC  INT,
    ColD  INT)   

GO

INSERT INTO Q252661_TableA
(
    ColA,
    ColB,
    ColC,
    ColD
)
VALUES
(
    1,
    2,
    3,
    4
),(
    2,
    4,
    8,
    16
)

GO
SELECT  ColA + ColB AS ColA, 
        ColC + ColD AS ColB,
        ColA + ColB + ColC + ColD AS ColC
FROM    Q252661_TableA

GO

因此，每个单独的计算都在从实际表中检索值的情况。以下XML片段摘自上述摘要：

          <ComputeScalar>
            <DefinedValues>
              <DefinedValue>
                <ColumnReference Column="Expr1003" />
                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColA]+[Q252661].[dbo].[Q252661_TableA].[ColB]">
                  <Arithmetic Operation="ADD">
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                      </Identifier>
                    </ScalarOperator>
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                      </Identifier>
                    </ScalarOperator>
                  </Arithmetic>
                </ScalarOperator>
              </DefinedValue>
              <DefinedValue>
                <ColumnReference Column="Expr1004" />
                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColC]+[Q252661].[dbo].[Q252661_TableA].[ColD]">
                  <Arithmetic Operation="ADD">
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                      </Identifier>
                    </ScalarOperator>
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
                      </Identifier>
                    </ScalarOperator>
                  </Arithmetic>
                </ScalarOperator>
              </DefinedValue>
              <DefinedValue>
                <ColumnReference Column="Expr1005" />
                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColA]+[Q252661].[dbo].[Q252661_TableA].[ColB]+[Q252661].[dbo].[Q252661_TableA].[ColC]+[Q252661].[dbo].[Q252661_TableA].[ColD]">
                  <Arithmetic Operation="ADD">
                    <ScalarOperator>
                      <Arithmetic Operation="ADD">
                        <ScalarOperator>
                          <Arithmetic Operation="ADD">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                              </Identifier>
                            </ScalarOperator>
                          </Arithmetic>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                          </Identifier>
                        </ScalarOperator>
                      </Arithmetic>
                    </ScalarOperator>
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
                      </Identifier>
                    </ScalarOperator>
                  </Arithmetic>
                </ScalarOperator>
              </DefinedValue>
            </DefinedValues>
            <RelOp AvgRowSize="23" EstimateCPU="8.07E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Table Scan" NodeId="1" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2">
              <OutputList>
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
              </OutputList>
              <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" IndexKind="Heap" Storage="RowStore" />
              </TableScan>
            </RelOp>
          </ComputeScalar>

执行计划中有五个<Arithmetic Operation="ADD">步骤。

回答问题

能否将CalculationA和CalculationB分别计算两次？

是的，如果根据示例计算是列的实际总和。最后的计算将是CalculationA + CalculationB的总和。或者优化器是否足够聪明，可以一次计算并使用两次结果？

这取决于您要计算的内容。
-在此示例中：是。
-在Randi的回答中：不。

我的假设是它将执行两次计算。

某些计算。

在这种情况下，取决于所涉及的计算，使用派生表或嵌套视图会更好吗？

正确。

完成后，可以再次删除数据库：

                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColA]+[Q252661].[dbo].[Q252661_TableA].[ColB]">
                  <Arithmetic Operation="ADD">

谢谢您的意见。您的答案似乎比@Randi的答案更为悲观，因为在优化程序必须开始多次计算之前，计算有多复杂？

–重心
19年11月7日在14:09

与悲观无关。这只是事实。您的问题和后续答案取决于您正在执行的“计算”，可能还取决于许多未知因素。您将不得不尝试进行开发和/或DBA活动期间期望遇到的计算。

– John K. N.
19年11月8日在7:25

您可以乐观或悲观地描述事实。我了解这取决于确切的计算。我的评论仅是指出@Randi的答案表明，优化器比您似乎更擅长重用计算。我知道最好针对特定的计算自己尝试一下，但是正如我在问题中说的那样，在收到您的答案之前，我不知道如何检查这些计算是否被重用。所以谢谢。你们俩都给了我很多进一步研究的途径。

–重心
19年11月8日在9:06

#3 楼

由于该问题已经有了很好的答案，因此我将重点放在DRY方面（DRY =不要重复自己）。

如果要在同一个查询中多次进行相同的计算，我就习惯使用CROSS APPLY（请不要忘记GROUP BY / WHERE / ORDER BY，在这里相同的计算往往会一次又一次地出现）。

SELECT calc.ColA,
       calc.ColB,
       calc.ColA + calc.ColB AS ColC
  FROM TableA AS a
 CROSS APPLY (SELECT a.Org_A * 100 AS ColA
                   , a.Org_B / 100 AS ColB
             ) AS calc
 WHERE calc.ColB = @whatever
 ORDER BY calc.ColA

当一种计算依赖于另一种计算时，没有理由不使用多个CROSS APPLY调用来计算中期结果（同样，当您需要使用最终结果时） WHERE / ORDER BY）

SELECT calc1.ColA,
       calc2.ColB,
       calc3.ColC
  FROM TableA AS a
 CROSS APPLY (SELECT a.Org_A    * 100        AS ColA) AS calc1
 CROSS APPLY (SELECT calc1.ColA * 100        AS ColB) AS calc2
 CROSS APPLY (SELECT calc1.ColA + calc2.ColB AS ColC) AS calc3
 WHERE calc.ColB = @whatever
 ORDER BY calc.ColA, calc3.ColC

这样做的主要目的是，当您发现错误或必须更改时，您仅需修改/更正一行代码而不是多次出现，并且不必冒同一个计算具有多个（略有不同，因为您忘记更改一个）版本的风险。

PS：关于可读性，CROSS APPLY通常会胜过几个嵌套的子选择（或CTE），尤其是当计算使用来自不同源表的列或您有中期结果时。

#4 楼

如果两个表达式都是确定性的，则优化器将重用先前计算的结果并简单地将它们相加。如果任何一个表达式都不确定，则必须重新计算该表达式。

很高兴看到一些证据来支持您的主张。您可以编辑问题以添加它。

–迈克尔·格林（Michael Green）
19/12/30在10:07

编程黑洞网

SQL Server是否仅在SELECT列表中执行一次计算？

#1 楼

评论

#2 楼

评论

#3 楼

#4 楼

评论