THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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

 

DELETE FROM Test WHERE

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.

DELETE FROM Test WHERE

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 04, 2010 3:13 AM by Uri Dimant

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Uri Dimant

Uri Dimant
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement