Feb 28 2013

Cand si cum se folosesc functiile ISNULL() si COALESCE()

Categorie: Index | NULL | OLTP | Performanta | SQL Server | T-SQLCatalin Dumitru @ 11:08

In SQL Server avem functii de system care sunt functii integrate in engine si ne ajuta sa obtinem valori, obiecte sau setari. Este si cazul functiei ISNULL() despre care vom vorbi in continuare. Coalesce() cea de-a 2-a functie despre care vom vorbi este mai degraba o expresie la fel ca si NULLIF() si CASE. Intre cele doua functii exista mai multe diferente insa in principiu ele fac cam acelasi lucru. Principalele diferente sunt:

  1. ISNULL() inlocuieste primul parametru cu al 2-lea daca primul este NULL iar COALESCE intoarce primul parametru NON NULL;
  2. ISNULL() este specific TSQL in timp ce COALESCE() este standard ANSI SQL (este bine sa se tina cont de acest aspect atunci cand ne gandim la portarea codului sau macar luam in considerare acest acepect);
  3. ISNULL() are 2 parametrii in timp ce COALESCE() nu este limitat;
  4. Tipul de data intors de ISNULL() este tipul primului parametru iar cel intors de COALESCE() este tipul parametrului cu cea mai mare prioritate; 
Ambele functii pot fi considerate ca abreviaza expresii CASE conform Querying Microsoft SQL Server 2012, capitolul 2.

Cand si cum se folosesc cele 2 functii.

Cand se folosesc cele 2 functii trebuie tinut de diferentele dintre cele doua functii. In lumea reala, aceste functii se folosesc pentru a inlocui NULL-ul din setul de date intors de o interogare, sau de a inlocui valoarea unei variabile in urma unei cautari. Spre exemplu: pentru setarea culturii unei aplicatii web putem interoga baza de date pentru a prelua date din setarile utilizatorului autentificat si daca acesta nu a facut nicio setare, putem folosi cultura implicita. 

SELECT @Cultura = ISNULL(@Cultura, Value) FROM Application.Settings WHERE Variable = cultura

Aceeasi instructiune se poate scrie mai optim:

IF @Cultura IS NULL BEGIN

       SELECT @Cultura = Value FROM Application.Settings WHERE Variable = cultura

END

Un alt caz in care putem sa folosim cele 2 functii este cel al celor 3 clauze: ON, WHERE, HAVING. In acest caz vom folosi cu grija aceste functii deoarece utilizarea lor impreuna cu coloanele dintr-o tabela au ca efect  neutilizarea indecsilor de pe coloana respectiva. Pentru exemplificare propun urmatorul exemplu: avem o tabela cu 40 de milioane de inregistrari, tebuie sa identificam randurile care contin o valoare data pe coloana guid sau avem NULL pe aceeasi coloana.

Tabela este simpla: CREATE TABLE [dbo].[Test](ID int IDENTITY(1,1) PRIMARY KEY, [guid] uniqueidentifier NULL)

Pentru test am generat peste 40 de milioane de inregistrari astfel:

INSERT        dbo.Test([guid])

SELECT        NEWID()

FROM          sys.objects o1

CROSS JOIN    sys.objects o2

CROSS JOIN    sys.objects o3

CROSS JOIN    sys.objects o4

Pentru a ne asigura ca gasim si cateva valori NULL pe coloana guid putem rula instructiunea:  

UPDATE        dbo.Test

SET           [guid] = null

WHERE         id % 10000000 = 0

Acum ca avem date de test, putem sa trecem la interogare:

Cazul fara index pe coloana guid:

DECLARE @guid uniqueidentifier

 

SELECT guid

FROM   dbo.Test

WHERE  guid = @guid

       OR (guid IS NULL AND @guid IS NULL)

 

SELECT guid

FROM   dbo.Test

WHERE  COALESCE(guid, '156004B1-CD0C-47A3-8EFB-D2E3E89E8A93')

= COALESCE(@guid, '156004B1-CD0C-47A3-8EFB-D2E3E89E8A93')

 

SELECT guid

FROM   dbo.Test

WHERE  ISNULL(guid, '156004B1-CD0C-47A3-8EFB-D2E3E89E8A93')

= ISNULL(@guid, '156004B1-CD0C-47A3-8EFB-D2E3E89E8A93')

 

SELECT guid

FROM   dbo.Test

WHERE  CASE WHEN guid IS NOT NULL

THEN guid

ELSE '156004B1-CD0C-47A3-8EFB-D2E3E89E8A93' END

= CASE WHEN @guid IS NOT NULL THEN @guid

ELSE '156004B1-CD0C-47A3-8EFB-D2E3E89E8A93' END

 

 

Cele 4 instructiuni select genereaza planuri de executie aproape identice. Primul se diferentiaza de celelalte 3 prin costul usor mai scazut al operatorului de paralelizare in timp ce restul celorlalte 3 instructiuni select  sunt identice.

 

Important ar fi de retinut ca ni se propune sa creeam un index pe coloana guid si vom beneficia de o optimizare importanta insa numai pentru primul caz, in care nu folosim functii. Asadar dupa crearea unui index pe coloana guid, planul de executie devine:

 

Putem observa ca in cazul primei interogari se va executa Index Seek (adica engine-ul se va pozitiona direct pe randurile care satisfac conditia utilizand indexul) in loc de Index Scan (parcurgerea intregului index pentru selectarea randurilor care indeplinesc conditia), urmatoarele 3 interogari vor ignora indexul si vor avea aceleasi costuri. Desi in cele 2 imagini se vad costuri diferite de 25% in prima imagine si 0% si 33% in imaginea 2, acestea sunt la nivelul celor 4 interogari, din costul total al batch-ului fiecarei interogari ii revine un anumit procent. La nivel de instructiune, variant cu ISNULL(), COAKESCE() si CASE vor performa la fel de slab.

Un alt aspect important este cand folosim aceste functii in SELECT pentru a inlocui valoarea NULL in output-ul interogarii:

SELECT COALESCE(guid, '156004B1-CD0C-47A3-8EFB-D2E3E89E8A93')

FROM   dbo.Test

WHERE  guid = @guid

       OR     (guid IS NULL AND @guid IS NULL)

 

 

Se va genera planul de executie:

 

 

 

 

Se poate vedea ca a aparut operatorul Compute scalar si avem si Index Seek. Acesta din urma este determinat de cele 3 cauze de care am mai amintit: ON, WHERE, HAVING.

 

Concluzii:

Este bine sa nu folosim aceste functii pentru astfel de cazuri in conditii pe seturi mari de date pentru ca vor ignora indecsii de pe coloana in cauza. Este bine sa pastram standardul ANSI SQL pe cat de mult posibil, nu se stie niciodata cand avem nevoie sa portam cod. Pe seturi mici de date nu o sa se genereze un plan de executie cu operatorul de paralelizare insa operatorii Index Scan si Index Seek se vor genera identic cu exemplul in cauza.

Pentru exemplificare a fost folosit SQL Server 2012 iar datele au fost de test. Desi a fost folosita o coloana uniqueidentifier, logica este aceeasi pentru toate tipurile de date.

 

 

 

Etichete: , , , , , ,