Redgate Hub (Italiano)
Le funzioni della finestra T-SQL semplificano la scrittura di molte query e spesso forniscono prestazioni migliori anche rispetto alle tecniche precedenti. Ad esempio, utilizzare la funzione LAG è molto meglio che fare un self-join. Per ottenere prestazioni migliori nel complesso, tuttavia, è necessario comprendere il concetto di framing e come le funzioni della finestra si basano sull’ordinamento per fornire i risultati.
NOTA: vedi il mio nuovo articolo per sapere come i miglioramenti all’ottimizzatore nel 2019 influenzano le prestazioni!,
La clausola OVER e l’ordinamento
Ci sono due opzioni nella clausola OVER che possono causare l’ordinamento: PARTITION BY e ORDER BY. PARTITION BY è supportato da tutte le funzioni della finestra, ma è opzionale. L’ORDINE DA è richiesto per la maggior parte delle funzioni. A seconda di ciò che si sta tentando di realizzare, i dati verranno ordinati in base alla clausola OVER e questo potrebbe essere il collo di bottiglia delle prestazioni della query.
L’opzione ORDER BY nella clausola OVER è richiesta in modo che il motore di database possa allineare le righe, per così dire, per applicare la funzione nell’ordine corretto., Ad esempio, supponiamo di voler applicare la funzione ROW_NUMBER in ordine di SalesOrderID. I risultati avranno un aspetto diverso rispetto a se si desidera che la funzione venga applicata in ordine di TotalDue in ordine decrescente., Ecco l’esempio:
|
1
2
3
4
5
6
7
8
9
10
11
|
UTILIZZARE AdventureWorks2017; –o a seconda di quale versione hai
ANDARE
SELEZIONARE SalesOrderID
TotalDue,
ROW_NUMBER() OVER(ORDER BY SalesOrderID) COME RowNum
DALLE Vendite.,SalesOrderHeader;
SELEZIONA SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDINA PER TotalDue DESC) COME RowNum
DALLE Vendite.SalesOrderHeader;
|
Poiché la prima query utilizza la chiave cluster come opzione ORDER BY, non è necessario alcun ordinamento.
La seconda query ha una costosa operazione di ordinamento.,
La clausola ORDER BY nella clausola OVER non è collegata alla clausola ORDER BY aggiunta alla query complessiva che potrebbe essere molto diversa., Here is an example showing what happens if the two are different:
|
1
2
3
4
5
|
SELECT SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) AS RowNum
FROM Sales.,SalesOrderHeader
ORDER BY SalesOrderID;
|
La chiave di indice cluster è SalesOrderID, ma le righe primo luogo deve essere ordinato da TotalDue in ordine decrescente e poi di nuovo a SalesOrderID. Dai un’occhiata al piano di esecuzione:
La clausola PARTITION BY, supportata ma facoltativa, per tutte le funzioni della finestra T-SQL causa anche l’ordinamento. È simile, ma non esattamente come, alla clausola GROUP BY per le query aggregate., This example starts the row numbers over for each customer.
|
1
2
3
4
5
6
|
SELECT CustomerID,
SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)
AS RowNum
FROM Sales.,SalesOrderHeader;
|
Il piano di esecuzione mostra solo una operazione di ordinamento, una combinazione di CustomerID e SalesOrderID.
L’unico modo per superare l’impatto sulle prestazioni dell’ordinamento è creare un indice specifico per la clausola OVER. Nel suo libro Microsoft SQL Server 2012 ad alte prestazioni T-SQL utilizzando le funzioni della finestra, Itzik Ben-Gan raccomanda l’indice POC. POC sta per (P)ARTITION BY, (O)RDER BY e (c)overing., Raccomanda di aggiungere qualsiasi colonna utilizzata per il filtraggio prima della partizione e ORDINA PER colonne nella chiave. Quindi aggiungere eventuali colonne aggiuntive necessarie per creare un indice di copertura come colonne incluse. Proprio come qualsiasi altra cosa, dovrai testare per vedere come un tale indice influisce sulla tua query e sul carico di lavoro complessivo. Naturalmente, non è possibile aggiungere un indice per ogni query che si scrive, ma se le prestazioni di una particolare query che utilizza una funzione di finestra è importante, è possibile provare questo consiglio.,
Here is an index that will improve the previous query:
|
1
2
3
|
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(Idcliente, SalesOrderID)
SONO (TotalDue);
|
Quando si riesegue la query, l’operazione di ordinamento è ormai andato dal piano di esecuzione:
Inquadratura
A mio parere, l’inquadratura è più di un concetto difficile da capire quando si impara la conoscenza di T-SQL funzioni finestra. Per ulteriori informazioni sulla sintassi vedere introduzione alle funzioni della finestra T-SQL., L’inquadratura è richiesto per i seguenti:
- Finestra aggregati con l’ORDINE, utilizzato per l’esecuzione di totali o medie mobili, per esempio
- FIRST_VALUE
- LAST_VALUE
per Fortuna, l’inquadratura non è necessaria la maggior parte del tempo, ma, purtroppo, è facile ignorare il telaio e utilizzare l’impostazione predefinita. Il frame predefinito è sempre COMPRESO TRA LA RIGA PRECEDENTE E QUELLA CORRENTE ILLIMITATA. Mentre si ottengono i risultati corretti fino a quando l’opzione ORDER BY è costituito da una colonna unica o un insieme di colonne, si vedrà un successo di prestazioni.,id=”4b64f77d3b”>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SET STATISTICS IO SU;
ANDARE
SELECT Idcliente,
SalesOrderID
TotalDue,
SUM(TotalDue) OVER(PARTIZIONE DA CustomerID ORDINE SalesOrderID)
COME RunningTotal
DALLE Vendite.,SalesOrderHeader;
SELEZIONA CustomerID,
SalesOrderID,
TotalDue,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDINA PER SalesOrderID
RIGHE TRA RIGA PRECEDENTE E CORRENTE ILLIMITATA)
COME RunningTotal
DA Vendite.SalesOrderHeader;
|
I risultati sono gli stessi, ma le prestazioni sono molto diverse. Sfortunatamente, il piano di esecuzione non ti dice la verità in questo caso., Si segnala che ogni query ha preso il 50% delle risorse:
Se si esaminano le statistiche IO valori, si vedrà la differenza:
l’Utilizzo di frame corretto è ancora più importante se il vostro ORDINE di opzione non è unico o se si utilizza LAST_VALUE. In questo esempio, la colonna ORDER BY è OrderDate, ma alcuni clienti hanno effettuato più di un ordine in una determinata data. Quando non si specifica il frame o si utilizza l’INTERVALLO, la funzione considera le date corrispondenti come parte della stessa finestra.,”>
La ragione della discrepanza è che la GAMMA vede il logico dei dati, mentre le RIGHE vede posizionalmente. Ci sono due soluzioni per questo problema. Uno è assicurarsi che l’opzione ORDER BY sia unica. L’altra e più importante opzione è specificare sempre il frame in cui è supportato.
L’altro posto in cui l’inquadratura causa problemi logici è con LAST_VALUE., LAST_VALUE restituisce un’espressione dall’ultima riga del fotogramma. Poiché il frame predefinito (INTERVALLO TRA RIGA PRECEDENTE E RIGA CORRENTE ILLIMITATA) sale solo alla riga corrente, l’ultima riga del frame è la riga in cui viene eseguito il calcolo.,
Finestra Aggregati
Una delle più comodo funzione di T-SQL finestra di funzioni è la possibilità di aggiungere un’espressione di aggregazione per un non-query di aggregazione. Sfortunatamente, questo può spesso funzionare male. Per vedere il problema, è necessario guardare le statistiche IO risultati dove vedrete un gran numero di letture logiche., Il mio consiglio quando è necessario restituire valori a diverse granularità all’interno della stessa query per un numero elevato di righe è di utilizzare una delle tecniche precedenti, come un’espressione di tabella comune (CTE), una tabella temporanea o anche una variabile. Se è possibile pre-aggregare prima di utilizzare l’aggregato della finestra, questa è un’altra opzione.,ows la differenza tra la finestra di aggregazione e un’altra tecnica:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SELEZIONARE SalesOrderID
TotalDue,
SUM(TotalDue) (A) COME OverallTotal
DALLE Vendite.,SalesOrderHeader
DOVE YEAR(OrderDate) =2013;
DICHIARARE @OverallTotal MONEY;
SELEZIONARE @OverallTotal = SUM(TotalDue)
DALLE Vendite.SalesOrderHeader
DOVE YEAR(OrderDate) = 2013;
SELEZIONARE SalesOrderID,
TotalDue,
@OverallTotal COME OverallTotal
DALLE Vendite.,SalesOrderHeader AS SOH
WHERE YEAR(OrderDate) = 2013;
|
La prima query esegue la scansione della tabella solo una volta, ma ha 28.823 letture logiche in un piano di lavoro. Il secondo metodo esegue la scansione della tabella due volte, ma non ha bisogno del worktable.
Il prossimo esempio utilizza un aggregato di Windows applicato a un’espressione aggregata:
Quando si utilizzano le funzioni finestra in una query aggregata, l’espressione deve seguire le stesse regole delle clausole SELECT e ORDER BY., In questo caso, la funzione finestra viene applicata a SUM(TotalDue). Sembra un aggregato nidificato, ma è in realtà una funzione di finestra applicata a un’espressione aggregata.
Poiché i dati sono stati aggregati prima dell’applicazione della funzione window, le prestazioni sono buone:
C’è un’altra cosa interessante da sapere sull’utilizzo degli aggregati di finestre. Se si utilizzano più espressioni che utilizzano la corrispondenza RISPETTO alle definizioni delle clausole, non verrà visualizzato un ulteriore degrado delle prestazioni.
Il mio consiglio è di usare questa funzionalità con cautela., È abbastanza utile ma non scala così bene.
Confronti delle prestazioni
Gli esempi presentati finora hanno utilizzato le piccole vendite.SalesOrderHeader tabella da AdventureWorks e rivisto i piani di esecuzione e letture logiche. Nella vita reale, i tuoi clienti non si preoccuperanno del piano di esecuzione o delle letture logiche; si preoccuperanno della velocità con cui vengono eseguite le query. Per vedere meglio la differenza nei tempi di esecuzione, ho usato lo script Thinking Big (Adventure) di Adam Machanic con un tocco.
Lo script crea una tabella chiamata bigTransactionHistory contenente oltre 30 milioni di righe., Dopo aver eseguito lo script di Adam, ho creato altre due copie del suo tavolo, con rispettivamente 15 e 7,5 milioni di righe. Ho anche attivato la proprietà Scarta risultati dopo l’esecuzione nell’editor di query in modo che la compilazione della griglia non influisse sui tempi di esecuzione. Ho eseguito ogni test tre volte e cancellato la cache del buffer prima di ogni esecuzione.,
I can’t say enough about how important it is to use the frame when it’s supported., Per vedere la differenza, ho eseguito un test per calcolare i totali correnti utilizzando quattro metodi:
- Soluzione cursore
- Sottoquery correlata
- Funzione finestra con frame predefinito
- Funzione finestra con RIGHE
Ho eseguito il test sulle tre nuove tabelle. Ecco i risultati in un formato grafico:
Quando si esegue con il frame di RIGHE, la tabella di 7,5 milioni di righe ha richiesto meno di un secondo per essere eseguita sul sistema che stavo usando durante l’esecuzione del test. Il tavolo da 30 milioni di righe ha richiesto circa un minuto per essere eseguito., id=”4b64f77d3b”>
|
1
2
3
4
5
|
SELECT ProductID, SUM(ActualCost) OVER(PARTITION BY ProductID
ORDER BY TransactionDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM bigTransactionHistory;
|
I also performed a test to see how window aggregates performed compared to traditional techniques., In questo caso, ho usato solo la tabella da 30 milioni di righe, ma ho eseguito uno, due o tre calcoli usando la stessa granularità e, quindi, la stessa clausola OVER. Ho confrontato le prestazioni aggregate della finestra con un CTE e una sottoquery correlata.
L’aggregato della finestra ha eseguito il peggio, circa 1,75 minuti in ciascun caso. Il CTE ha eseguito al meglio aumentando il numero di calcoli poiché la tabella è stata appena toccata una volta per tutti e tre., La sottoquery correlata ha funzionato peggio quando si aumenta il numero di calcoli poiché ogni calcolo doveva essere eseguito separatamente e ha toccato la tabella per un totale di quattro volte.,v>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
WITH Calcs AS (
SELECT ProductID,
AVG(ActualCost) AS AvgCost,
MIN(ActualCost) AS MinCost,
MAX(ActualCost) AS MaxCost
FROM bigTransactionHistory
GROUP BY ProductID)
SELECT O.,ProductID,
ActualCost,
AvgCost,
MinCost,
MaxCost
DA bigTransactionHistory COME O
UNIRE Calcs SU O. ProductID = Calcs.ProductID;
|
Conclusione
Le funzioni della finestra T-SQL sono state promosse come ottime per le prestazioni. A mio parere, rendono più facili le query di scrittura, ma è necessario capirle bene per ottenere buone prestazioni. L’indicizzazione può fare la differenza, ma non è possibile creare un indice per ogni query scritta., Framing può non essere facile da capire, ma è così importante se avete bisogno di scalare fino a grandi tabelle.