CURSOR - running very slow

Question

CURSOR - running very slow

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Sean Lange (5/2/2013)
npyata (5/2/2013)
Here is the updated code: Still this is running slow.
DECLARE @log_id INT
DECLARE @vr_seqno INT
DECLARE @getlogid CURSOR
SET @getlogid = CURSOR FOR
SELECT log_id
FROM [testdb].[dbo].[match_event_log]
OPEN @getlogid
FETCH NEXT
FROM @getlogid INTO @log_id
WHILE @@FETCH_STATUS = 0
set @vr_seqno = 0
BEGIN
PRINT @log_id
set @vr_seqno = @vr_seqno + 1
Update match_event_log
Set log_seqno = @vr_seqno
where log_id = @log_id
FETCH NEXT
FROM @getlogid INTO @log_id
END
It is running slow because you are using a cursor instead a set based update. See the post from Lowell. It will do this in a fraction of the time of this cursor.

I have to agree. You should try the code Lowell posted earlier.

npyata Mr or Mrs. 500 Points: 537 More actions · Answer 2

Here is code which has been suggested: I need the incremental value of log_seqno. How to compute vr_seqno incrementally.

ffhand i think this would do it in a single shot, but i'm looking for my friend Sean's matching post to be sure:

Update [CAMINO].[dbo].[match_event_log]

set log_seqno = vr_seqno

FROM (select

row_number() over (order by log_id,log_timestamp_dt) AS vr_seqno,

log_id

from [TESTDB].[dbo].[match_event_log]

where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'

) MyData

where log_id = MyData.log_id;

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

npyata (5/2/2013)
Here is code which has been suggested: I need the incremental value of log_seqno. How to compute vr_seqno incrementally.
ffhand i think this would do it in a single shot, but i'm looking for my friend Sean's matching post to be sure:
Update [CAMINO].[dbo].[match_event_log]
set log_seqno = vr_seqno
FROM (select
row_number() over (order by log_id,log_timestamp_dt) AS vr_seqno,
log_id
from [TESTDB].[dbo].[match_event_log]
where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'
) MyData
where log_id = MyData.log_id;

Incrementing each rows value is exactly what this is doing. Take a look at ROW_NUMBER. http://msdn.microsoft.com/en-us/library/ms186734.aspx

If you aren't sure what it is doing just run the select portion.

select

row_number() over (order by log_id,log_timestamp_dt) AS vr_seqno,

log_id

from [TESTDB].[dbo].[match_event_log]

where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

npyata Mr or Mrs. 500 Points: 537 More actions · Answer 4

it working fine now. Let me work on other steps. Thanks to everyone who suggested the code improvement.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

npyata (5/2/2013)
it working fine now. Let me work on other steps. Thanks to everyone who suggested the code improvement.

Now the BIG question is, since you're the one that will have to support the code in the future, do you actually understand exactly how the codes works and why?

Do you also understand that the word "Cursor" should not be a part of your code 99.99% of the time?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

npyata Mr or Mrs. 500 Points: 537 More actions · Answer 6

npyata

Mr or Mrs. 500

Points: 537

May 3, 2013 at 8:07 am

#1612222

Thanks