SQL Server: è possibile che un Order By migliori le performace?

In questo articolo vi racconto un estratto del talk “Ordering Woes” al SqlBits 2019 di  Itzik Ben-Gan. Piccolo spoiler: vedremo una casistica particolare dove l’aggiunta di un Order By migliora le performance di una query, contro ogni pronostico aggiungerei. Vi consiglio di leggere questo articolo, è sicuramente molto utile a fine didattico anche perché la query proposta non è affatto artificiosa, ci dà idea inoltre del talento di Ben-Gan nel tuning delle performance di una query SQL. Tuttavia ci deve essere ben chiaro che nel restante 99,9% dei casi l’aggiunta di un ordinamento non necessario ci porterà a importanti peggioramenti delle performance.

Costruiamo il caso d’esempio

Creiamo nel nostro Database d’esempio una tabella Ordini con due colonne di circa diecimila righe. La prima avrà la proprietà Identity e sarà chiave primaria. Popoliamo inizialmente la tabella costruendo un progressivo anche per la colonna IdCliente per circa 10000 righe.

CREATE TABLE Ordini(
  IdOrdine INT IDENTITY(1,1) PRIMARY KEY,
  IdCliente INT)
INSERT INTO Ordini(IdCliente)
SELECT TOP 10000
  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
                               AS IdCliente
FROM sys.objects AS a
CROSS JOIN sys.objects AS b;

A questo punto facciamo in modo da avere per ogni cliente quattro ordini. Per far questo lanciamo due volte questa query

INSERT INTO Ordini(IdCliente)
SELECT IdCliente
FROM   Ordini;

Verifichiamo il contenuto della tabella eseguendo una semplice

SELECT * 
FROM   Ordini
WHERE  IdCliente = 1

Se abbiamo seguito alla lettera le istruzioni avremo quattro righe. Creiamo ora un indice non cluster sulla colonna IdCliente.

CREATE NONCLUSTERED INDEX IX_IdCliente
ON Ordini(IdCliente)

Nel prossimo paragrafo analizzeremo la query per estrarre i due ordini con Id maggiore per ogni cliente

Video Corso Data Analysis completo

Scriviamo la query e miglioriamo le performance

Possiamo risolvere l’esercizio utilizzando le window function.

WITH CTE AS 
 (SELECT *,
    RANK() OVER(PARTITION BY IdCliente
                ORDER BY IdOrdine DESC) AS Rango 
  FROM Ordini)
SELECT *
FROM   CTE 
WHERE  Rango <= 2;

Analizzando il piano d’esecuzione notiamo:

  • un costosissimo operatore Sort
  • il mancato utilizzo dell’indice sulla colonna IdCliente
piano d'esecuzione senza order by

Riscriviamo la query aggiungendo in fondo l’ORDER BY descrescente per IdCliente. Solo se l’ordinamento è coerente con quello all’interno della Window Function, SQL Server sceglierà un piano d’esecuzione che utilizzi l’indice sulla colonna IdCliente, con un importante miglioramento delle performance per la prima parte della query.

WITH CTE AS 
 (SELECT *,
    RANK() OVER(PARTITION BY IdCliente
                ORDER BY IdOrdine DESC) AS Rango 
  FROM Ordini)
SELECT *
FROM   CTE 
WHERE  Rango <= 2
ORDER BY IdCliente DESC;

Vediamo infatti che sparisce il costosissimo operatore Sort.

piano d'esecuzione con order by

Continua a imparare

Torna in alto
Torna su