wake-up-neo.net

SQL Server esegue i calcoli solo in un elenco SELECT una volta?

Prendi il seguente esempio:

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

Calcolo A e Calcolo B sarebbero calcolati ciascuno due volte?
O l'ottimizzatore sarebbe abbastanza intelligente da calcolarli una volta e usare il risultato due volte?

Vorrei eseguire un test per vedere il risultato da solo, tuttavia, non sono sicuro di come poter verificare qualcosa del genere.

La mia ipotesi è che eseguirà il calcolo due volte.
In quale caso, a seconda dei calcoli coinvolti, potrebbe essere meglio usare una tabella derivata o una vista nidificata? Considera quanto segue:

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

In questo caso, spero che i calcoli vengano eseguiti solo una volta?

Per favore qualcuno può confermare o confutare i miei presupposti? O mi istruisci su come testare qualcosa del genere per me stesso?

Grazie.

29
Gravitate

La maggior parte delle informazioni necessarie saranno nel piano di esecuzione (e nel piano XML).

Prendi questa domanda:

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

Il piano di esecuzione (aperto con Explorer piano sentryone ) mostra quali passaggi ha attraversato:

enter image description here

Con l'aggregazione dello stream aggregando i valori per EXPR1005 & EXPR1006

enter image description here

Se vogliamo sapere quali sono, potremmo ottenere le informazioni esatte su queste espressioni dall'XML del piano di query:

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

Con il primo calcolo scalare di calcolo ColA & ColB:

enter image description here

E l'ultimo scalare di calcolo è una semplice aggiunta:

enter image description here

Questo lo sta leggendo mentre i dati scorrono, in teoria dovresti leggerlo da sinistra a destra se vai oltre l'esecuzione logica.

In tal caso, EXPR1004 Sta chiamando le altre espressioni, EXPR1002 E EXPR1003. A loro volta questi stanno chiamando EXPR1005 & EXPR1006.

Calcolo A e Calcolo B verrebbero calcolati ciascuno due volte? O l'ottimizzatore sarebbe abbastanza intelligente da calcolarli una volta e usare il risultato due volte?

Test precedenti mostrano che in questo caso ColC è semplificato come aggiunta dei calcoli definiti come ColA & ColB.

Di conseguenza, ColA & ColB vengono calcolati una sola volta.


Raggruppamento per 200 valori distinti

Se stiamo raggruppando per 200 valori distinti (val3) viene mostrato lo stesso:

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;

Aggregando fino a questi 200 valori distinti in val3

enter image description here

eseguendo le somme su val & val2 e poi aggiungendole insieme per ColC:

enter image description here

Anche se stiamo raggruppando tutti tranne un valore non univoco, la stessa aggiunta dovrebbe essere vista per lo scalare di calcolo.


Aggiunta di una funzione a ColA e ColB

Anche se cambiamo la query in questo:

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

Le aggregazioni non verranno ancora calcolate due volte, stiamo semplicemente aggiungendo la funzione ABS() al set di risultati dell'aggregazione, che è una riga:

enter image description here

enter image description here

Ovviamente, l'esecuzione di SUM(ABS(ColA) & SUM(ABS(ColB)) renderà l'ottimizzatore incapace di usare la stessa espressione per calcolare ColC.


Se vuoi approfondire la situazione, ti spingerei verso Query Optimizer Deep Dive - Parte 1 (fino alla Parte 4) di Paul White.

Un altro modo per approfondire le fasi di esecuzione della query è aggiungere questi suggerimenti:

OPTION 
(
    RECOMPILE, 
    QUERYTRACEON 3604,
    QUERYTRACEON 8605
);

Ciò esporrà l'albero di input creato dall'ottimizzatore.

L'aggiunta dei due precedenti valori calcolati per ottenere ColC viene quindi tradotta in:

AncOp_PrjEl COL: Expr1004 

ScaOp_Arithmetic x_aopAdd

    ScaOp_Identifier COL: Expr1002 

    ScaOp_Identifier COL: Expr1003 

Questa informazione è già presente nell'albero di input , anche prima che abbia avuto luogo la fase di semplificazione, dimostrando che l'ottimizzatore sa immediatamente che non deve eseguire lo stesso calcolo due volte.

38
Randi Vertongen

Se la prima parte del calcolo è un calcolo effettivo (Col1 + Col2) e non una funzione, quindi i singoli calcoli vengono eseguiti per ogni passaggio del "calcolo".

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

