GNiessen
asked on
Performance of Delete/Insert vs. Update
In looking at objects that are a high load on our database I found a Stored Procedure that is has the highest CPU usage on our system and it is a simple procedure that based on 5 parameters it deletes the record based on the 2 key fields (Unique) and then does an insert of a record with the 5 parameter fields (which match the columns in the table.
To me I though this is inefficient and it should be only doing an insert if the row is not found, otherwise doing an update.
How can I best test this out or know if I am correct?
The table has over 10 million rows with three indexes.
To me I though this is inefficient and it should be only doing an insert if the row is not found, otherwise doing an update.
How can I best test this out or know if I am correct?
The table has over 10 million rows with three indexes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, all keys (column 1 and 2) are part of the Primary Index. The third and forth columns are separately indexed for cross-reference.
This table contains rows that are updated 2-5 times over their life. And with about 15,000 added each day, I figure that is about 50,000 changes a day.
This table contains rows that are updated 2-5 times over their life. And with about 15,000 added each day, I figure that is about 50,000 changes a day.
An UPDATE will keep the data in place (unless the size of a varchar item grows the row beyond the available space in the block) and only update the index on columns 3 and 4.
Unless there's a very unique set of circumstances, an UPDATE will be a lot faster than the DELETE/INSERT cycle that you're currently using.
Kent
Unless there's a very unique set of circumstances, an UPDATE will be a lot faster than the DELETE/INSERT cycle that you're currently using.
Kent
ASKER
Is it better to do a select on the Keys and then do IF EXISTS:
, or can I do an UPDATE and then check if there were any rows effected?
IF EXISTS (select * from MyTable where Col1 = @pCol1 and Col2 = @pCol2)
UPDATE MyTable SET Col3 = @pCol3, Col4 = @pCol4, Col5 = @pCol5
WHERE Col1 = @pCol1 and Col2 = @pCol2
ELSE
INSERT MyTable (Col1, Col2, Col3, Col4, Col5)
SELECT @pCol1,@pCol2,@pCol3,@pCol4,@pCol5
, or can I do an UPDATE and then check if there were any rows effected?
UPDATE MyTable SET Col3 = @pCol3, Col4 = @pCol4, Col5 = @pCol5
WHERE Col1 = @pCol1 and Col2 = @pCol2
SELECT @rc = @@ROWCOUNT
IF @rc = 0 THEN
INSERT MyTable (Col1, Col2, Col3, Col4, Col5)
SELECT @pCol1,@pCol2,@pCol3,@pCol4,@pCol5
On SQL Server 2005 or higher (and DB2, and Oracle, and, etc.) use the MERGE statement.
MERGE INTO mytable t0
AS
(
SELECT * FROM mytable
) t1
ON (t0.primary_key = t1.primary_key)
WHEN MATCHED
THEN UPDATE SET ....
WHEN NOT MATCHED
THEN INSERT ....
Kent
MERGE INTO mytable t0
AS
(
SELECT * FROM mytable
) t1
ON (t0.primary_key = t1.primary_key)
WHEN MATCHED
THEN UPDATE SET ....
WHEN NOT MATCHED
THEN INSERT ....
Kent
ASKER
I thought MERGE was not available in 2005. I know it is in 2008. But I am in SQL Server 2005. And I get errors on MERGE.
In my opinion the IF EXISTS is the best.
You could use @@ROWCOUNT to get the number of rows affected by last SQL statement.
You could use @@ROWCOUNT to get the number of rows affected by last SQL statement.
Oops. You're correct. I forget that SQL Server was a bit behind the curve on this one. :)
Kent
Kent
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That should help. Testing it now.
ASKER
I tried to increase points, but it didn't work. Any idea why?
ASKER
I am increasing to 500. I will finish testing tomorrow morning. It all looks good now. I just got distracted by another effort.
ASKER
The original process had a cost of 0.085 for an update (0.042 for Delete and 0.043 for an insert). The new process is 0.243 (0.0003 for the Select and 0.024 for the update). Multiplied by 50,000 time, that adds up.
Thanks
Thanks
Just to clarify, is the new process time 0.0243 or 0.243?
Kent :)
Kent :)
ASKER
Yes, 0.0243. :-)
1. Does the table has any clustered index? if yes this is one reason your cpu and io are high as cluster is rebuilt with each delete/insert
2. does table has triggers that fire on delete/insert? Another reason for high cpu and io.
3. Are there any missing indexes? you need to check your query plans for that.