Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

CURSOR - running very slow Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 10:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
npyata (5/2/2013)
I have included the where caluse :
where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'
order by log_idlog_timestamp_dt between '

I want to increament the log_seqno. So I am using the vr_seqno=@vr_seqno + 1 and updating the log_seqno for each row it is fetched.

Once the above update is done I want to update the other columns based the log_seqno. The update process is running very slow. I am not able to see the results when I terminate the process.


Show us. Post the DDL (CREATE TABLE statement) for the table [TESTDB].[dbo].[match_event_log], post some sample data (meaning data you make up, not real production data) as a series of INSERT INTO statements, then show us what the data in the table shold look like when your cursor process is done.

Based just on your description, I am not sure what you are doing.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1448914
Posted Thursday, May 2, 2013 11:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
Lowell (5/2/2013)


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



ROFL Lowell. I was at a meeting at a client's for the last few hours so didn't have a chance to respond. If I had, I think my response would have been almost the identical code you posted. It really is scary!!!


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448930
Posted Thursday, May 2, 2013 11:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:07 AM
Points: 9, Visits: 28
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
Post #1448935
Posted Thursday, May 2, 2013 11:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
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.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448937
Posted Thursday, May 2, 2013 12:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
One question I would ask is, do you care about the order? Your cursor has no order by so there is no guarantee what order the data will be returned. Could you maybe use an identity column?

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448939
Posted Thursday, May 2, 2013 12:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1448940
Posted Thursday, May 2, 2013 12:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:07 AM
Points: 9, Visits: 28
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;


Post #1448943
Posted Thursday, May 2, 2013 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448945
Posted Thursday, May 2, 2013 12:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:07 AM
Points: 9, Visits: 28
it working fine now. Let me work on other steps. Thanks to everyone who suggested the code improvement.
Post #1448947
Posted Thursday, May 2, 2013 8:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1449030
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse