Aggregazione in Outer Apply vs Left Join vs Derived table


4

Considera la seguente configurazione.Ci sono tre tavoli coinvolti #CCP_DETAILS_TEMP , Period e ACTUALS_DETAILS

#CCP_DETAILS_TEMP avrà 50000 record, ACTUALS_DETAILS può avere 5000000 record e period tabella avrà 2000 record

Dettagli dell'indice:

CREATE UNIQUE CLUSTERED INDEX IX_CCP_DETAILS_TEMP
    ON #CCP_DETAILS_TEMP (CCP_DETAILS_SID)

CREATE NONCLUSTERED INDEX IXN_ACTUALS_DETAILS_PERIOD_SID_RS_MODEL_SID_CCP_DETAILS_SID_QUANTITY_INCLUSION
    ON ACTUALS_DETAILS (PERIOD_SID, CCP_DETAILS_SID, RS_MODEL_SID, QUANTITY_INCLUSION)
    INCLUDE( SALES, QUANTITY, DISCOUNT) 

CREATE UNIQUE CLUSTERED INDEX IX_PERIOD
    ON PERIOD (PERIOD_SID)

Ho un requisito per il quale ho scritto tre diversi modi per raggiungere il risultato.Ora voglio sapere qual è il migliore.

Tutte e tre le query sono in esecuzione più o meno nello stesso tempo.Ho bisogno di alcuni consigli di esperti su quali si esibiranno meglio.C'è qualche svantaggio in qualsiasi approccio

Approccio 1:Outer Apply

Tempo preso:4615 Milli Seconds

SELECT c.CCP_DETAILS_SID,
       A.PERIOD_SID,
       SALES,
       QUANTITY
FROM  #CCP_DETAILS_TEMP c
       CROSS JOIN (SELECT PERIOD_SID
             FROM  BPIGTN_GAL_APP_DEV_ARM..PERIOD
             WHERE PERIOD_SID BETWEEN 577 AND 624)A
       OUTER apply (SELECT Sum(SALES),
                 Sum(QUANTITY)
             FROM  [DBO].[ACTUALS_DETAILS] ad
             WHERE a.PERIOD_SID = ad.PERIOD_SID
                 AND ad.CCP_DETAILS_SID = c.CCP_DETAILS_SID
                 AND QUANTITY_INCLUSION = 'Y') oa (sales, quantity)

enter image description here

Statistiche di query:

Tabella 'PERIODO'.Conteggio scansioni 1, letture logiche 2, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella "#CCP_DETAILS_TEMP".Conteggio scansioni 16, letture logiche 688, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella 'Worktable'.Conteggio scansioni 16, letture logiche 807232, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture read-ahead lob 0.

Tabella "ACTUALS_DETAILS".Conteggio scansioni 1200000, letture logiche 3859053, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella 'Worktable'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella 'Worktable'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server: tempo CPU = 36796 ms, tempo trascorso = 4615 ms.

Tempi di esecuzione di SQL Server: tempo CPU = 0 ms, tempo trascorso = 0 ms.

Approccio 2:Left Join

Tempo preso:4293 Milli Seconds

SELECT c.CCP_DETAILS_SID,
       A.PERIOD_SID,
       Sum(SALES),
       Sum(QUANTITY)
FROM  #CCP_DETAILS_TEMP c
       CROSS JOIN (SELECT PERIOD_SID
             FROM  BPIGTN_GAL_APP_DEV_ARM..PERIOD
             WHERE PERIOD_SID BETWEEN 577 AND 624) a
       LEFT JOIN [ACTUALS_DETAILS] ad
          ON a.PERIOD_SID = ad.PERIOD_SID
            AND ad.CCP_DETAILS_SID = c.CCP_DETAILS_SID
            AND QUANTITY_INCLUSION = 'Y'
GROUP BY c.CCP_DETAILS_SID,
        A.PERIOD_SID 

enter image description here

Statistiche di query:

Tabella "ACTUALS_DETAILS".Conteggio scansioni 17, letture logiche 37134, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella 'PERIODO'.Conteggio scansioni 1, letture logiche 2, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella "#CCP_DETAILS_TEMP".Conteggio scansioni 16, letture logiche 688, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella 'Worktable'.Conteggio scansioni 16, letture logiche 807232, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture read-ahead lob 0.

Tabella 'File di lavoro'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella 'Worktable'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server: tempo CPU = 7983 ms, tempo trascorso = 4293 ms.

Tempi di esecuzione di SQL Server: tempo CPU = 0 ms, tempo trascorso = 0 ms.

Approccio 3:Aggregating first and Left join:

Tempo preso:4200 Milli Seconds

SELECT c.CCP_DETAILS_SID,
       A.PERIOD_SID,
       SALES,
       QUANTITY
FROM  #CCP_DETAILS_TEMP c
       CROSS JOIN (SELECT PERIOD_SID
             FROM  BPIGTN_GAL_APP_DEV_ARM..PERIOD
             WHERE PERIOD_SID BETWEEN 577 AND 624) a
       LEFT JOIN (SELECT CCP_DETAILS_SID,
                PERIOD_SID,
                Sum(SALES)  SALES,
                Sum(QUANTITY) QUANTITY
            FROM  [ACTUALS_DETAILS] ad
            WHERE QUANTITY_INCLUSION = 'Y'
            GROUP BY CCP_DETAILS_SID,
                 PERIOD_SID) ad
          ON a.PERIOD_SID = ad.PERIOD_SID
            AND ad.CCP_DETAILS_SID = c.CCP_DETAILS_SID

