what is better one ''where'' in a select or in update?
-
Friends, what is better ? is recommendaby to ask for some register if exists in a exists instruction beforeupdated ? or that question is better to include in the where of update???
example :
if exists ( select x from table T where field1 is not null and id = 32)
update T where id = 32
or
update T where id = 32 and field1 is not null
??? avoid two times to visit the base or avoid a block of table???
the update find the register and then blockes or block the table while is trying to find it ????????????????????????
Sorry my worst english
-
Your queries don't make sense. They don't specify what update to perform. For example, the second query should read something like this:
update T set myCol = 40
where id = 32 and field1 is not null
Also, even if they did make sense, they wouldn't do the same thing. The second one will update rowns where id is 32 and field1 is not null, whereas the first will update rows where id is 32, as long as it finds at least one row with id 32 and field1 not null.
John
-
the update find the register and then blockes or block the table while is trying to find it ??? if that is the first i dont need to use the exists but if is the second i guess that to use an exists is correct because that way i avoid an exclusive block or not??? in my example id is the index of the table, but field1 is not indexed.
-
How many rows(registers) are you expecting to update? What is your trasaction isolation level? I would say it is never advisable to say the following:
IF NOT EXISTS(SELECT ...) BEGIN INSERT/UPDATE etc. ENDBecause there is no guarentee that the results of the existance check will be valid when you do the update.
John is correct, as far as updates go, the two statements will have the same result. I highly doubt you would see any exclusive table locks in your update statement if no records exist. The only way you would see an exclusive table lock associated with an update statment under the default transaction isolation level is if you are acquiring more than around 5000 page-level locks (or if you specify a table lock hint). How many records are you expecting to update at a time?
It's true that when you are performing the update, you might block other spids, but the duration of the blocking should be very brief. if it is not (and concurrency is important), consider breaking up your update.
SQL guy and Houston Magician
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply