Performance su Query, SQL Server 2016

il 25 settembre 2017 alle 12:07

294 messaggi dal 14 novembre 2001

Ciao, mi trovo di fronte ad un calo di performance che non riesco a spiegarmi. Posto qui le due Query sperando siano chiare

La prima Query è veloce, circa un secondo (i record totali estratti, colonna ContaRighe, sarebbero circa 800.000, la somma dei KG invece, colonna Totale_KG, sui 300.000)

SELECT ID_AnagraficaRivendita, NumeroRivendita, NomeComune, DataFlusso, CodiceProdottoAAMS, KG
FROM
(SELECT SUM(KG) OVER () AS Totale_KG, COUNT(*) OVER () AS ContaRighe, dbo.Flussi_Rivendite.ID_AnagraficaRivendita, dbo.Flussi_Rivendite.DataFlusso, dbo.Flussi_Rivendite.CodiceProdottoAAMS, dbo.Flussi_Rivendite.KG, dbo.AnagraficaRivendite.NumeroRivendita, dbo.AnagraficaComuni.NomeComune
FROM dbo.Flussi_Rivendite INNER JOIN dbo.AnagraficaRivendite ON dbo.Flussi_Rivendite.ID_AnagraficaRivendita = dbo.AnagraficaRivendite.ID_AnagraficaRivendita INNER JOIN
dbo.AnagraficaComuni ON dbo.AnagraficaRivendite.CodiceIstat_Form_Num = dbo.AnagraficaComuni.CodiceIstat_Form_Num WHERE dbo.Flussi_Rivendite.DataFlusso BETWEEN 20160101 AND 20170925
) AS Tbl_1
ORDER BY NomeComune, NumeroRivendita, CodiceProdottoAAMS, DataFlusso OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY

