Which one is Better

Question

Which one is Better

Vishal Singh

SSCertifiable

Points: 5830
More actions
December 26, 2008 at 1:45 am

#125304

Dear Administrator
In a scenario where I am updating 5 columns out of 30 in a table and one of them.
Once I am done with the Update I am getting NULL Value for one of the columns so I decided to modify that column with 0 (value) if its NULL.
now from performace and row locking (during update) point of view, what do you think which one of the below is better?
UPDATE mytable SET col1=val1, col2 =ISNULL(val2,'0'),col3=val3,col4=val4,
OR THE BELOW ONE FROM locking point of view
UPDATE mytable SET col1=val1, col2 =val2,col3=val3,col4=val4
UPDATE mytable SET col2='0' WHERE col2 IS NULL
,.
-Forum Etiquette: How to post Performance Problems[/url]
-Forum Etiquette: How to post data/code to get the best help [/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 1

Vishal Singh (12/26/2008)
Dear Administrator
In a scenario where I am updating 5 columns out of 30 in a table and one of them.
Once I am done with the Update I am getting NULL Value for one of the columns so I decided to modify that column with 0 (value) if its NULL.
now from performace and row locking (during update) point of view, what do you think which one of the below is better?
UPDATE mytable SET col1=val1, col2 =ISNULL(val2,'0'),col3=val3,col4=val4,
OR THE BELOW ONE FROM locking point of view
UPDATE mytable SET col1=val1, col2 =val2,col3=val3,col4=val4
UPDATE mytable SET col2='0' WHERE col2 IS NULL
,.

First one is best

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 2

But I guess I it will have a significant row locks and that can impact other processes if we look on to the bigger picture for a highly busy OLTP system...isn't it?

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

ps. One Orange Chip Points: 29422 More actions · Answer 3

Whenever you fire an update statement, all the rows under scope will be locked. In first case, you're firing one update statement, the duration of row lock on that particular row will be lesser than duration of row lock if you're firing two update statements.

Row locking takes place whenever you're updating a row. It doesnt matter if u're updating with NULL or NOT NULL values.

Pradeep Singh

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 4

Yes but in the above condition, once the first update finish the row will be released for other queries.

But if I mix "IS NULL" the update will go through a looping and keep all the data for other columns locked..

like say the first update is running and meanwhile I triggered one query

to work with col1..it will get down..

here what I can see is the iteration which is happening to check NULL for the one column is putting all the other columns on hold...while in the second example at least the duration of the lock is less since in the first place scanning has not been involved..does it matter anyway..?

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

ps. One Orange Chip Points: 29422 More actions · Answer 5

Vishal Singh (12/26/2008)
Yes but in the above condition, once the first update finish the row will be released for other queries.
But if I mix "IS NULL" the update will go through a looping and keep all the data for other columns locked..
like say the first update is running and meanwhile I triggered one query
to work with col1..it will get down..
here what I can see is the iteration which is happening to check NULL for the one column is putting all the other columns on hold...while in the second example at least the duration of the lock is less since in the first place scanning has not been involved..does it matter anyway..?

I'm little confused here. Are you referring to UPDATE statement or a SELECT statement. There is no LOOPING that happnes if you use IS NULL in a conditional update!!

Pradeep Singh

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 6

If I check a condition for ISNULL would it scan the table till it gets the value..right? and whereever it gets the value null it will update the value with 0..am i in the right direction here..

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

ps. One Orange Chip Points: 29422 More actions · Answer 7

I guess u're referring to SELECT.

=========================

I've a table (reccalldetails) with appx 200,000 rows in it.

I created a non-clustered index on the column POLICYNO in it.

I fired this statement:

Case I

select * from reccalldetails where policyno='abcdefgh'

--------------------------------------------

Following is the execution plan for this line...

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([dailyactivity].[dbo].[RecCallDetails]))

|--Index Seek(OBJECT:([dailyactivity].[dbo].[RecCallDetails].[idx_reccalldetails_pol]), SEEK:([RecCallDetails].[PolicyNo]=Convert([@1])) ORDERED FORWARD)

Index Seek - 25%

BookMark Lookup - 75%

================================================

Case II

select * from reccalldetails where policyno is null

Following is the execution plan for this line...

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([dailyactivity].[dbo].[RecCallDetails]))

|--Index Seek(OBJECT:([dailyactivity].[dbo].[RecCallDetails].[idx_reccalldetails_pol]), SEEK:([RecCallDetails].[PolicyNo]=NULL) ORDERED FORWARD)

Index Seek - 51%

BookMark lookup - 49%

=========================================

In both cases it chose index seek instead of index scan. However since i have 79 rows out of 200000 which has NULLs in policyno, the relative time of index seek is more in the 2nd case.

===========================================

If your index is defined on the column used in the WHERE clause and is updated, it'll go to index seek. Also, SELECT creates a shared lock on the retrieved rows which means other SELECT statements can also view these rows simultaneously, but UPDATE wont be allowed on these rows until all rows have been retreived and the shared lock has been released.

Pradeep Singh