July 11, 2008 at 12:09 am
Hi,
I have a script that consist a cursor to update a table row by row. There could be around 1 million row updates in the table. I need to display a message on every 1000 updates on the table. Below is an example script that I have where I am trying to accomplish this, however, the @@rowcount is not getting added up after every update inside the cursor. Any body has an idea as how this can be accomplised.
This will be run from another batch file and I want message on the command prompt to appear as " 1000 rows updated" everytime 1000 gets updated. Dont ask why we want to do it this way.
Below is a code that I have that I need help with:
DECLARE @ProductTopicID int
,@UpdRowCnt As int
,@BatchRowCnt As int
set @BatchRowCnt = 0
DECLARE ProductCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT ProductTopicId
FROM dbo.ProductTopic with (nolock)
WHERE LastPaymentDate is not NULL
OPEN ProductCursor
set @BatchRowCnt = @BatchRowCnt + @UpdRowCnt
if (@BatchRowCnt > 1000)
Begin
PRINT N'1000 rows updated'; --displaying this message on every 1000 :ermm:updates
set @BatchRowCnt = 0
end
else
PRINT N'Nothing';
FETCH NEXT FROM ProductCursor
INTO @ProductTopicID
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
UPDATE ProductTopic
SET PaymentStatus = 'Paid'
where ProductTopicID = @ProductTopicID
select @UpdRowCnt = @@rowcount
END
FETCH NEXT FROM ProductCursor
INTO @ProductTopicID
END
CLOSE ProductCursor
DEALLOCATE ProductCursor
July 11, 2008 at 12:52 am
Try moving your (print every 1000 rows) code into the WHILE @@FETCH_STATUS = 0 loop, like this:
...
FETCH NEXT FROM ProductCursor
INTO @ProductTopicID
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
UPDATE ProductTopic
SET PaymentStatus = 'Paid'
where ProductTopicID = @ProductTopicID
select @UpdRowCnt = @@rowcount
set @BatchRowCnt = @BatchRowCnt + @UpdRowCnt
if (@BatchRowCnt > 1000)
Begin
PRINT N'1000 rows updated'; --displaying this message on every 1000 updates
set @BatchRowCnt = 0
end
else
PRINT N'Nothing';
END
FETCH NEXT FROM ProductCursor
INTO @ProductTopicID
...
I know you said "don't ask", so I won't... But you absolutely should not be using cursors for this =)
July 11, 2008 at 1:00 am
I have to ask why - Sorry - but unless you have a lot more going on that cursor is costing you serious performance ....
Can you not just do:
Update producttopic set
PaymentStatus = 'Paid'
WHERE LastPaymentDate is not NULL
Also would having some sort of coding for payment status not be better - make it a tinyint or similar and allocate meanins such as 0 = Unpaid, 1 = Paid, 2- Overdue etc etc thtat would save you a lot of space and hence time. What data type is PaymentStatus ?
Mike John
July 11, 2008 at 9:11 am
The code is posted just for an example to show how I am trying to capture the @@rowcount to print message whenever 1000 rows updated. There is a lot more going inside the cursor like bunch of string manipulation before I finally run the update query. Yes, you are right, I myself try to avoid cursors unless there is not other way.
Thanks all.
July 11, 2008 at 9:25 am
bdba (7/11/2008)
The code is posted just for an example to show how I am trying to capture the @@rowcount to print message whenever 1000 rows updated. There is a lot more going inside the cursor like bunch of string manipulation before I finally run the update query. Yes, you are right, I myself try to avoid cursors unless there is not other way.Thanks all.
Is ProductTopicID the primary key or a unique index on ProductTopic? If so then each loop through the cursor should only update 1 row so @@RowCount should always be 1 so just incrementing a counter by 1 each time through the loop and printing a message when it reaches 1000 would work.
Have you considered a CLR function for the string manipulation? Then you can include it in the Update. It may simplify your SQL Code and could be faster than the cursor.
If you included at least some pseudo-code for the string manipulation you might get an answer that could eliminate the cursor altogether and do 1000 row batches.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 11, 2008 at 10:06 am
Looks like raycollins2 has your answer, but depending on if your updating 1 row at a time, its probably faster (appears performance isn't and issue since you are using a cursor) to avoid referring back to @@rowcount if you know its going to be 1 every time, and just increment a variable, then print/reset it every 1000 rows
July 11, 2008 at 10:35 am
Thanks all for useful inputs. I am trying them and will let you know if i have any issues again.
Cheers.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply