La concatenazione di due o più set di dati è più comunemente espressa in T-SQL utilizzando la clausolaUNION ALL. Dato che SQL Server optimizer può spesso riordinare cose come join e aggregati per migliorare le prestazioni, è abbastanza ragionevole aspettarsi che SQL Server consideri anche il riordino degli input di concatenazione, in cui ciò fornirebbe un vantaggio. Ad esempio, l’ottimizzatore potrebbe considerare i vantaggi della riscrittura A UNION ALL B come B UNION ALL A.

Infatti, l’ottimizzatore di SQL Server non lo fa. Più precisamente, c’era un supporto limitato per il riordino degli input di concatenazione nelle versioni di SQL Server fino a 2008 R2, ma questo è stato rimosso in SQL Server 2012 e da allora non è riemerso.

SQL Server 2008 R2

Intuitivamente, l’ordine degli input di concatenazione è importante solo se esiste un obiettivo di riga. Per impostazione predefinita, SQL Server ottimizza i piani di esecuzione in base al fatto che tutte le righe qualificate verranno restituite al client. Quando un obiettivo di riga è attivo, l’ottimizzatore tenta di trovare un piano di esecuzione che produrrà rapidamente le prime righe.

Gli obiettivi di riga possono essere impostati in diversi modi, ad esempio utilizzandoTOP, unFAST n suggerimento di query, o utilizzandoEXISTS (che per sua natura deve trovare al massimo una riga). Dove non esiste un obiettivo di riga (cioè il client richiede tutte le righe), generalmente non importa in quale ordine vengono letti gli input di concatenazione: ogni input verrà completamente elaborato alla fine in ogni caso.

Il supporto limitato nelle versioni fino a SQL Server 2008 R2 si applica dove c’è un obiettivo di esattamente una riga. In questa specifica circostanza, SQL Server riordina gli input di concatenazione in base al costo previsto.

Questo non viene fatto durante l’ottimizzazione basata sui costi (come ci si potrebbe aspettare), ma piuttosto come una riscrittura post-ottimizzazione dell’ultimo minuto del normale output dell’ottimizzatore. Questa disposizione ha il vantaggio di non aumentare lo spazio di ricerca del piano basato sui costi (potenzialmente un’alternativa per ogni possibile riordino), pur producendo un piano ottimizzato per restituire rapidamente la prima riga.

Esempi

I seguenti esempi utilizzano due tabelle con contenuti identici: un milione di righe di numeri interi da uno a un milione. Una tabella è un heap senza indici non cluster; l’altra ha un indice cluster univoco:

CREATE TABLE dbo.Expensive( Val bigint NOT NULL); CREATE TABLE dbo.Cheap( Val bigint NOT NULL, CONSTRAINT UNIQUE CLUSTERED (Val));GOINSERT dbo.Cheap WITH (TABLOCKX) (Val)SELECT TOP (1000000) Val = ROW_NUMBER() OVER (ORDER BY SV1.number)FROM master.dbo.spt_values AS SV1CROSS JOIN master.dbo.spt_values AS SV2ORDER BY ValOPTION (MAXDOP 1);GOINSERT dbo.Expensive WITH (TABLOCKX) (Val)SELECT C.ValFROM dbo.Cheap AS COPTION (MAXDOP 1);

Nessuna Riga Obiettivo

La seguente query è per le stesse righe di ciascuna tabella e restituisce la concatenazione dei due insiemi:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005;

Il piano di esecuzione generato dalla query optimizer è:

L’avviso sull’operatore rootSELECT ci sta avvisando dell’ovvio indice mancante sulla tabella heap. L’avviso sull’operatore di scansione della tabella viene aggiunto da Sentry One Plan Explorer. Sta attirando la nostra attenzione sul costo di I/O del predicato residuo nascosto all’interno della scansione.

