[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

improve batch insert store proc performance

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
royjayd
Asked:
royjayd
  • 4
1 Solution
 
didnthaveanameCommented:
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
 
royjaydAuthor Commented:
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
 
didnthaveanameCommented:
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
 
didnthaveanameCommented:
The table lock hint will help performance. I meant to say that, but did not.
0
 
didnthaveanameCommented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now