SQL Server 2005 Development Guidelines

Di Davide Mauri - Microsoft MVP

In questa pagina

Una piccola premessa Una piccola premessa
SELECT * ? No, Grazie! SELECT * ? No, Grazie!
Date e Orari: il tipo DATETIME Date e Orari: il tipo DATETIME
La clausola WHERE La clausola WHERE
Conclusioni Conclusioni

Una piccola premessa

Perché scrivere un articolo sulle best practices da adottare durante lo sviluppo di soluzioni basate su SQL Server? Ce n’è davvero ancora bisogno?

Come avrete già intuito la mia risposta è “si” e anzi direi proprio “assolutamente si”! E’ ormai un fatto piuttosto consolidato, sia nella piccola realtà quanto in quella di grandi aziende, che lo sviluppatore che si trova a scrivere codice .NET (o altro) si trova anche a dover scrivere codice SQL per riuscire a leggere e scrivere dati dal database.

Tipicamente questa parte è la più snobbata di tutte, e non di rado lo sviluppo di codice SQL è visto come un “male” dalla quale non si può scappare e che se fosse possibile evitare sarebbe solo un gran sollievo.

Ve lo dice uno sviluppatore fatto e finito, amante della programmazione ad oggetti, con tutti i suoi paradigmi e le sue metodologie, che ha iniziato a sviluppare dal buon vecchio C++, arrivando ad utilizzare C# e VB.NET passando per ASM, Delphi e VB .

Ve lo dice uno sviluppatore che fino a qualche anno fa la pensava esattamente come voi. Cosa mi ha fatto cambiare idea? Inizialmente un semplice e banale motivo: per quanto potessi scrivere bene le mie applicazioni, alla fine – lavorando anche su moli relativamente piccole di dati, ma piuttosto frequentemente utilizzate – la gran parte dei problemi di prestazioni erano dovute a query poco performanti. Poco importava quanto potessi implementare e sfruttare meccanismi di caching o quanto potessi ottimizzare le mie applicazioni: nel momento in cui su un database avvenivano contemporaneamente molte query il database soffriva. Tanto. Ad esse andavano ad aggiungersi la necessità di fare report e query di analisi che, ovviamente, non facevano che peggiorare il problema.

Oltre a questo, molti errori e preconcetti che avevo erano semplicemente dovuti all’ignoranza riguardo non solo allo strumento, ma al concetto di database in generale.

Da quando inziai ad addentrarmi nel mondo dei database, ed in particolare nel mondo dei database relazionali, capii che scrivere una buona query è importante tanto quanto scrivere del buon codice. Un’applicazione non può fare a meno di un database, ed avere un’applicazione funzionante su un database malfermo è semplicemente garanzia di malfunzionamenti. Presto o tardi arriveranno; ed arriveranno tanto più in fretta tanto più la vostra applicazione avrà successo, attirando a se sempre più utenti e dati.

Questa serie di due articoli – a questo ne seguirà infatti un altro – mira proprio ad aiutare gli sviluppatori a far luce su quell’area buia che - a volte - è il database, in modo tale da poter comprendere e riparare, evitare, prevenire e soprattutto conoscere tutta quella serie di situazioni problematiche che tipicamente si incontrano durante lo sviluppo di un’applicazione, sfatando anche falsi “miti” (ad es. considerare le stored procedure transazionali), cercando così di indicare la strada giusta per capire che il database è tutt’altro che qualcosa di antico, sorpassato, scomodo e noioso.

SELECT * ? No, Grazie!

La prima regola aurea da seguire quando si scrive una query – che sia essa da utilizzarsi in modo estermporaneo, oppure da inserire all’interno di una vista o di una stored procedure – è quella di evitare sempre e comunque l’utilizzo dell’operatore star, ossia l’asterisco, per richiedere la selezione di tutte le colonne.

I motivi di questa regole sono diversi e variegati, tutti della fondamentale importanza.

