maggio 2009 - Posts
Analizziamo ora una casistica simile a quella mostrata in un mio post precedente. Consideriamo un tipico problema che si risolve con l’OUTER JOIN, avendo a disposizione due tabelle (Person.Contact e Sales.ContactCreditCard) di AdventureWork in relazione tra loro: si vogliono conoscere tutti i contatti che non hanno una carta di credito. La query che risolve il problema sarà:
USE AdventureWorks;
SELECT
C.ContactID
, C.Title
, C.FirstName
, C.MiddleName
, C.LastName
, CC.CreditCardID
FROM
Person.Contact AS C
LEFT JOIN Sales.ContactCreditCard AS CC
ON C.ContactID = CC.ContactID
WHERE
CC.ContactID IS NULL ;
la quale restituisce 854 righe.
Se volessi spostare la condizione where nella clausula join, otterrei la seguente query:
SELECT
C.ContactID
, C.Title
, C.FirstName
, C.MiddleName
, C.LastName
, CC.CreditCardID
FROM
Person.Contact AS C
LEFT JOIN Sales.ContactCreditCard AS CC
ON C.ContactID = CC.ContactID
AND CC.ContactID IS NULL ;
che restituisce invece 19972 righe, e cioè TUTTI i contatti! Facendo riferimento al suddetto post, mi aspettavo di ottenere gli stessi risultati! Cosa succede?
Per comprendere «l’arcano» :) , bisogna conoscere come una query viene processata a livello logico dall’engine. Per un maggiore approfondimento rimando alla lettura del libro “Inside Microsoft SQL Server 2008: T-SQL Querying” (il primo capitolo dell’edizione precedente del libro lo trovate qui). Nel nostro caso, l’engine esegue i seguenti passi:
- Genera il prodotto cartesiano (cross join) delle tabelle C e CC; consideriamo il risultato di tale prodotto la tabella virtuale V1.
- Applica la condizione di join alle righe di V1 e le righe risultanti apparterranno alla tabella virtuale V2. Si osservi che, valendo la logica a tre valori (spiegata più in dettaglio qui), verranno filtrate le righe di V1 per cui la suddetta condizione restituisce TRUE, escludendo FALSE e UNKNOWN. Ora, la condizione di join è definita come: (C.ContactID = CC.ContactID AND CC.ContactID IS NULL). La prima uguaglianza restituisce tutte quelle righe in cui CC.ContactID è un valore non nullo, perché, se fosse nullo, la condizione C.ContactID = NULL verrebbe valutata a UNKNOWN e dunque le relative righe sarebbero scartate dal filtro. Allo stesso tempo, si richiede che (CC.ContractID IS NULL), ma le due espressioni non possono essere vere allo stesso tempo, dunque la nostra condizione di join restituirà un insieme di righe V2 vuoto.
Per verificare che tale condizione esclude tutte le righe, sia nel caso di C.ContactID numerico, sia sia nel caso di C.ContactID uguale a NULL, è sufficiente lanciare la seguente query:
SELECT
CASE WHEN (1 = 1 AND 1 IS NULL) THEN 'RIGA INCLUSA'
ELSE 'RIGA SCARTATA'
END AS ValoreNumerico,
CASE WHEN (NULL = NULL AND NULL IS NULL) THEN 'RIGA INCLUSA'
ELSE 'RIGA SCARTATA'
END AS ValoreNullo
per osservare i risultati predetti:
- Poiché si tratta di un outer join, l’engine aggiunge alle righe di V2 tutte le righe della tabella preserved (in questo caso C perché si tratta di un left join) che non match-ano con CC. Sia la tabella virtuale V3 il risultato di tale operazione. Poiché V2 è una tabella vuota, vuol dire che il matching al punto 2 ha restituito “nessun contatto”; dunque il “non matching” restituirà “tutti i contatti”. Dunque, V3 conterrà tutti i contatti.
Occhio dunque ad includere la condizione di WHERE in quella di JOIN quando si ha a che fare con un outer join!
Per chi volesse conoscere più da vicino questa nuova tecnologia che sarà disponibile su SQL Server 2008 R2, c’è una spiegazione dettagliata e altri link di riferimento su un post di Kirk Haselden, il Product Unit Manager della nuova tecnologia.
Trovate il post qui.
Se il vostro responsabile vi chiedesse di tirare fuori tutti gli ordini che l’azienda ha emesso tutti i fine mese di ogni anno fino ad oggi, come vi comportereste?
Devo ammettere che fino a qualche giorno fa ho utilizzato una tabella di appoggio di date, dalla quale per ogni mese tiravo fuori il massimo giorno. Oggi (meglio tardi che mai!) ho scoperto un trucchetto niente male!
Utilizziamo il “famigerato” database AdventureWork, in particolare la tabella Sales.SalesOrderHeader. Scrivendo la semplice query seguente, si ottiene quanto richiesto:
La data ‘19991231’ è una data di riferimento che può essere una qualunque data avente anno ‘aaaa’ e mese ‘mm’ a piacere, tali che la data ‘aaaamm31’ sia valida.
La funzione DATEDIFF(month, ‘aaamm31’, O.orderdate) restituisce il numero di mesi che intercorrono tra la data di riferimento e la data dell’ordine (può anche essere un numero negativo). Se poi si somma tale numero di mesi alla data di riferimento (quasi una operazione inversa), si ottiene una data che ha come anno e mese gli stessi della data dell’ordine, ma come giorno lo stesso della data di riferimento se questo, insieme ad anno e mese, forma una data valida; altrimenti viene preso il più grande giorno del mese in modo tale da formare una data valida.
Se si utilizzasse come data di riferimento la data ‘19991229’, si filtrerebbero tutti gli ordini emessi il giorno 29 di ogni mese (eccezion fatta per febbraio se non è bisestile nell’anno di emissione dell’ordine).
Dunque, se la data di riferimento è ‘19991231’, oppure ad esempio ‘29990831’, si ottengono gli ordini emessi l’ultimo giorno di ogni mese. Se invece prendiamo come data di riferimento, ad esempio, la data ‘20000101’, otteniamo gli ordini emessi il primo di ogni mese (il giorno 1 insieme a qualunque mese e anno forma sempre una data valida).
Alla luce di ciò, teniamo presente che possiamo usare, come data di riferimento, anche la data 0:
e quindi possiamo facilmente ottenere, ad esempio, le date di oggi e di ieri con l’azzeramento dell’ora, come segue:
Allo stesso modo possiamo ottenere alcune date (sempre con l’ora azzerata) “comode” per eventuali confronti:
E’ anche possibile lavorare con cifre decimali per gestire le “porzioni” dei giorni (in questo caso la “mezza giornata”):
Lo stesso risultato si sarebbe potuto ottenere usando, ad esempio, la data di riferimento '20010401 12:00:00'.
A questo punto non posso che augurare un buon DateTime a tutti voi! :)
Giorni fa mi sono ritrovato a dover creare una tabella con un campo destinato a contenere un valore numerico a due cifre decimali, il peso in kg dei prodotti di magazzino. Sono sorti dunque i seguenti dilemmi: devo usare un tipo di dato float o decimal? Quali differenze ci sono tra i due tipi di dato?
Innanzi tutto, mi sono documentato sui BOL ed ho trovato le differenze sostanziali:
- il float è un tipo di dato numerico approssimato, mentre il decimal è a scala e precisione fisse (dove per precisione s’intende il numero totale di cifre componenti il numero, comprese le cifre dopo la virgola; per scala s’intende il numero di cifre dopo la virgola)
- differenze nel range e nella occupazione:
| float(1..24) = float(24) (real) | (-3.40E+38..-1.18E- 38), 0, (1.18E-38..3.40E+38) | 4 byte |
| float(25..53) = float(53) (double precision) | (-1.79E+308..-2.23E-308), 0, (2.23E-308..1.79E+308) | 8 byte |
| decimal(p=1..9, s) | -10^p +1 .. 10^p - 1 | 5 byte |
| decimal(p=10..19, s) | -10^p +1 .. 10^p - 1 | 9 byte |
| decimal(p=20..28, s) | -10^p +1 .. 10^p - 1 | 13 byte |
| decimal(p=29..38, s) | -10^p +1 .. 10^p - 1 | 17 byte |
Essendo dunque il float un tipo di dato a virgola mobile, è evidente che è indicato per calcoli scientifici con molte cifre decimali, dove una approssimazione di queste ultime è ammessa. E’ altresì evidente che per calcoli finanziari, ad esempio, non sono ammesse tali approssimazioni e di qui il necessario utilizzo di un tipo di dato a scala e precisione fisse.
Nel mio caso, il numero più grande che avrei potuto rappresentare era del tipo xx.xxx.xxx,yy. Quindi, usando un tipo a precisione fissa, avrei dovuto utilizzare il decimal(10,2), con una occupazione di 9 byte, contro i 4 byte del float(24).
Pertanto mi sono chiesto se in effetti era necessario avere una precisione fissa per la casistica che dovevo risolvere: fornire una reportistica completa per il management aziendale sulle operazioni e le grandezze di magazzino. Confontandomi con i destinatari del lavoro, siamo giunti alla conclusione che una precisione “al capello” non era affatto necessaria, poiché la lettura del report avrebbe dovuto dare un’idea di massima delle grandezze che si andavano a rappresentare. Quindi con mio sommo gaudio :) mi sono sentito tranquillo nell’utilizzare il float(24) e dunque risparmiare ben 5 byte di spazio per ogni numero persistito!
Ma non sempre la ruota della fortuna gira dalla tua parte! Il database gestionale della stessa azienda tratta tutti i dati numerici come decimal(x,y) (l’età di una persona è un decimal(3,0)!! Ah, quanto avrei voluto vedere un bel tinyint!!). Nello stesso database ci sono, inoltre, parecchi campi numerici (sempre decimal, ovviamente) che contengono tanti zeri o valori interi! Certamente, in questi casi, lo spreco di byte è incommensurabile!! Dunque mi sono chiesto se fosse esistito un tipo di dato simile al varchar che avrebbe potuto risolvere il problema… Ho scoperto che dal Service Pack 2 di SQL Server 2005 (solo edizioni Enterprise, Developer ed Evaluation) è stato reso disponibile il formato di memorizzazione (e non un tipo di dato) vardecimal. Ho pensato dunque di aver trovato un escamotage al problema. Ma non appena ho aperto i BOL sull’argomento ho trovato questo:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ROW and PAGE compression instead. For more information, see Creating Compressed Tables and Indexes.
Cavoli! Per un momento avevo pensato di aver risolto il problema con un “barbatrucco” (termine preso in prestito da un famoso cartoon della mia infanzia, che qualcuno ricorderà benissimo :) ). Ora mi ritrovo a dover studiare un nuovo argomento: la compressione dei dati! Sicuramente sarà questo uno dei temi dei prossimi post :P
A voi è già capitato di dover affrontare un problema del genere? Preziosi suggerimenti sono ben accetti!! :)
Dopo le prime esperienze con T-SQL, non appena si è un po’ più pratici nello scrivere le query, il pensiero corre subito alle performance: “Bene, ho scritto questa query: funziona. Ma… avrei potuto scriverla meglio, in modo tale da ottenere il risultato nel minor tempo possibile?”
Mi è spesso capitato di pormi la suddetta domanda quando utilizzavo dei join. La domanda si perfezionava in: “Ma se usassi la condizione che sta nella WHERE direttamente nella JOIN condition, potrei avere dei benefici?”. In effetti, la prima cosa che ci viene in mente osservando una query del tipo:
SELECT *
FROM MyTable AS T
INNER JOIN OtherTable AS OT
ON T.id = OT.id
WHERE OT.col1 = 'filter'
è quella di immaginare che l’engine prima “unisca” le due tabelle in base alla join condition, e poi ne filtri le righe tramite la where. In realtà l’operazione “unire le due tabelle in base alla join condition” corrisponde a due operazioni logiche:
- prodotto cartesiano delle due tabelle
- filtro delle righe in base al predicato ON
Dunque il filtro della where elaborerà le righe date come risultato dalla inner join (le righe saranno al più le stesse di MyTable). Qualora si includesse tale filtro nel predicato ON, si avranno differenze nei piani di esecuzione e nelle attività di I/O?
Prendendo come database di esempio AdventureWorks, consideriamo le seguenti due query:
SET STATISTICS IO ON;
SELECT *
FROM
Sales.SalesOrderHeader AS SOH
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE
SOD.ProductID = 756;
SELECT *
FROM
Sales.SalesOrderHeader AS SOH
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
AND SOD.ProductID = 756;
Le statistiche e i piani di esecuzione sono i seguenti:
Dunque la risposta è NO, non ci sono differenze. L’engine valuta allo stesso modo il filtro del predicato ON e il filtro WHERE per l’INNER JOIN in tutti e due i casi.