UGISS User Group Italiano SQL Server
Prendendo l’ispirazione dal primo capitolo del libro Inside Microsoft SQL Server 2008: T-SQL Querying di Itzik Ben-Gan, in questo articolo parleremo delle fasi che entrano in gioco durante l’esecuzione logica di una query ed in particolare analizzeremo l’ordine con cui vengono eseguite.
In molti linguaggi di programmazione, il codice viene processato nell’ordine in cui è stato scritto. Potremmo pensare quindi, che in una query, la clausola SELECT sia la prima ad essere processata, ma in realtà non è così. In SQL, la prima clausola ad essere processata è la clausola FROM, mentre la SELECT verrà processata successivamente, moto più tardi.
Per descrivere le fasi del processo logico che viene invocato per risolvere una query, utilizzeremo una semplice SELECT. Per prima cosa generiamo, nel database di sistema tempdb, le tabelle dbo.customers e dbo.orderheader:
use [tempdb];
go
if (object_id(‘dbo.orderheader’, ‘U’) is not null)
drop table dbo.orderheader;
go
if (object_id(‘dbo.customers’, ‘U’) is not null)
drop table dbo.customers;
go
— create table dbo.customers
create table dbo.customers
(
customerid varchar(20) not null primary key,
city varchar(20) not null
);
go
— create table dbo.orderheader
create table dbo.orderheader
(
orderid int not null primary key,
orderdate datetime default getdate() not null,
ordernumber as (replace(str(orderid) , ‘ ‘, ‘0’)),
customerid varchar(20) null references customers(customerid)
);
Inseriamo alcuni dati di prova:
— Inserimento dati di prova
insert into dbo.customers(customerid, city) values(‘David’, ‘Amsterdam’);
insert into dbo.customers(customerid, city) values(‘Fernando’, ‘Amsterdam’);
insert into dbo.customers(customerid, city) values(‘Karlos’, ‘Amsterdam’);
insert into dbo.customers(customerid, city) values(‘Bob’, ‘Milano’);
insert into dbo.orderheader(orderid, customerid) values(1, ‘Fernando’);
insert into dbo.orderheader(orderid, customerid) values(2, ‘Fernando’);
insert into dbo.orderheader(orderid, customerid) values(3, ‘Karlos’);
insert into dbo.orderheader(orderid, customerid) values(4, ‘Karlos’);
insert into dbo.orderheader(orderid, customerid) values(5, ‘Karlos’);
insert into dbo.orderheader(orderid, customerid) values(6, ‘Bob’);
insert into dbo.orderheader(orderid, customerid) values(7, null);
Consultiamo i dati di prova inseriti:
select
*
from
dbo.customers;
select
*
from
dbo.orderheader;
Consideriamo la query seguente, che restituisce i clienti di Amsterdam con meno di tre ordini, (compresi i clienti senza ordini) gli ordini di ogni cliente verranno contati, la presentazione dei dati sarà ordinata in modo crescente per numero ordini.
c.customerid,
count(o.orderid) as numorders
from
dbo.customers as c
left outer join
dbo.orderheader as o on c.customerid = o.customerid
where
(c.city = ‘Amsterdam’)
group by
c.customerid
having
count(o.orderid) < 3
order by
numorders;
Esaminiamo la query e proviamo ad immaginare il corretto ordine di valutazione di ogni clausola… qual è l’ordine corretto ? Ogni commento di questo tipo:
precede una fase o una sotto-fase della query. Proviamo a sostituire il carattere “?” con il numero che ne rappresenta l’ordine di esecuzione, la clausola SELECT quando verrà eseguita ? Quale ordine assegniamo a: /*(?)*/SELECT
/*(?)*/SELECT
/*(? <select_list>)*/
C.customerid,
COUNT(O.orderid) AS numorders
/*(?)*/FROM /*(?)*/ dbo.customers AS C
LEFT OUTER JOIN dbo.orderheader AS O
/*(?)*/ ON C.customerid = O.customerid
/*(?)*/WHERE
(C.city = ‘Amsterdam’)
/*(?)*/GROUP BY
C.customerid
/*(?)*/HAVING
COUNT(O.orderid) < 3
/*(?)*/ORDER BY
numorders;
Le clausole della query precedente vengono valutate in questo ordine:
/*(5)*/SELECT
/*(5-1 <select_list>)*/
C.customerid,
COUNT(O.orderid) AS numorders
/*(1)*/FROM /*(1-Join1)*/ dbo.customers AS C
LEFT /*(1-Join3)*/ OUTER JOIN dbo.orderheader AS O
/*(1-Join2)*/ ON C.customerid = O.customerid
/*(2)*/WHERE
(C.city = ‘Amsterdam’)
/*(3)*/GROUP BY
C.customerid
/*(4)*/HAVING
COUNT(O.orderid) < 3
/*(6)*/ORDER BY
numorders;
Vediamo in dettaglio le diverse fasi.
/*(1)*/ FROM
La prima clausola specificata nella query, cioè la SELECT, è la quinta ad essere eseguita. La prima fase è rappresentata dalla valutazione della clausola FROM che nell’esempio avviene in tre sotto-fasi. La prima sotto-fase è rappresentata dall’esecuzione del prodotto cartesiano tra le due tabelle interessate nella JOIN, il risultato della sotto-fase 1-Join1 è rappresentato da una tabella virtuale che contiene ogni possibile coppia di righe per le tabelle dbo.customers e dbo.orderheader. Se la tabella a sinistra della JOIN (dbo.customers) contiene n righe e la tabella a destra della JOIN (dbo.orderheader) contiene m righe, la tabella virtuale risultante avrà (n x m) righe. Nell’esempio, la tabella virtuale VT1-Join1 avrà (4 x 7) = 28 righe.
/*(1-Join1)*/ FROM dbo.Customers AS C … JOIN dbo.Orders AS O
La tabella virtuale VT1-Join1 verrà utilizzata come input per la sotto-fase successiva 1-Join2 che è rappresentata dall’applicazione del filtro ON:
/*(1-Join2)*/ ON C.customerid = O.customerid
Il predicato nel filtro ON viene quindi applicato a tutte le righe contenute nella tabella virtuale restituita dalla precedente sotto-fase (VT1-Join1). Solo le righe che verificano il predicato entreranno a far parte della tabella virtuale VT1-Join2 restituita dalla sotto-fase 1-Join2. La prossima sotto-fase 1-Join3 è rappresentata dall’aggiunta delle “outer rows”:
/*(1-Join3)*/ dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
L’aggiunta delle outer rows avviene solo in presenza di una OUTER JOIN di tipo LEFT, RIGHT o FULL perché la presenza di una OUTER JOIN indica a SQL Server che una o entrambe le tabelle di input devono essere contrassegnate come tabelle da “preservare”. Contrassegnare una tabella come “da preservare” significa che vogliamo vengano restituite tutte le righe di questa tabella anche se non rispettano il predicato nel filtro ON. La sotto-fase 1-Join3 restituisce quindi tutte le righe della tabella virtuale VT1-Join2 a cui vengono aggiunte le righe delle tabelle “preservate”. Queste righe vengono chiamate “outer rows”. Nelle outer rows, il valore NULL viene assegnato agli attributi (valori delle colonne) per le tabelle non preservate. Il risultato di questa sotto-fase è rappresentato dalla tabella virtuale VT1-Join3, e non essendoci altre sotto-fasi questa tabella virtuale è la stessa che viene restituita dalla clausola FROM (fase 1).
/*(2)*/ WHERE
Il filtro WHERE viene applicato a tutte le righe della tabella virtuale restituita dalla fase precedente. La tabella virtuale VT2 restituita da questa fase contiene solo le righe che soddisfano la condizione di WHERE.
In questa fase i dati non sono aggregati, ed è per questo motivo che non si potrà scrivere:
In aggiunta, nella fase 2, non si potrà referenziare un alias di colonna creato nella <select_list> perchè la fase SELECT <select_list> non è ancora stata processata. Non si potrà quindi scrivere:
Dove docyear rappresenta un alias per:
Dopo aver esaminato l’ordine con cui vengono eseguiti i filtri ON e WHERE possiamo rispondere alla domanda: E’ meglio applicare un’espressione logica aggiunta nel filtro ON o nel filtro WHERE ? Semplificando ulteriormente la query iniziale e volendo limitare l’estrazione dei soli clienti di Amsterdam, è corretta la seguente query numero uno oppure la successiva numero due ?
— 1
select
*
from
dbo.Customers c left outer join dbo.Orders o
on c.customerid=o.customerid and (c.city = ‘Amsterdam’)
— 2
select
*
from
dbo.Customers c left outer join dbo.Orders o on c.customerid=o.customerid
where
(c.city = ‘Amsterdam’)
I rispettivi output:
Abbiamo verificato che il filtro ON (sotto-fase 1-Join2) è applicato prima di aggiungere le outer rows (sotto-fase 1-Join3) mentre il filtro WHERE è applicato successivamente (fase 2). L’eliminazione di righe, nella tabella preservata, per effetto del filtro ON non è definitiva perché la sotto-fase 1-Join3 (eseguita successivamente) aggiungerà le righe eliminate dalla tabella preservata come outer rows. L’eliminazione di righe nella clausola WHERE è invece definitiva!!
I clienti senza ordini vengono eliminati nella fase 1-Join2, e successivamente aggiunti nella fase 1-Join3 come "outer rows"
*
from
dbo.Customers c /*1-Join3*/ left outer join dbo.Orders o /*1-Join2*/ on c.customerid=o.customerid
Nell’esempio, si desidera interrogare solo i clienti di Amsterdam e in questo caso è più opportuno specificare il filtro nella clausola WHERE con:
Specificando il filtro nella clausola ON, i clienti non-di-Amsterdam verranno estratti perchè aggiunti nella fase 1-Join3.
Questa differenza logica tra la clausola ON e la clausola WHERE esiste solo quando viene utilizzata una OUTER JOIN, in presenza di una INNER JOIN la fase 1-Join3 non viene eseguita.
/*(3)*/ GROUP BY
La clausola GROUP BY viene applicata alle righe contenute nella tabella virtuale VT2, ogni combinazione univoca dei valori degli attributi che compaiono nel GROUP BY identifica un gruppo. I valori NULLs vengono considerati uguali, tutti i valori NULLs vengono raggruppati in un gruppo (come fossero valori conosciuti).
L’output di questa fase è rappresentato dalla tabella virtuale VT3.
/*(4)*/ HAVING
La clausola HAVING viene applicata alle righe contenute nella tabella virtuale VT3 restituita dalla fase precedente.
L’output di questa fase è rappresentato dalla tabella virtuale VT4.
/*(5)*/ SELECT
La prima clausola specificata nella query, la clausola SELECT, è la quinta ad essere eseguita 
eventuali alias definiti nella <select_list> non possono essere usati nelle fasi precedenti.
L’output di questa fase è rappresentato dalla tabella virtuale VT5.
/*(6)*/ORDER BY
L’ultima fase che viene eseguita per la query di esempio, è la fase di ordinamento (o presentazione) dei dati.
La clausola ORDER BY è l’unica in cui è possibile riutilizzare gli alias definiti nella <select_list>. Se dopo la clausola SELECT è specificato un DISTINCT, le espressioni nell’ORDER BY hanno accesso solo alla virtual table restituita nell’ultima fase (VT5). Se il DISTINCT non è specificato, le espressioni nell’ORDER BY possono accedere sia alla virtual table di input che alla virtual table di output della fase SELECT.
Conclusioni
La scrittura di codice T-SQL è caratterizzata da alcuni aspetti singolari, tra i quali la logica di ordinamento relativa alla valutazione degli elementi (clausole) presenti in una query. La conoscenza di questi aspetti permette di poter scrivere codice T-SQL efficiente ed efficace. Da questo articolo si evince chiaramente che avere tabelle in JOIN solo perché potrebbero servire in futuro non è mai una scelta saggia.
Pulizia del tempdb
/* Pulizia tempdb */
IF OBJECT_ID(‘dbo.Orderheader’) IS NOT NULL DROP TABLE dbo.Orderheader;
IF OBJECT_ID(‘dbo.Customers’) IS NOT NULL DROP TABLE dbo.Customers;