Utilizzare “*” significa adattarsi ad avere il resulset contenente le colonne ordinate secondo la sequenza specificata durante la creazione della tabella dalla quale i dati stanno per essere estratti. Questo implica un errore di fondo non banale: secondo il modello relazionale le colonne non hanno un ordinamento preciso, in quanto i resultset con le colonne A,B e B,A sono assolutamente identici. Oltre che nella teoria, anche nella pratica dovremmo ragionare secondo questo principio. Il perchè è presto spiegato: supponiamo di aver scritto un’applicazione che utilizzando una query tipo

SELECT * FROM dbo.Orders

acceda alle colonne utilizzando la posizione anziché il nome delle stesse (cosa non infrequente se si tiene conto che per accedere ad una colonna specificandone il nome .NET Framework deve invocare il metodo GetOrdinal() che, seppure piccolo, comporta un overhead). E’ facile immaginare che una semplice modifica alla tabella, ad esempio aggiungendo una colonna in mezzo a due (passando quindi da due colonne A, B a tre colonne A,C,B) provochi una situazione di errore nell’applicazione stessa.

Già questo dovrebbe convincervi definitivamente della cosa, in quanto – errori applicativi a parte – si verrebbe a creare una forte coesione tra l’applicazione ed il database, che renderebbe di fatto impossibile poter mettere mano al database per ottimizzarlo o modificarlo.

Ma c’è dell’altro. Sì, perchè è bene chiarire una volta per tutte una cosa: modificare una tabella andando ad aggiungere una colonna in mezzo ad altre due già presenti è semplicemente impossibile. No, non sono impazzito. Per verificare con mano la cosa basta controllare la documentazione del comando ALTER TABLE: la possibilità di inserire una nuova colonna in una tabella già esistente non prevede l’opzione per la specifica della posizione della colonna, in quanto verrà inserita in coda alle altre. Questo è giusto e corretto in quanto, come detto in precedenza, pensare ad un ordinamento particolare delle colonne durante la definizione di una tabella non ha semplicemente senso. La sequenza con la quale si vogliono avere le colonne va specificato solo e solamente nella clausola SELECT, al posto dell’abusato “*”!

Per rispondere alla domanda che vi sta girando in testa: “Ma allora come fa l’Enterprise Manager di SQL Server 2000 oppure il Management Studio di SQL Server 2005 a permettermi di inserire una nuova colonna in una posizione arbitraria?” vi invito a provare a utilizzare il tasto

*



al posto che salvare direttamente le modifiche effettuate. Il tasto in questione genera lo script che sarà dato in pasto a SQL Server nel momento in cui deciderete di salvare le modifiche stesse. Per chi non ha sotto mano un computer sulla quale fare la prova, anticipo quello che vedrete:

  1. Creazione di una nuova tabella con la sequenza delle colonne che avete appena ridefinito

  2. Copia della tabella originale nella tabella creata al punto sopra

  3. Eliminazione della tabella orginale

  4. Rinominazione della tabella appena creata al nome della tabella originale eliminata al punto sopra

Ossia un “piccolo trucco” per poter aggirare il problema. Trucco non tanto piccolo quando nella tabella ci sono dati (magari tanti) che vengono quindi “sballottati” da una parte all’altra, andando ad impattare negativamente (e anche di molto) sulle prestazioni, sui lock, sulla concorrenza e via dicendo.

Ok, vi siete convinti? Bene, ma c’è ancora un’ultima cosa da sottolineare. Utilizzare una SELECT * significa dover praticamente rinunciare alla possibilità di avere indici ottimali, in quanto non è praticamente possibile creare indici ad hoc (più correttamente conosciuti come “di copertura”) per una select in cui vengono richiesti tutti i campi della tabella; oltre a questo una query che prende tutti i campi, magari fatta pensando di poter soddisfare esigenze diverse scrivendo un’unica query generica, genera una mole di I/O che diventa spesso un notevole collo di bottiglia (con risvolti negativi anche sull’utilizzo della memoria e dalla CPU). Molto meglio evitare l’utilizzo dello “*” e scrivere tante query quante ne servono (magari incapsulandole in Stored Procedure come vedremo nel prossimo articolo), in modo da avere query molto mirate che possono essere ottimizzate quando e quanto serve.

