Best Script Performance

Asked

Viewed 47 times

0

I made a condition that way:

If @dt_contabilizacao = @dt_operacional And Not Exists (Select 1 From tabela Where      dt_mes_referencia = @mes_referencia)

But the check of dt is not necessary since if there is data of that month it no longer inserts, so I left so:

If Not Exists (Select 1 From tabela Where dt_mes_referencia = @mes_referencia)

Then a question arose, if I play this result in a variable that will contain '1' or be null, if it is null will insert, then I would make this condition in If, however I do not know if this is a way to improve the performance of the script or in it, Could you please take that question away?

  • Why not put the condition directly in command INSERT?

1 answer

4

I see no difference in performance between constructions

-- código #1
IF not exists (SELECT * from tabela where dt_mes_referencia = @mes_referencia)
   INSERT ...

and

-- código #2 v2
declare @Var bit;
set @Var= case when (exists (SELECT * from tabela where dt_mes_referencia = @mes_referencia))
                   then 'true' else 'false' end;    
IF @Var = 'false'
  INSERT ...

Theoretically the second construction would perform worse, because of the need to allocate memory to the variable, store value in the variable and then retrieve content from it; but this difference is null, in practice, because the main bottleneck in the database is access to the disk.

However, the second construction may be advantageous if the condition has to be checked more than once in the same SQL code snippet, thus reducing disk access.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.