enter image description here

Statistiche di query:

Tabella "ACTUALS_DETAILS".Conteggio scansioni 17, letture logiche 37134, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella 'Worktable'.Conteggio scansioni 16, letture logiche 807232, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture read-ahead lob 0.

Tabella 'File di lavoro'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella 'PERIODO'.Conteggio scansioni 1, letture logiche 2, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella "#CCP_DETAILS_TEMP".Conteggio scansioni 16, letture logiche 688, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella 'Worktable'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server: tempo CPU = 7731 ms, tempo trascorso = 4200 ms.

Tempi di esecuzione di SQL Server: tempo CPU = 0 ms, tempo trascorso = 0 ms.

4

Per domande future, pubblica i piani di esecuzione effettivi utilizzando Paste The Plan .Penso di essere stato in grado di decodificare tutti i dettagli rilevanti usando gli screenshot e l'output STATISTICS ma potrei aver sbagliato alcune cose.Sembra che i tuoi piani siano in esecuzione con un DOP di 16, circa 50000 righe vengono restituite da #CCP_DETAILS_TEMP e 24 righe vengono restituite da PERIOD .

In tutti e tre i piani di query l'unione tra #CCP_DETAILS_TEMP e PERIOD viene eseguita allo stesso modo, ha lo stesso output STATISTICS e funge da tabella esterna nel join a ACTUALS_DETAILS .Sembra che SQL Server stia facendo la cosa giusta per quel join e non è così interessante, quindi salterò quella parte.È irrilevante per il tuo paragone.

Ciò che è rilevante è il pattern di accesso al tavolo su ACTUALS_DETAILS .Tutte e tre le query utilizzano le ricerche di indice sull'indice di copertura, ma le ricerche di indice vengono eseguite in modo diverso.Nella prima query, 1200000 ricerche vengono eseguite utilizzando le colonne PERIOD_SID e CCP_DETAILS_SID .Nella seconda e terza query, 17 ricerche vengono eseguite utilizzando solo PERIOD_SID .Credo che tutte le righe vengano recuperate con PERIOD_SID BETWEEN 577 AND 624 , in modo che la ricerca dell'indice possa essere effettivamente considerata una scansione dell'indice parallela che inizia con PERIOD_SID = 577 e termina con PERIOD_SID = 624 .Ciò comporta una grande differenza nell'IO tra le query:

Tabella "ACTUALS_DETAILS".Conteggio scansioni 1200000, letture logiche 3859053, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tabella "ACTUALS_DETAILS".Conteggio scansioni 17, letture logiche 37134, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

C'è un grande vantaggio nel non leggere le stesse pagine più e più volte.Mentre è vero che l'approccio di pseudo-scansione può tecnicamente leggere le pagine che non sono necessarie, in generale fai molto meno IO.Credo inoltre che la differenza di I/O sia direttamente responsabile della grande differenza di tempo tra la prima query e le altre due query: 36796 ms vs 7731 ms.Mentre la prima query veniva eseguita, in media manteneva 9 CPU completamente occupate rispetto a meno di 2 CPU occupate per la seconda e terza query.Questo è un grosso svantaggio per la prima query e lo si noterà su un sistema occupato o se le query sono state forzate per l'esecuzione con DOP inferiore.Nella mia esperienza limitata con APPLY ho notato che l'ottimizzatore di query di SQL Server tende a implementarlo come un join di loop annidato con indici di ricerca.Questo dovrebbe essere considerato una prova aneddotica e sono sicuro che ci sono delle eccezioni, ma spiega quello che vedi qui.

Le query 2 e 3 implementano il join a ACTUALS_DETAILS come join hash.Presumo che l'idea alla base della spinta del GROUP BY nella tabella derivata ad fosse tale da consentire a SQL Server di eseguire l'aggregazione in anticipo e di unirsi a un numero inferiore di righe e di aggregare meno righe.Tuttavia, SQL Server ha riscritto la seconda query per eseguire comunque l'aggregazione in anticipo.È possibile sapere perché gli operatori di aggregazione e di corrispondenza hash dello stream si trovano a destra dell'operatore di corrispondenza hash (right outer join) nel secondo piano.Per quanto posso dire, il secondo e il terzo piano di query sono effettivamente gli stessi, sebbene il terzo piano abbia alcuni operatori di costi aggiuntivi dello 0%.

Personalmente non considererei la differenza tra 4293 e 4200 ms di tempo trascorso o 7983 e 7731 ms tempo di CPU per essere statisticamente significativo.È possibile che se si eseguono le query più volte, la seconda query potrebbe essere più veloce della terza query.Userei qualsiasi stile di query per te più naturale.Personalmente, utilizzerei la terza query perché rappresenta meglio ciò che voglio fare all'ottimizzatore, ovvero eseguire l'aggregazione il prima possibile.