Questa seconda Query è la stessa di quella sopra, ma nella prima SELECT "prendo" anche i risultati ContaRighe e Totale_KG... Pensavo ciò non dovesse influire sulle prestazioni, invece i tempi arrivano a 5-7 secondi :(

SELECT ContaRighe, Totale_KG, ID_AnagraficaRivendita, NumeroRivendita, NomeComune, DataFlusso, CodiceProdottoAAMS, KG
FROM
(SELECT SUM(KG) OVER () AS Totale_KG, COUNT(*) OVER () AS ContaRighe, dbo.Flussi_Rivendite.ID_AnagraficaRivendita, dbo.Flussi_Rivendite.DataFlusso, dbo.Flussi_Rivendite.CodiceProdottoAAMS, 
dbo.Flussi_Rivendite.KG, dbo.AnagraficaRivendite.NumeroRivendita, dbo.AnagraficaComuni.NomeComune
FROM dbo.Flussi_Rivendite INNER JOIN dbo.AnagraficaRivendite ON dbo.Flussi_Rivendite.ID_AnagraficaRivendita = dbo.AnagraficaRivendite.ID_AnagraficaRivendita INNER JOIN
dbo.AnagraficaComuni ON dbo.AnagraficaRivendite.CodiceIstat_Form_Num = dbo.AnagraficaComuni.CodiceIstat_Form_Num
WHERE dbo.Flussi_Rivendite.DataFlusso BETWEEN 20160101 AND 20170925
) AS Tbl_1
ORDER BY NomeComune, NumeroRivendita, CodiceProdottoAAMS, DataFlusso OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY

Qualcuno saprebbe darmi un aiuto ? Anche semplicemente come concetto, come mai due colonne già calcolate dentro la Query Tbl_1, se cerco di riprenderle mi rallentano tutto ? E come mai invece le altre colonne non danno questo problema ?

Modificato da maurodii il 25 settembre 2017 12.12 -

Modificato da maurodii il 25 settembre 2017 12.13 -

Campo Testaccio, c'hai tanta gloria...

Rispondi Quoting

andrea.tosato86

il 25 settembre 2017 alle 13:10

244 messaggi dal 22 gennaio 2017
Contributi

Ciao,
Se la colonna è di tipo PERSISTED, il valore verrà memorizzato, altrimenti verrà calcolato ad ogni esecuzione della query.
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql

Rispondi Quoting

Andrea Montanari

il 25 settembre 2017 alle 14:09

1.976 messaggi dal 27 luglio 2005
Contributi

salve,
sto cercando di capire se in effetti l'Optimizer e' cosi' intelligente da escludere dall'inner query il processamento degli argomenti NON inclusi nella SELECT list esterna... e cosi' in effeti sembra... utilizzando SQL Sentry Plan Explorer (la versione base e' FREE, https://www.sentryone.com/plan-explorer), si evince che parrebbe di si...

su una mia tabella di test ho eseguito

SELECT innerTB.pax, innerTB.pens
  FROM (
SELECT SUM(a.ImportoPensione) pens, sum(a.PaxA + a.PaxR) pax
  FROM dbo.AGE_CONTI a
  ) AS innerTB;

SELECT innerTB.pax
  FROM (
SELECT SUM(a.ImportoPensione) pens, sum(a.PaxA + a.PaxR) pax
  FROM dbo.AGE_CONTI a
  ) AS innerTB;

2 batch leggeri che comportano in effetti una "trivial optimization" per uno scan di 6000 righe...
il piano generato comporta un clustered index scan (97%), un compute scalar (0%), uno stream aggregate (3%), un successivo compute scalar (0%) ed il finale select(0%)
la precentuale indica il costo percentuale sul totale del batch.
al di la' ovviamente del medesimo costo in termini di righe man mano passati di step in step, si evince gia' da SSMS un minor costo di step in step in termini di bytes passati, qui mi e' venuto il dubbio... il pacchetto iniziale avrebbe dovuto essere del medesimo peso, nel mio caso 18 B per l'estimated row size del clustered index scan, mentre nel caso di omissione della colonna esterna [innerTB].[pens], l'estimated row size indica 9B, quindi in effetti sembra che l'aggregazione di SUM(a.ImportoPensione) pens non venga eseguita...

passando a SQL Sentry Plan Explorer, in effetti, la proiezione completa inclusiva di [pens] riporta un clustered index scan come segue:

<RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="6175" EstimateIO="0.119421" EstimateCPU="0.0069495" AvgRowSize="18" EstimatedTotalSubtreeCost="0.126371" TableCardinality="6175" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
  <OutputList>
    <ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="PaxA" />
    <ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="PaxR" />
    <ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="ImportoPensione" />
  </OutputList>
  <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="6175" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="160" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6175" ActualEndOfScans="1" ActualExecutions="1" />
  </RunTimeInformation>
  <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
    <DefinedValues>
      <DefinedValue>
        <ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="PaxA" />
      </DefinedValue>
      <DefinedValue>
        <ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="PaxR" />
      </DefinedValue>
      <DefinedValue>
        <ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="ImportoPensione" />
      </DefinedValue>
    </DefinedValues>
    <Object Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Index="[IX_age_contiArr]" Alias="[a]" IndexKind="Clustered" Storage="RowStore" />
  </IndexScan>
</RelOp>

mentre la medesima operazione SENZA la proiezione finale di [pens] riporta:

<RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="6175" EstimateIO="0.119421" EstimateCPU="0.0069495" AvgRowSize="9" EstimatedTotalSubtreeCost="0.126371" TableCardinality="6175" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
  <OutputList>
    <ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="PaxA" />
    <ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="PaxR" />
  </OutputList>
  <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="6175" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="160" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6175" ActualEndOfScans="1" ActualExecutions="1" />
  </RunTimeInformation>
  <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
    <DefinedValues>
      <DefinedValue>
        <ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="PaxA" />
      </DefinedValue>
      <DefinedValue>
        <ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="PaxR" />
      </DefinedValue>
    </DefinedValues>
    <Object Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Index="[IX_age_contiArr]" Alias="[a]" IndexKind="Clustered" Storage="RowStore" />
  </IndexScan>
</RelOp>

dove NON appare in effetti
<ColumnReference Database="[VbHotel]" Schema="[dbo]" Table="[AGE_CONTI]" Alias="[a]" Column="ImportoPensione" />
che quindi gia' a livello di sub query NON viene valutato, scartato dall'Optimzer in quanto NON referenziato nella outer SELECT list...

grazie, non me lo ricordavo... anche oggi ho imparato qualche cosa toccandola con mano :)
saluti

Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php

Rispondi Quoting

maurodii

il 25 settembre 2017 alle 14:33

294 messaggi dal 14 novembre 2001