L’ordine degli input per la Concatenazione non ha importanza qui, perché non abbiamo impostato un obiettivo di riga. Entrambi gli input verranno letti completamente per restituire tutte le righe dei risultati. Di interesse (anche se questo non è garantito) si noti che l’ordine degli input segue l’ordine testuale della query originale. Osserva anche che l’ordine delle righe dei risultati finali non è specificato, poiché non abbiamo usato una clausola ORDER BY di primo livello. Assumeremo che è deliberato e l’ordine finale è irrilevante per il compito a portata di mano.

Se invertiamo l’ordine scritto delle tabelle nella query in questo modo:

SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 UNION ALL SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005;

Il piano di esecuzione segue il cambiamento, l’accesso al cluster la prima tabella (di nuovo, questo non è garantito):

Entrambe le query può aspettare di avere le stesse caratteristiche di prestazioni, come si eseguono le stesse operazioni, solo in un ordine diverso.

Con un obiettivo di riga

Chiaramente, la mancanza di indicizzazione sulla tabella heap normalmente renderà più costosa la ricerca di righe specifiche, rispetto alla stessa operazione sulla tabella cluster. Se chiediamo all’ottimizzatore un piano che restituisca rapidamente la prima riga, ci aspetteremmo che SQL Server riordini gli input di concatenazione in modo che la tabella cluster economica venga consultata per prima.

Utilizzando la query che menziona prima la tabella heap e utilizzando un suggerimento di query 1 VELOCE per specificare l’obiettivo della riga:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005OPTION (FAST 1);

Il piano di esecuzione stimato prodotta in un’istanza di SQL Server 2008 R2 è:

si Noti che la concatenazione ingressi sono stati riordinati per ridurre il costo stimato di rientro della prima riga. Si noti inoltre che l’indice mancante e gli avvisi I/O residui sono scomparsi. Nessuno dei due problemi è di conseguenza con questa forma del piano quando l’obiettivo è quello di restituire una singola riga il più rapidamente possibile.

La stessa query eseguita su SQL Server 2016 (utilizzando entrambi i modelli di stima della cardinalità) è:

SQL Server 2016 non ha riordinato gli input di concatenazione. L’avviso I/O di Plan Explorer è tornato, ma purtroppo l’ottimizzatore non ha prodotto un avviso di indice mancante questa volta (sebbene sia rilevante).

Riordino generale

Come accennato, la riscrittura post-ottimizzazione che riordina gli input di concatenazione è efficace solo per:

• SQL Server 2008 R2 e versioni precedenti
• Una riga obiettivo di uno

Se siamo davvero vogliono solo una riga restituita, piuttosto che un piano ottimizzati per restituire la prima riga in fretta (ma che in ultima analisi ancora restituire tutte le righe), si può usare un TOP clausola con una tabella derivata o espressione di tabella comune):

SELECT TOP (1) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UA;

In SQL Server 2008 R2 o in precedenza, questo produce ottimale riordinate-piano di inserimento:

In SQL Server 2012, 2014, 2016 post-ottimizzazione di riordino si verifica:

Se si desidera più di una riga restituita, per esempio con TOP (2), la riscrittura non verrà applicato sul SQL Server 2008 R2 anche se un FAST 1 suggerimento è utilizzato anche. In questa situazione, dobbiamo ricorrere a trucchi come usare TOP con una variabile e unOPTIMIZE FOR suggerimento:

DECLARE @TopRows bigint = 2; -- Number of rows actually needed SELECT TOP (@TopRows) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UAOPTION (OPTIMIZE FOR (@TopRows = 1)); -- Just a hint

l’hint per La query è sufficiente impostare una riga obiettivo di uno, mentre il runtime valore della variabile assicura il numero desiderato di righe (2) viene restituito.

Il piano di esecuzione effettivo su SQL Server 2008 R2 è:

Entrambe le righe restituite provengono dall’input di ricerca riordinato e la scansione della tabella non viene eseguita affatto. Plan Explorer mostra i conteggi delle righe in rosso perché la stima era per una riga (a causa del suggerimento) mentre due righe sono state rilevate in fase di esecuzione.

Senza UNION ALL