Date e Orari: il tipo DATETIME

Il tipo DATETIME è da sempre foriero di dubbi e piccoli-grandi problemi. Tutto nasce dal fatto che SQL Server memorizza date e orari sempre e comunque, ed a questo si aggiunge il problema della localizzazione, che modifica il modo in cui una data viene mostrata proprio in funzione della localizzazione utilizzata dell’utente.

Per evitare ogni tipo di problema legato all’interpretazione errata di una data (giorno-mese-anno oppure mese-giorno-anno?) durante la manipolazione della stessa è sufficiente utilizzare sempre comunque il formato

yyyymmdd

che, per la data odierna (15 Novembre 2006), prevede il seguente valore:

20061115

Il formato in questione è indipendente dalla localizzazione e quindi sarà sempre compreso da SQL Server in modo non ambiguo.

Attenzione al tempo però! Nel caso in cui non sia specificata l’ora (che – ricordo – è obbligatoria, in quanto il tipo di dato DATETIME la richiede), viene utilizzato il valore di default, ossia mezzanotte. Quindi la data precedentemente mostrata corrisponde, in realtà, a questa valore:

20061115 00:00:00.000

Come si nota la parte dedicata all’ora è sempre composta da ore, minuti, secondi e millisecondi

Questo significa che ogni confronto tra due date dovrà necessariamente tenere conto dell’ora. Supponendo di avere una tabella Ordini, nella quale viene memorizzata la data dell’ordine tramite GETDATE(), una query di questo genere

SELECT * FROM Ordini WHERE DataOrdine = '20061115'

Non produce il risultato voluto (ossia la lista di tutti gli ordini fatti nel determinato giorno). Questo non è strano se pensiamo alla query che viene realmente eseguita e che è questa:

SELECT * FROM Ordini WHERE DataOrdine = '20061115 00:00:00.000'

Che mostrerà quindi solo gli ordini fatti a mezzanotte (non molti probabilmente…). Per poter avere tutti gli ordini fatti nel giorno desiderato è necessario utilizzare una query di questo tipo:

SELECT * FROM Ordini WHERE 
DataOrdine >= '20061115' AND DataOrdine < '20061116'

Tale tipologia di query è assolutamente da preferirsi all’utilizzo di query che fanno uso dell’operatore CONVERT che permette di “rimuovere” la parte relativa all’ora dalla data.

La query riportata sopra è semanticamente equivalente alla seguente:

SELECT * FROM Ordini WHERE CONVERT(CHAR(8), DataOrdine , 112) = '20061115'


In quanto per ogni riga della tabella il valore in DataOrdine viene convertito in una stringa con la forma yyyymmdd (Fate riferimento ai Books Online per un approfondimento sulla funzione CONVERT e sulle tipologie di conversione possibili. In questo caso il codice 112 identifica la conversione verso il formato yyyymmdd).

La query che utilizza il CONVERT deve essere evitata a tutti i costi in quanto inibisce l’utilizzo di un eventuale indice presente sulla colonna DataOrdine, penalizzando in modo pesantemente negativo le prestazioni della query.

Per essere precisi, è bene dire che volendo la query proposta come esempio:

SELECT * FROM Ordini WHERE 
DataOrdine >= '20061115' AND DataOrdine < '20061116'

Può essere anche scritta facendo uso dell’operatore between. E’ importante però ricordarsi che la precisione dell’ora, in SQL Server, ha una granularità minima pari a 3 millisecondi. In pratica significa che per intervalli di tempo inferiore a questo SQL Server non è in grado di distinguere due orari altrimenti diversi. Volendo usare l’operatore between, quindi, la query va scritta come:

SELECT * FROM Ordini WHERE 
DataOrdine BETWEEN '20061115' AND '20061115 23:59:59.997'