Grazie per le risposte, ho provato a leggere la documentazione sullee colonne Persisted, ma non ci ho capito molto :(

Soprattutto nel case di ContaRighe, che deriva da COUNT(*) OVER () AS ContaRighe, cosa dovrei rendere Persisted? La vedo dura, forse mi conviene cambiare strada e trovare un'altra soluzione

EDIT:
Una soluzione che non mi piace e nemmeno avrei mai detto che funzionasse: in questo modo riesco a ricavare i KG totali molto più velocemente rispetto alla seconda query del mio primo post. Ma come mi sembra strano! In questo modo "SUM(KG) OVER () AS Totale_KG" non sarebbe più necessario

SELECT
(SELECT SUM(KG) AS V
FROM dbo.Flussi_Rivendite AS FlussiB WHERE
DataFlusso BETWEEN 20160101 AND 20170925) AS VV,
ID_AnagraficaRivendita, NumeroRivendita, NomeComune, DataFlusso, CodiceProdottoAAMS, KG
FROM
(SELECT SUM(KG) OVER () AS Totale_KG, dbo.Flussi_Rivendite.ID_AnagraficaRivendita, dbo.Flussi_Rivendite.DataFlusso, dbo.Flussi_Rivendite.CodiceProdottoAAMS, dbo.Flussi_Rivendite.KG, dbo.AnagraficaRivendite.NumeroRivendita, dbo.AnagraficaComuni.NomeComune
FROM dbo.Flussi_Rivendite INNER JOIN dbo.AnagraficaRivendite ON dbo.Flussi_Rivendite.ID_AnagraficaRivendita = dbo.AnagraficaRivendite.ID_AnagraficaRivendita INNER JOIN
dbo.AnagraficaComuni ON dbo.AnagraficaRivendite.CodiceIstat_Form_Num = dbo.AnagraficaComuni.CodiceIstat_Form_Num WHERE dbo.Flussi_Rivendite.DataFlusso BETWEEN 20160101 AND 20170925
) AS Tbl_1
ORDER BY NomeComune, NumeroRivendita, CodiceProdottoAAMS, DataFlusso OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY

Modificato da maurodii il 25 settembre 2017 15.32 -

Campo Testaccio, c'hai tanta gloria...

Rispondi Quoting

maurodii

il 26 settembre 2017 alle 13:57

294 messaggi dal 14 novembre 2001

Alla fine sto optando per questa soluzione, mi dimezza i tempi.

WITH Data_CTE
AS
(SELECT dbo.Flussi_Rivendite.ID_AnagraficaRivendita,
NumeroRivendita, NomeComune, DataFlusso,
dbo.Flussi_Rivendite.CodiceProdottoAAMS, KG
FROM
dbo.Flussi_Rivendite INNER JOIN
dbo.AnagraficaRivendite ON dbo.Flussi_Rivendite.ID_AnagraficaRivendita = dbo.AnagraficaRivendite.ID_AnagraficaRivendita INNER JOIN
dbo.AnagraficaComuni ON dbo.AnagraficaRivendite.CodiceIstat_Form_Num = dbo.AnagraficaComuni.CodiceIstat_Form_Num
WHERE dbo.Flussi_Rivendite.DataFlusso BETWEEN 20160101 AND 20170926),
Count_CTE AS
(SELECT COUNT(*) AS TotalRows, SUM(KG) AS Vendita FROM Data_CTE)

SELECT *
FROM Data_CTE
CROSS JOIN Count_CTE
ORDER BY NomeComune, NumeroRivendita, CodiceProdottoAAMS, DataFlusso
OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY;

ps: grazie dei suggerimenti

Campo Testaccio, c'hai tanta gloria...

Rispondi Quoting

Torna al forum | Feed RSS

ASPItalia.com non è responsabile per il contenuto dei messaggi presenti su questo servizio, non avendo nessun controllo sui messaggi postati nei propri forum, che rappresentano l'espressione del pensiero degli autori.

Community

Corsi e consulenza

In primo piano

.NET Conference Italia 2023 - Milano e Online

Le novità di .NET 7 e C# 11

Le novità in ASP.NET Core e Blazor con .NET 7

Le novità di Entity Framework (Core) 7

I più letti di oggi

In evidenza

Misc

Utilizziamo i cookie per analisi, contenuti personalizzati e pubblicità.

.NET Conference Italia 2023 - Milano e Online

Le novità di .NET 7 e C# 11

Le novità in ASP.NET Core e Blazor con .NET 7

Le novità di Entity Framework (Core) 7