commit

hi guys

I have a store proc like this

CREATE PROCEDURE FINAL_SP
AS
DECLARE @BatchSize INT = 5000
WHILE 1 = 1
BEGIN    
    INSERT INTO Final WITH (TABLOCK)
(ID,NAME,LEG_IDF) 
 SELECT TOP(@BatchSize)
 ID,Name,LEG FROM FINAL_ST 

    IF @@ROWCOUNT < @BatchSize 
    BREAK
    
END
GO

Open in new window


Right now the inserts happen on every 5000 rows,
My requirment is i also want to COMMIT on every 5000 rows.
any idea how i can do that?

Thanks.
royjaydAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Assuming you are not already running within a transaction, you could simply bookend the code with a BEGIN TRAN and COMMIT TRAN.

It also looks like you really don't need a WHILE loop, because you BREAK out of it anyway. So how about something like this:

CREATE PROCEDURE FINAL_SP
AS
DECLARE @BatchSize INT = 5000
BEGIN TRAN
INSERT INTO Final WITH (TABLOCK)
    (ID,NAME,LEG_IDF) 
    SELECT TOP(@BatchSize)
    ID,Name,LEG FROM FINAL_ST 
END
COMMIT TRAN

Open in new window

If you need the while loop for some reason, at least you can add the BEGIN TRAN and COMMIT TRAN.
0
dsackerContract ERP Admin/ConsultantCommented:
Now, if your objective was truly to loop, only inserting 5000 at a time and committing, then assuming you want to ensure you DON'T reinsert the same records, you'd do something like this.

(Post edit: I played with this query a bit, and it was troublesome. Assuming your ID is numeric, this is actually faster. If your ID is not "int", and it is numeric, change the variables to the proper type.)

CREATE PROCEDURE FINAL_SP (
    @BatchSize int = 5000
)
AS
DECLARE @LastID    int,
        @MaxID     int

SELECT @MaxID = MAX(ID) FROM FINAL_ST

WHILE 1 = 1
BEGIN
    SELECT @LastID = ISNULL(MAX(ID), -1) FROM Final
    IF @LastID = @MaxID
        BREAK

    BEGIN TRAN
    INSERT INTO Final WITH (TABLOCK)
        (ID,NAME,LEG_IDF) 
    SELECT TOP(@BatchSize) 
           ID,Name,LEG
    FROM   FINAL_ST
    WHERE  ID > @LastID
    ORDER BY ID
    COMMIT TRAN
END

Open in new window

This gets the last ID that was inserted and goes from there. I also changed the @BatchSize to a parameter with a default of 5000, so that you can call it with a different value, if ever you wished to.

There are some assumptions here, including that your ID is either a primary key or indexed. Hope this helps.
0
royjaydAuthor Commented:
thanks...
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

royjaydAuthor Commented:
>>to ensure you DON'T reinsert the same records, you'd do something like this

To ensure i dont reinsert the same record i already have a logic so i modified my code something like this..


CREATE PROCEDURE FINAL_SP
AS
DECLARE @BatchSize INT = 5000
WHILE 1 = 1
BEGIN    
  BEGIN TRAN
    INSERT INTO Final WITH (TABLOCK)
(ID,NAME,LEG_IDF)
 SELECT TOP(@BatchSize)
 ID,Name,LEG FROM FINAL_ST WHERE NOT EXISTS(select 1 from Final where Final.checksum = FINAL_ST.checksum)

COMMIT TRAN
    IF @@ROWCOUNT < @BatchSize
    BREAK
   
END
GO

Now the problem is it commits only 5000 rows and exits. Any idea what i am doing wrong?

thanks.
0
dsackerContract ERP Admin/ConsultantCommented:
Yes. I would also DECLARE @MyRowCount int. Then set it to @@ROWCOUNT immediately after the INSERT and before the COMMIT. Then check its value rather than @@ROWCOUNT.

It doesn't take very much to move past SQL retaining anything in @@ROWCOUNT.

I also wouldn't discount the second approach (using the @LastID and @MaxID), especially if your ID field is numeric. I'd benchtest both methods (the NOT EXISTS and the max ID) and see which runs faster.
0
royjaydAuthor Commented:
ok..something like this?

CREATE PROCEDURE FINAL_SP
AS
DECLARE @BatchSize INT = 5000
DECLARE @MyRowCount INT
WHILE 1 = 1
BEGIN    
  BEGIN TRAN
    INSERT INTO Final WITH (TABLOCK)
