Solved

improve batch insert store proc performance

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

777 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