THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Uri Dimant

Another bad habit to kick: checking variables in DML operations not just before

Perhaps I am 'breaking in' Aaron's bad habits series of blogs or Alex's defensive programming but I would like to share with you the style I have seen recently on the client's side and how it affects perfromance.

There is long lines stored procedure that has many DML operations but I have seen that repeated many times within the stored procedure.

Test table has a clustered unique index created on c column and has 2 million rows.

DECLARE @par1 char(1)='A'

DECLARE @par2 INT=10



c >20000 AND c<60000

AND @par1<>'B' AND @par2>9

If you turn on an actual execution plan you will see that SQL Server  uses CI index SEEK to get to  the range and intresting , Filter operator to check variables and  also Sort operator to select the first few rows based on a sort order.

On the other hand running the below you will see only CI Delete operator only. And running then secod one takes  on my laptop slightly faster.


c >20000 AND c<60000

I would like to hear from you friends how you deal with such programming styles and how it affects performance at your company. Would it be much better to write IF..ELSE block to check for deletion which I proposed to the client in that case?



Published Thursday, March 4, 2010 3:13 AM by Uri Dimant


No Comments
New Comments to this post are disabled

About Uri Dimant

Uri Dimant
Privacy Statement