Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

improve batch insert store proc performance

Posted on 2013-06-28
5
Medium Priority
?
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

618 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