October 28, 2006 at 2:54 am
Dear all,
I have a table which is updated and queried frequently, about 50 updates and 10 reads per second.
I used below command to update the data.
delete from table1 where columna = @columna
insert into table1(columna,...) values(@columna,...)
My issue is when the table is queried sometimes the result will be without a row with columna = @columna because it is queried just after the delete happens.
Do you think change "delete & insert" way to "update" can fix my issue? Or, I need to add a transaction to the "delete & insert"? But I am afraid of the blocks of the queries.
Thanks for your help in advance.
Thanks,
coby
October 28, 2006 at 10:35 am
you'll need to begin trans and commit trans around the statements.
if you're worried about the locking use read uncommited in your select statements
MVDBA
October 28, 2006 at 1:00 pm
Just a note:
1 "write" equals about 8 "read"s in terms of consumed resourses.
So, your approach ir really wrong for such a busy system like yours.
You must choose UPDATE + INSERT ... WHERE NOT EXISTS, and inside UPDATE you must check that the new values are different from existing ones and update only those which actually are changed.
_____________
Code for TallyGenerator
October 28, 2006 at 8:00 pm
Thanks a lot.
I will change "delete + insert" to "update + insert if not exists". The begin trans and commit trans are still needed, right?
Another question is:
Is it right that below command A will get better performance than command B?
A.
if exists( select columna from tablea where columna = @columna )
begin
update tablea set columnb = @columnb where columna = @columna
end
B.
update tablea set columnb = @columnb where columna = @columna
Thanks,
coby
October 28, 2006 at 8:15 pm
1. No need fr transaction.
Imagine scenario when 2 processes try to do the same save interrupting each other:
1st runs UPDATE, 2nd runs UPDATE,
1st runs INSERT WHERE NOT EXIST, 2nd runs INSERT WHERE NOT EXIST
Where do you see the chance for wrong data to end up in database?
2. Compare execution plans for both commands.
Both commands will not update any row if there is no match;
Both will do te same UPDATE if there is match, but command A will do it in 2 steps.
BTW. You don't need update anything if new value is already there:
update tablea
set columnb = @columnb
where columna = @columna and columnb <> @columnb
If columnb is nullable you need to change the query to handle the case of NULLs.
_____________
Code for TallyGenerator
October 28, 2006 at 8:29 pm
1. I was told that update itself is "delete + insert" actually but I think you are right because update is atomic itself and will not provide wrong data to the other select process.
2. Both commands are doing the same thing. So, will command A's performance a bit bad because it needs 2 steps if there is an update needed?
Thanks,
coby
October 28, 2006 at 9:48 pm
1. Generally speaking yes. But actually UPDATE will be equal to DELETE + INSERT in the worst possible case.
Don't forget about single record to LOG file instead of 2.
Also, if you delare transaction you must be sure this code not gonna be used inside another transaction.
2. Right.
_____________
Code for TallyGenerator
October 28, 2006 at 10:12 pm
Sergiy, Michael,
I appreciate your answers.
Thanks,
coby
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply