Trigger e la funzione UPDATE()

Ciao a tutti!
 

Oggi parliamo di trigger e più nello specifico della funzione UPDATE() e più in generale di come ottimizzare questi automatismi che SQL Server mette a disposizione.

Partiamo dalla funzione UPDATE() per parlare delle metatabelle INSERTED e DELETED.

Buona lettura!

 

La funzione UPDATE()

 

Già, ma che cos’è la funzione UPDATE()?

La funzione UPDATE(NOMECOLONNA), che si utilizza dentro ad un trigger restituisce l’informazione relativa al fatto che il valore della campo NOMECOLONNA abbia cambiato o meno valore.

Vediamo un esempio creandoci due semplici tabelle uguali tra di loro

CREATE table TABELLA_A (id int identity (1,1), CODICE VarChar(40), DESCR VarChar(40))

CREATE table TABELLA_B (id int identity (1,1), CODICE VarChar(40), DESCR VarChar(40))

Adesso creiamo un trigger in update sulla tabella_A per ottenere questo comportamento:

Se cambia il codice nella tabelle_A allora cambierà il codice nella tabella_B

CREATE TRIGGER [dbo].[TR_TABELLA_A_UPD_CODICE] ON [dbo].[TABELLA_A]
AFTER UPDATE
AS
  IF UPDATE(CODICE)
  BEGIN
    UPDATE L
       SET L.CODICE = I.CODICE
       FROM INSERTED I    
       JOIN DELETED D on I.ID = D.ID
       JOIN TABELLA_B L on L.CODICE = D.CODICE       
  END

Come potete vedere ho l’utilizzato l’IF UPDATE(). Ma perché l’ho fatto? 

 

….Performance sempre performance!

E qui il primo concetto:

 

Che motivo avrei per eseguire un update –che non aggiornerà nessuna riga- quando il campo codice non viene cambiato. Nessuna!

Quindi le prime ottimizzazioni partono dall’evitare un esecuzione inutile.

 

 

INSERTED & DELETED

Sfruttare IF UPDATE non è però l’unico modo di ottenere questo risultato.

L’altro metodo è quello di interrogare le tabelle INSERTED e DELETED.

 

Avrei potuto infatti scrivere così:

CREATE TRIGGER [dbo].[TR_TABELLA_A_UPD_CODICE] ON [dbo].[TABELLA_A]
AFTER UPDATE
AS
  IF EXISTS (SELECT I.ID FROM INSERTED I JOIN DELETED D ON I.ID = D.ID WHERE I.CODICE = D.CODICE)
  BEGIN
    UPDATE L
       SET L.CODICE = I.CODICE
       FROM INSERTED I    
       JOIN DELETED D ON I.ID = D.ID
       JOIN TABELLA_B L ON L.CODICE = D.CODICE       
  END

 

Ma quale differenza c’è tra questi due modi di procedere?

Vediamole eseguendo questa Query con il trigger che utilizza IF UPDATE

UPDATE [TABELLA_A] SET CODICE = '012' WHERE id = 1

Osserviamo il piano di esecuzione:

Cosa notate?

Nel piano di esecuzione l’ IF UPDATE non lo vedo. Quindi come metodo sembra ottimo in quanto non impiega ne tempo ne risorse.

 

Però attenzione: perché è così  veloce?

 

La funzione UPDATE() è così veloce perché non fa altro che cercare se l’update che state eseguendo contiene “la parola” CODICE. (E questo è il secondo concetto da ricordare)

Quindi, se la velocità è il PRO, Il CONTRO deriva proprio da quello che abbiamo appena detto.

La funzione UPDATE() ritorna TRUE anche se effettivamente non viene aggiornato nessun dato: se ci fate caso le due tabelle che abbiamo creato sono vuote.

Per completare l’argomento facciamo il confronto con il trigger che impiega l’interrogazione delle tabelle INSERTED e DELETED.

Il nostro piano di esecuzione diventa così:

Cosa notate?

Innanzitutto questa volta nel piano  di esecuzione l’istruzione IF EXISTS (SELECT I.ID FROM INSERTED I JOIN DELETED D ON I.ID = D.ID WHERE I.CODICE = D.CODICE)  la vediamo (tra l’altro questa SELECT impiega il 65 % del tempo totale)

Stavolta però l’UPDATE che aggiorna il campo CODICE sulla tabella B non viene eseguito.



Per terminare, oggi vi ho raccontato "un bel po di cose" sulla funzione UPDATE().
Abbiamo visto come utilizzarla nei trigger, abbiamo capito come funziona e anche perché è così veloce.
Abbiamo poi confrontato questo modo di procedere con un modo alternativo che consiste nell’interrogare le tabelle INSERTED e DELETED.

Per oggi è tutto.

Ciao ed alla prossima!

Luca

Luca Biondi @ SQLServerPerformance blog!

Next post: SET IDENTITY_INSERT, a cosa serve?

Previous post: Di sottoquery ed ottimizzazioni (parte 2)