Solved

improve batch insert store proc performance

Posted on 2013-06-28
5
332 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

861 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