Solved

Performance of Delete/Insert vs. Update

Posted on 2012-03-14
18
269 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:GNiessen
  • 8
  • 5
  • 3
  • +1
18 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 167 total points
ID: 37721540
Generally, I would expect a simple update to be much faster than a delete/insert cycle.  

On an update cycle, if the two key columns aren't changed, that index won't need to be updated.  An Insert/Delete cycle will have to update the indexes twice, and the new row is likely to be stored at the end of the table instead of the original location.  That could have a performance impact depending on how data is later accessed.

Are all of the indexes on the keys?  If not, which columns?


Good Luck,
Kent
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37721810
Here are some questions you need to answer in order to understand the impact of delete/insert(bad in my opinion) vs a simple update if record exists:
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.
0
 

Author Comment

by:GNiessen
ID: 37721955
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.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37721971
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
0
 

Author Comment

by:GNiessen
ID: 37722052
Is it better to do a select on the Keys and then do IF EXISTS:

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

Open in new window


, 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

Open in new window

0
 
LVL 45

Expert Comment

by:Kdo
ID: 37722084
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
0
 

Author Comment

by:GNiessen
ID: 37722224
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.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37722270
In my opinion the IF EXISTS is the best.
You could use @@ROWCOUNT to get the number of rows affected by last SQL statement.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 45

Expert Comment

by:Kdo
ID: 37722281
Oops.  You're correct.  I forget that SQL Server was a bit behind the curve on this one.  :)


Kent
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 166 total points
ID: 37722286
Just add the WITH (ROWLOCK) to the UPDATE like below in case you have a large number of rows to update so SQL wont decide to lock that table or pages due to lock escalation mechanism

IF EXISTS (select * from MyTable where Col1 = @pCol1 and Col2 = @pCol2)
  UPDATE MyTable WITH (ROWLOCK) 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
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 167 total points
ID: 37725454
Testing has shown that sometimes you get a performance gain by using a constant instead of * in an EXISTS check:


IF EXISTS (select 1 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
0
 

Author Comment

by:GNiessen
ID: 37725604
That should help.  Testing it now.
0
 

Author Comment

by:GNiessen
ID: 37725619
I tried to increase points, but it didn't work.  Any idea why?
0
 

Author Comment

by:GNiessen
ID: 37740470
I am increasing to 500.  I will finish testing tomorrow morning.  It all looks good now.  I just got distracted by another effort.
0
 

Author Closing Comment

by:GNiessen
ID: 37747177
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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37747316
Just to clarify, is the new process time 0.0243 or 0.243?

Kent   :)
0
 

Author Comment

by:GNiessen
ID: 37752036
Yes, 0.0243.  :-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trigger for audit 26 71
Problem with SqlConnection 4 160
SQL Restore Script - Syntax Error 8 91
How to place a condition in a filter criteria in t-sql? 12 60
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now