Se avessimo usato ‘20061115 23:59:59.999’ SQL Server avrebbe arrotondato questo valore a ‘20061116 00:00:00.000’, producendo quindi un risultato non corretto.

Nota sul formato ISO 8601

I formati ISO invarianti rispetto alla localizzazione che possono essere utilizzati con SQL Server sono i seguenti:

yyyyMMdd hh:mm:ss.nnn

e

yyyy-MM-ddThh:mm:ss.nnn

Un esempio pratico di quello che succede se non si usa un formato ISO è dimostrato nello script sottostante, dove viene usata un data di riferimento “ambigua” che viene interpretata in modo diverso in funzione della localizzazione:

SET DATEFORMAT DMY 
SELECT CAST('20060501' as datetime) -- Corretto: 1 Maggio 2006 
SELECT CAST('2006-05-01T00:00:00' as datetime) -- Corretto: 1 Maggio 2006
SELECT CAST('2006-05-01' as datetime) -- Sbagliato: 5 Gennaio 2006
SELECT CAST('2006-05-01 00:00:00' as datetime) -- Sbagliato: 5 Gennaio 2006

L’utilizzo di SET DATEFORMAT in questo esempio serve per forzare SQL Server ad utilizzare il formato data Italiano in modo da verificare l’ambiguità con l’utilizzo di formati non ISO.

La clausola WHERE

Uno dei punti più critici di una query è la clausola WHERE. Essa è infatti in gran parte responsabile dell’utilizzo di un indice oppure no, ed è quindi bene prestare una particolare attenzione alla sua scrittura.

Senza scendere troppo nel dettaglio riguardo agli indici (è previsto un articolo in proposito), la prima nota importante è quella di evitare di applicare funzioni sulle colonne sulla quale viene fatta una ricerca. Tale best practice ha già fatto una fugace apparsa nel paragrafo dedicato alla gestione delle date e degli orari; è venuto ora il momento di approfondire il discorso.

Quando SQL Server trova una clausola WHERE cerca immediatamente di capire se c’è almeno un indice in grado di aiutarlo nella risoluzione della clausola stessa.

Perché un indice sia utile SQL Server deve sfruttare il fatto che, essendo ordinato, esso permette l’identificazione e l’esclusione di tutti quei risultati che sono prima o dopo i valori che stiamo cercando. Per far ciò il valore specificato nella clausola WHERE ed i valori della colonna sulla quale è stato costruito l’indice sulla quale stiamo facendo la ricerca devono essere, evidentemente, comparabili. E’ pertanto molto importante – affinché un indice sia utilizzato – che non vengano applicate funzioni che modificano il tipo od il valore dei dati sulla colonna indicizzata.

Una query come la seguente:

select OrderId, ProductId, UnitPrice from dbo.OrderDetails where ceiling(UnitPrice) = 36

andando ad utilizzare un’operazione di arrotondamento sulla colonna UnitPrice rende inutilizzabile l’indice posto sulla stessa colonna.

Riscrivendo la query come

select OrderId, ProductId, UnitPrice from dbo.OrderDetails where  UnitPrice > 35 and UnitPrice <= 36

si ottiene lo stesso risultato ma passando per l’utilizzo dell’indice. Nel database di esempio che ho usato per fare queste prove (65000 righe nella tabella OrderDetails) le prestazioni nel secondo caso migliorano del 300%. Ovviamente questo valore non è un riferimento assoluto, in quanto dipende molto dalla distribuzione dei dati nella tabella, ma è comunque indicativo. Su query frequentemente utilizzate un miglioramento di performance anche di piccola entità permette una diminuzione delle operazioni di I/O a carico del disco, un miglior utilizzo della memoria ed una minor durata dei lock necessari per garantire la consistenza dell’operazione.

Per gli stessi motivi è bene anche evitare di utilizzare clausole LIKE che fanno uso di wildcard come primo carattere della stringa da cercare.

Ad esempio la query:

SELECT Nome, Cognome FROM dbo.Clienti WHERE Indirizzo LIKE ‘%battista%’

