My SP never finishes
-
I have a stored procedure that calls multiple other stored procedures. Each sub procedure performs a very similar task but each stored procedure is for one particular table. (This is for a conversion job that will only be run once.)
All of my SPs run fine except the last one. The last stored procedure has to update a few million rows. Last night I kicked off the process and it ran for 15.5 hours and was still not finished.
In my procedure I have to run three update queries. They are each updating the same field but I have to do it in three queries because the method of updating is different depending on the value the update field holds.
What I would like to do is break the process down into smaller chunks. I would like to update some number of records per transaction and be able to get some kind of notification when each transaction is complete. This will help me identify if there is anything getting hung up in my process.
Does anyone know how I can break this process down into multiple transactions? Because this is a conversion it doesn't have to be pretty but I would like to set it up to run the fastest and provide some feedback so I can know it is progressing.
What I would like would look something like the following:
-- First 10,000 records
BEGIN TRAN
Update method A
Update method B
Update method C
COMMIT TRAN
Print 'Transaction 1 is complete'
-- Next 10,000 records
BEGIN TRAN
Update method A
Update method B
Update method C
COMMIT TRAN
Print 'Transaction 2 is complete'
... and so on.
Thanks for all help.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011 -
You can use @rowcount = 10000 in a loop and having each update push forward until it reaches the limit. Then print out a line "update 10000 rows successful". Then loop on...
It you think the update goes too slow, you might want to make sure the where clause is using indexes correctly.
Also pre-allocate a decent size for the log so that it doesn't have to constantly expand, causing performance problem.
-
that is what I usually do. Process X amount of data first and find what is the best size to process at once.
Also I sometimes use index defrag every 100 loops and do log backup, etc.
All depends on size of database and transactions though.
-
Thanks for all your input.
I am working on a solution based on your feedback but I have another question related to this.
FYI, at the time I first submitted the question I couldn't get a record count because of some heavy processing. The total number of records (as of now) I am having to update is 6.5 million.
I want to get some kind of update to know where I am at in my process. If I put this in a stored procedure and in my stored procedure I put print statements, is there a way I can see the print statements output while the procedure is running? Is this even the best method to do this?
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011 -
Most likly, it won't print out as it goes. Don't know why but, it stack up and print out chunk messages as time goes. If anyone knows a way, I'd like to know as well.
So, I am using other way. One way is to send email out by using xp_sendmail and the other way is to have temperay table and insert into the status of how it is dong.
Changing [print '1000 rows'] to [insert into...]
Both works fine to me
-
Steve,
In your example from your Batching article, I have a question about the following example:
Declare @t int, @i int
Set @i = 5000
Set rowcount 1
While @I > 0
Begin
Select @t = transid from TempSteve
Update transaction
Set amount = t.amount
From tempsteve t
Where t.id = transaction.id
And t.transactionid= @t
Update tempsteve set status = 1
Where transid = @t
Select @I = @I - 1
End
How does this code know to get the next record from teh TempSteve table? Is this just an example or is this how the code is written? It looks to me like the SELECT from TempSteve would only get the first record every time and that the STATUS field has no impact.
Please correct me if I am wrong.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply