Solved

improve batch insert store proc performance

Posted on 2013-06-28
5
324 Views
Last Modified: 2013-07-18
hi guys

I have a store procedure which is inserting in customer_final table in batches of 5000.

CREATE PROCEDURE FINAL_BATCH_INS
AS
DECLARE @BatchSize INT = 5000
WHILE 1 = 1
BEGIN    
    INSERT INTO customer_final WITH (TABLOCK)
(ID,Name,Area,CheckSum) 
 SELECT TOP(@BatchSize)
 ID,Name,Area,CheckSum  FROM customer 
 WHERE NOT EXISTS (select 1 from customer_final where customer_final.CheckSum 
		    = customer.CheckSum)

    IF @@ROWCOUNT < @BatchSize BREAK
END
GO

Open in new window


Does this proc look ok?
Am i missing explicit COMMIT ?
Any way i can make it better?

thanks
0
Comment
Question by:royjayd
  • 4
5 Comments
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39285002
Only thing that I'm wondering about is the tablock hint.  If you're specifying a batch size of 5000, which is when locks typically elevate to table locks, to prevent a table lock from occurring, why use the table lock hint?  And don't get me wrong, there are pros and cons to each, just matters what you're after (using batch sizes to prevent table locks reduces contention, whereas locking the table increases the speed of the insert)
0
 

Author Comment

by:royjayd
ID: 39285054
if i remove (TABLOCK)  will it improve the performance ?
I dont really need to lock the table when insert happens because there is no other process which reads or write in this table when the insert happens.

thanks
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39285146
If you don't have to worry about contention, I would test execution times as it currently exists and test against pulling the update out of the loop, leave the tablock hint and see what kind of execution time you get. I would try testing in non production and with the same data. (You could wrap the loop in a transaction and roll it back after you get an execution time, and then pull the update out of the loop and try it with no batch size to see how they compare. You could also play with the batch sizes to see if there's a sweet spot)
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39285148
The table lock hint will help performance. I meant to say that, but did not.
0
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 500 total points
ID: 39285510
Also...  I just noticed something else. You may be able to speed up the stored proc by losing the subquery.  Like this:

DECLARE @BatchSize INT = 5000;
declare @maxKey int;
select @maxKey = max( primaryKeyColumnHere ) from customer;
declare @currKey int = 0;

WHILE 1 = 1
BEGIN    
    INSERT INTO customer_final WITH (TABLOCK)
       (ID,Name,Area,CheckSum) 
    SELECT ID,Name,Area,CheckSum  FROM customer 
    WHERE primaryKeyColumnHere >= @currKey and primaryKeyColumnHere <= @currKey + @batchSize;

    set @currKey = @currKey + @batchSize;
    IF @currKey > @maxKey BREAK
END

Open in new window

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 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