Se sostituiamo <CalculationA> dalla tua dichiarazione con un calcolo valido usando ColA e ColB da una tabella e ripeti questo per ogni successivo <CalculationB>,... step, quindi l'attività effettiva di calcolo del risultato verrà eseguita singolarmente per ogni step.

Per riprodurre la mia dichiarazione, incolla i seguenti frammenti di codice in SQL Server Management Studio ed esegui. Assicurati di aver attivato l'opzione Includi piano di esecuzione effettivo .

Include Actual Execution Plan

Crea un database, una tabella, popola la tabella ed esegue un calcolo che produce un piano di esecuzione.

 CREATE DATABASE Q252661 
 GO 
 USE Q252661 
 GO 
 
 CREATE TABLE dbo.Q252661_TableA (
 ColA INT , 
 ColB INT, 
 ColC INT, 
 ColD INT) 
 
 GO 
 
 INSERISCI IN Q252661_TableA 
 (
 ColA, 
 ColB, 
 ColC, 
 ColD 
) 
 VALORI 
 ( 
 1, 
 2, 
 3, 
 4 
), (
 2, 
 4, 
 8, 
 16 
) 
 
 GO 
 SELEZIONA ColA + ColB AS ColA, 
 ColC + ColD AS ColB, 
 ColA + ColB + ColC + ColD AS ColC 
 DA Q252661_Tabella A 
 
 GO 

La query verrà eseguita e produrrà un piano di esecuzione grafico simile al seguente:

Graphical Execution Plan of ADDing valuesPiano di esecuzione grafico per l'aggiunta di valori

Come nella risposta di Randi, ci concentreremo sull'operatore Compute Scalar .

Se si fa clic sul piano di esecuzione delle query in SSMS e si fa clic con il pulsante destro del mouse per visualizzare il piano effettivo:

Show Execution Plan XML...

.. troverai il seguente XML (focalizzato sulla porzione Calcolare Scalare ):

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

Pertanto, ogni singolo calcolo viene eseguito più volte nel caso in cui i valori vengano recuperati da una tabella effettiva. Il seguente frammento XML proviene dal riepilogo sopra:

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

Ci sono cinque <Arithmetic Operation="ADD"> passi nel piano di esecuzione.


Rispondere alla tua domanda

Calcolo A e Calcolo B verrebbero calcolati ciascuno due volte?

Sì, se i calcoli sono somme effettive di colonne come nell'esempio. L'ultimo calcolo sarebbe la somma di CalculationA + CalculationB.

O l'ottimizzatore sarebbe abbastanza intelligente da calcolarli una volta e usare il risultato due volte?

Dipende da cosa stai calcolando. - In questo esempio: sì. - Nella risposta di Randi: no.

La mia ipotesi è che avrebbe eseguito il calcolo due volte.

Hai ragione per determinati calcoli.

In tal caso, a seconda dei calcoli coinvolti, potrebbe essere meglio utilizzare una tabella derivata o una vista nidificata?

Corretta.


Una volta terminato, puoi eliminare nuovamente il database:

USE [master]
GO
DROP DATABASE Q252661
4

Dato che ci sono già buone risposte alla domanda, mi concentrerò sull'aspetto DRY (DRY = non ripeterti).

Mi sono abituato a usare CROSS APPLY, se devo fare gli stessi calcoli nella stessa query più volte (non dimenticare GROUP BY / WHERE / ORDER BY, dove gli stessi calcoli tendono a ripetersi ancora e ancora e ancora).

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

Quando un calcolo dipende da un altro, non vi è alcun motivo per non utilizzare più CROSS APPLY chiama per calcolare i risultati provvisori (lo stesso, quando è necessario utilizzare il risultato finale in 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

Il punto principale per farlo è che devi modificare/correggere solo una riga di codice quando trovi un bug o devi cambiare qualcosa invece di più occorrenze e non rischi di avere più (leggermente diverso, poiché hai dimenticato di cambiarne uno ) versioni dello stesso calcolo.

PS: per quanto riguarda la leggibilità CROSS APPLY di solito vincerà rispetto a più sub-selezioni nidificate (o CTE), in particolare quando i calcoli utilizzano colonne di diverse tabelle di origine o si ottengono risultati intermedi.

0
Thomas Franz