(ID,NAME,LEG_IDF) 
 SELECT TOP(@BatchSize)
 ID,Name,LEG FROM FINAL_ST WHERE NOT EXISTS(select 1 from Final where Final.checksum = FINAL_ST.checksum)

@MyRowCount = @@ROWCOUNT
 COMMIT TRAN
    IF @MyRowCount < @BatchSize 
    BREAK    
END
GO 

Open in new window

I get an error on the line>>@MyRowCount
Incorrect syntax near @MyRowCount

thanks.
0
dsackerContract ERP Admin/ConsultantCommented:
Put the SET verb in front of it. :)
0
royjaydAuthor Commented:
thanks. ran the store procedure now but it does not commit after every 5000 rows, it just keeps inserting.

this is what i have

CREATE PROCEDURE FINAL_SP
AS
DECLARE @BatchSize INT = 5000
DECLARE @MyRowCount INT
WHILE 1 = 1
BEGIN    
  BEGIN TRAN
    INSERT INTO Final WITH (TABLOCK)
(ID,NAME,LEG_IDF) 
 SELECT TOP(@BatchSize)
 ID,Name,LEG FROM FINAL_ST WHERE NOT EXISTS(select 1 from Final where Final.checksum = FINAL_ST.checksum)

set @MyRowCount = @@ROWCOUNT
 COMMIT TRAN
    IF @MyRowCount < @BatchSize 
    BREAK    
END
GO  

Open in new window


Also i cannot use the ID since it can contain alphabets and numbers and can be duplicate, we have a checksum column which we are using as a prmary key index.
Thanks
0
dsackerContract ERP Admin/ConsultantCommented:
I think it actually is committing. It simply starts a new transaction and commits it on the fly, so you're not going to see breaks unless you either add some displays or add a WAITFOR (perhaps to pause it a second). But those commits are happening.
0
royjaydAuthor Commented:
Here is why i think commit is not happening after every 5000 rows
When i do

select COUNT(*) from Final
>>No data

>>select COUNT(*) from Final(nolock)
50000

If i am not wrong (nolock) will fetch the uncommited rows and that is what is happening here.

Thanks.
0
dsackerContract ERP Admin/ConsultantCommented:
You don't by chance have a transaction already started in a parent proc?

Before your WHILE 1 = 1 add a SELECT @@TRANCOUNT above it. What do you get?
0
royjaydAuthor Commented:
let me check, will get back..
0
royjaydAuthor Commented:
I appologize for getting back so late, was really tied up with production issues.

From my observation when i run the store proc with SELECT @@TRANCOUNT
something like
CREATE PROCEDURE FINAL_SP
AS
DECLARE @BatchSize INT = 500
DECLARE @MyRowCount INT
SELECT @@TRANCOUNT
WHILE 1 = 1
BEGIN    
  BEGIN TRAN
    INSERT INTO Final WITH (TABLOCK)
(ID,NAME,LEG_IDF) 
 SELECT TOP(@BatchSize)
 ID,Name,LEG FROM FINAL_ST WHERE NOT EXISTS(select 1 from Final where Final.checksum = FINAL_ST.checksum)

set @MyRowCount = @@ROWCOUNT
 COMMIT TRAN
    IF @MyRowCount < @BatchSize 
    BREAK    
END
GO   

Open in new window


i see these two lines in log file
2013-10-16 11:47:19,416  INFO main JdbcTemplate:1116 - Added default SqlReturnResultSet parameter named #result-set-1
2013-10-16 11:47:19,416  INFO main JdbcTemplate:1116 - Added default SqlReturnResultSet parameter named #result-set-1

The entire log file is attached. This log file is generated from my java code( as my java code executes the store procedure) to insert 30000 rows with batch size of 500.
And this is why we see 61 log statements in the attached log file.

I just want to confirm that the COMMIT is actually happening after every 500 inserts.
Is there anyway this can be confirmed?

Thanks very much.
batch-logs.txt
0
dsackerContract ERP Admin/ConsultantCommented:
You could exit your process after your first COMMIT, then verify the records are there.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
royjaydAuthor Commented:
thanks , i can check that in store procedure itself i think , after the COMMIT TRAN
statement.

...
set @MyRowCount = @@ROWCOUNT
 COMMIT TRAN
Is it possible to exit from here?    
IF @MyRowCount < @BatchSize
    BREAK    
END

thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.