Questo problema non è limitato alle query scritte esplicitamente conUNION ALL. Altre costruzioni comeEXISTS eOR possono anche portare l’ottimizzatore a introdurre un operatore di concatenazione, che potrebbe soffrire della mancanza di riordino degli input. C’è stata una domanda recente su Database Administrators Stack Exchange con esattamente questo problema. Trasformare la query da quella domanda a utilizzare il nostro esempio tabelle:

SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 ) OR EXISTS ( SELECT 1 FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 ) THEN 1 ELSE 0 END;

Il piano di esecuzione di SQL Server 2016 ha la tabella heap il primo di ingresso:

In SQL Server 2008 R2 ordine degli ingressi è ottimizzato per riflettere la singola riga obiettivo di semi join:

il più piano ottimale, il mucchio di scansione non viene mai eseguito.

Soluzioni alternative

In alcuni casi, sarà evidente allo scrittore di query che uno degli input di concatenazione sarà sempre più economico da eseguire rispetto agli altri. Se ciò è vero, è abbastanza valido riscrivere la query in modo che gli input di concatenazione più economici appaiano per primi in ordine scritto. Ovviamente ciò significa che lo scrittore di query deve essere consapevole di questa limitazione dell’ottimizzatore e disposto a fare affidamento su comportamenti non documentati.

Un problema più difficile sorge quando il costo degli input di concatenazione varia con le circostanze, forse a seconda dei valori dei parametri. L’utilizzo diOPTION (RECOMPILE) non sarà di aiuto in SQL Server 2012 o versioni successive. Tale opzione può essere utile su SQL Server 2008 R2 o versioni precedenti, ma solo se viene soddisfatto anche il requisito dell’obiettivo a riga singola.

Se ci sono dubbi sull’affidamento sul comportamento osservato (input di concatenazione del piano di query che corrispondono all’ordine testuale della query) è possibile utilizzare una guida al piano per forzare la forma del piano. Dove diversi ordini di input sono ottimali per circostanze diverse, è possibile utilizzare più guide di piano, in cui le condizioni possono essere accuratamente codificate in anticipo. Questo non è certo l’ideale però.

Considerazioni finali

L’ottimizzatore di query SQL Server contiene infatti una regola di esplorazione basata sui costi,UNIAReorderInputs, che è in grado di generare variazioni dell’ordine di input di concatenazione ed esplorare alternative durante l’ottimizzazione basata sui costi (non come riscrittura post-ottimizzazione a colpo singolo).

Questa regola non è attualmente abilitata per uso generale. Per quanto posso dire, viene attivato solo quando è presente una guida al piano o un suggerimento USE PLAN. Ciò consente al motore di forzare correttamente un piano generato per una query qualificata per la riscrittura del riordino degli input, anche quando la query corrente non è qualificata.

La mia sensazione è che questa regola di esplorazione sia deliberatamente limitata a questo uso, perché le query che trarrebbero beneficio dal riordino degli input di concatenazione come parte dell’ottimizzazione basata sui costi non sono considerate sufficientemente comuni, o forse perché c’è la preoccupazione che lo sforzo extra non ripaghi. La mia opinione è che il riordino dell’input dell’operatore di concatenazione dovrebbe sempre essere esplorato quando un obiettivo di riga è in vigore.

È anche un peccato che la riscrittura post-ottimizzazione (più limitata) non sia efficace in SQL Server 2012 o versioni successive. Ciò potrebbe essere dovuto a un bug sottile, ma non sono riuscito a trovare nulla al riguardo nella documentazione, nella knowledge base o su Connect. Ho aggiunto un nuovo elemento di connessione qui.

Aggiornamento 9 agosto 2017: questo è ora risolto sotto trace flag 4199 per SQL Server 2014 e 2016, vedere KB 4023419:

FIX: La query con UNION ALL e un obiettivo di riga può essere eseguita più lentamente in SQL Server 2014 o versioni successive quando viene confrontata con SQL Server 2008 R2