Rappresenta la necessità di cercare le informazioni di tutti quei clienti che risiedono presso un indirizzo che contiene la voce “battista”. Gli indirizzi che soddisfano questa condizione sono ad esempio “Alberti Leon Battista” come pure “Martini Padre Giovan Battista” o, ancora, “San Giovanni Battista alla Creta”. Query come questa, purtroppo, richiedono una scansione della tabella che, detto in termini molto pratici, significa che SQL Server si legge tutte le righe della tabella e prende solo quelle che gli servono. Com’è possibile immaginare l’efficienza di tale operazione è piuttosto bassa, ma purtroppo non è possibile fare altro in quanto, anche mettendo un indice sulla colonna Indirizzo non aiuterebbe a velocizzare la ricerca. Il perché è piuttosto facile da capire: immaginatevi di dover cercare nella guida telefonica tutte le persone il cui cognome finisce per “uri”; il fatto che la guida sia indicizzata proprio per cognome non vi aiuta per niente in questo caso.

Altre query che soffrono di questo problema sono, ad esempio

SELECT Nome, Cognome FROM dbo.Clienti WHERE Indirizzo LIKE ‘%battista’

Oppure

SELECT Nome, Cognome FROM dbo.Clienti WHERE Indirizzo LIKE ‘_attista’

Mentre invece non ne soffre la seguente:

SELECT Nome, Cognome FROM dbo.Clienti WHERE Indirizzo LIKE ‘battista%’

In quanto in questo caso SQL Server è abbastanza furbo da capire che l’indice costruito sulla colonna Indirizzo può essere di comunque grande utilità in quanto permette di identificare velocemente (molto velocemente) tutte quei valori che iniziano con battista, escludendo cosi tutti gli altri che invece non hanno questa caratteristica.

Ovviamente dire che una query del tipo presentato nell’esempio dovrebbe essere evitata non risolve il problema dove, in molti casi, un query del genere è utile. Utile in quanto permette ad un utente di cercare informazioni anche se è in possesso solamente di una piccola parte dei dati dalla quale partire ad effettuare la ricerca.

Per questo tipo di necessità – ossia la ricerca basata su stringhe – è molto importante utilizzare proficuamente il motore Full Text Search messo a disposizione da SQL Server. Tale motore è separato dal motore relazionale (ma integrato con esso) e permette di effettuare ricerche specifiche su dati testuali, gestendo correttamente tutte le specificità di una lingua e permettendo di creare piccoli motori di ricerca simili a quelli utilizzati su web.

Un approfondimento del motore Full Text non è nello scopo di questo articolo (soprattutto per ragioni di spazio) e quindi vi invito ad approfondire l’argomento sempre utilizzando i Books Online. Oltre a quest’ultima risorsa è anche disponbile il materiale del Workshop del 15 Dicembre “Administration & Development” sul sito di UGISS (www.ugiss.org) dove è stata tenuta una sessione proprio su questo argomento.

Note riguardo SQL Server 2005 e l’operatore LIKE

SQL Server 2005 offre importanti novità riguardo l’ottimizzazione dell’esecuzione di query che fanno uso di operatori LIKE e di wildcard come prefissi della stringa di ricerca. La versione 2005 è in grado di capire e utilizzare un indice anche nel caso la clausola di ricerca sia del tipo

SELECT Nome, Cognome FROM dbo.Clienti WHERE Indirizzo LIKE ‘%battista’

Oppure

SELECT Nome, Cognome FROM dbo.Clienti WHERE Indirizzo LIKE ‘%battista%’

Ovviamente le prestazioni non sono comunque ottimali, per cui è bene evitare di utilizzare queste tipologie di ricerca se possibile; rispetto a SQL Server 2000, però, il guadagno in termini di performance è piuttosto buono.

Conclusioni

In questo primo articolo sono state presentate le best practices “di base”, riguardanti in particolare le tecniche di quering. Nel prossimo articolo verranno analizzate le best practices legate a stored procedure, transazioni ed alla manutenibilità del codice scritto.


Mostra: