SQL Server trigger optimization part 1

Hi Guys,
Today light article in which I want to tell you a short note on optimization of triggers.
Enjoy the reading!

Carissimi Lettori,
Oggi articolo leggero leggero in cui voglio raccontavi una breve nota sulla ottimizzazione dei trigger.
Buona lettura!

Introduction

Let's say right away that a good rule to apply is to avoid the execution of T-SQL statements when actually their execution goes to insert / update any row.

Suppose we have two tables called TABLE_A and TABLE_B.
Table_A has an id_table_B field that points to TABLE_B.ID
We then have a simple insert trigger done as below.

       
ALTER TRIGGER [dbo].[TR_INS_TABLE_A] on [dbo].[TABLE_A]
for insert
AS
begin
  
  Update b
    set b.campo1 = -1
  from inserted I
    join TABLE_B b on i.id_table_B = b.id
  where
    b.campo1 = 0

end

If the TABLE_A.id_table_B field accepts the null values ​​we will have that some rows of TABLE_A will have a value in the id_table_B field, other rows of the same table will not have a value.

We say that if the number of rows that have a value for the id_table_B field is less than the number of rows in the table we can introduce a further check before our update.

Which?

Simply considering that we go to Table_B using a INNER JOIN via the TABLE_A.id_table_B field.
So whenever the id_table_B field is null it is useless to execute the update.
We then add an IF EXISTS before the update and check that the id_table_B field is set.

The modified trigger will look like this:

       
ALTER TRIGGER [dbo].[TR_INS_TABLE_A] on [dbo].[TABLE_A]
for insert
AS BEGIN

  IF EXISTS(SELECT ID FROM INSERTED i where i.id_table_B IS NOT  NULL)
  BEGIN

    Update b
     set b.campo1 = -1
    from inserted I
      join TABLE_B b on i.id_table_B = b.id
    where
      b.campo1 = 0

  END

END
 

Diciamo subito che una buona regola da applicare è quella di evitare l'esecuzione di statements T-SQL quando effettivamente la loro esecuzione va ad inserire/aggiornare nessuna riga.

Supponiamo di avere due tabelle chiamate TABLE_A e TABLE_B.
Table_A ha un campo id_table_B che punta a TABLE_B.ID
Abbiamo poi un semplice trigger in insert fatto come sotto.

       
ALTER TRIGGER [dbo].[TR_INS_TABLE_A] on [dbo].[TABLE_A]
for insert
AS
begin
  
  Update b
    set b.campo1 = -1
  from inserted I
    join TABLE_B b on i.id_table_B = b.id
  where
    b.campo1 = 0

end

Se il campo TABLE_A.id_table_B accetta i valori nulli avremo che alcune righe di TABLE_A avranno valorizzato il campo id_table_B, altre righe della stessa tabella non l'avranno valorizzato.

Diciamo che, se il numero delle righe che hanno il campo valorizzato è minore del numero di righe della tabella possiamo introdurre un ulteriore controllo prima del nostro update.

Quale?

Semplicemente considerando che andiamo sulla Table_B in INNER JOIN tramite il campo TABLE_A.id_table_B.
Quindi tutte le volte che il campo id_table_B è nullo è inutile eseguire l'update.
Aggiungiamo quindi prima dell'update un IF EXISTS e controlliamo che il campo id_table_B sia valorizzato.

Il trigger modificato avrà questo aspetto:

       
ALTER TRIGGER [dbo].[TR_INS_TABLE_A] on [dbo].[TABLE_A]
for insert
AS BEGIN

  IF EXISTS(SELECT ID FROM INSERTED i where i.id_table_B IS NOT  NULL)
  BEGIN

    Update b
     set b.campo1 = -1
    from inserted I
      join TABLE_B b on i.id_table_B = b.id
    where
      b.campo1 = 0

  END

END

 

Some considerations

You will tell me that in this way, however, in all the cases in which id_table_b is valued, we are going to execute a select more!

Of course, it's true! as a matter of fact it is necessary to know how many times the field is full and how many not!
Surely, if the field is mandatory and is therefore always enhanced, it makes no sense to add the control.
If, on the contrary, the field is practically always not valued, then we can add this additional control with relative tranquility.

And in intermediate cases? In intermediate cases we can view the actual execution plan to understand what the actual cost of our T-SQL command is and the cost that additional control.

Example:

Suppose our cost update equal to 1.0 and additional control costs 0.1
Instead suppose further that in 40% of the rows the id_table_b field is filled in.

Original trigger cost = 1
Modified trigger cost = 0.4 * (1 + 0.1) + 0.6 * 0.1) = 0.44 + 0.06 = 0.5

So we can say that on average the additional condition applies!

Mi direte voi che però in questo modo in tutti i casi in cui id_table_b è valorizzato andremo ad esecuire una select in piu!

Certo, è vero! come premesso infatti occorre conoscere quante volte il campo è pieno e quante no!
Sicuramente se il campo è obbligatorio ed è quindi sempre valorizzato non ha senso aggiungere il controllo.
Se all'opposto il campo è praticamente sempre non valorizzato, allora possiamo aggiungere questo controllo aggiuntivo con relativa tranquillita.

E nei casi intermedi?  Nei casi intermedi possiamo visualizzare il piano di esecuzione effettivo per capire qual'è il costo effettivo del nostro comando T-SQL ed il costo che controllo aggiuntivo.

Esempio:

Supponiamo che il nostro update costi 1 ed il controllo aggiuntivo costi invece 0.1
Supponiamo inoltre che nel 40% delle righe il campo id_table_b sia valorizzato.

Costo trigger originale = 1
Costo trigger modificato = 0.4 * (1+0.1) + 0.6 * 0.1) = 0.44 + 0.06 = 0.5

Possiamo dire che mediamente applicare la condizione aggiuntiva conviene!




That's all for today.
See you soon!

Luca Biondi @ SQLServerPerformance blog!

Next post: SQL Server, DECIMAL and FLOAT data types. Are you ready to deep dive into the secrets of SQL Server?

Previous post: SQL Server and the JOIN operators part 1