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

Cursor taking long time Expand / Collapse
Author
Message
Posted Friday, March 14, 2014 12:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:58 AM
Points: 32, Visits: 177
Hi All,

This is the code I have which users cursors, I have about 2 million records and this script is reading each row and updating it. Is there any suggestions to this?

Declare @A [varchar](9),
@B [integer],
@SQLexec [varchar](500)

Set @B = 0
Declare Cur1 Cursor
For Select A From dbo.tblname

Open Cur1
Fetch from Cur1 into @A
WHILE @@FETCH_STATUS = 0

Begin
Set @B = @B + 1
Set @SQLexec = 'Update dbo.tblname Set rec_num = '+Cast(@B as varchar)+' from dbo.tblname where A = '+@A
Exec(@SQLexec)

Fetch Next From Cur1 into @A
End

Close Cur1
DEALLOCATE Cur

GO
Post #1551279
Posted Friday, March 14, 2014 12:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 3,545, Visits: 7,653
You posted in 2 different forums for 2 different versions, please define which one would you like to consider. The other post is this one: http://www.sqlservercentral.com/Forums/Topic1551281-391-1.aspx

Your code seems to do something as simple as this:
Update dbo.tblname Set 
rec_num = 0

If it doesn't satisfy your requirements, check the code you posted.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1551287
Posted Friday, March 14, 2014 12:38 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 23,293, Visits: 32,028
Before doing anything I just want to verify that you are actually using SQL Server 2000 (or older).



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 #1551295
Posted Friday, March 14, 2014 12:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:58 AM
Points: 32, Visits: 177
It's 2005.
Post #1551298
Posted Friday, March 14, 2014 12:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 3,545, Visits: 7,653
I suggest that all answers should be posted on the other thread.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1551302
Posted Friday, March 14, 2014 12:51 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 23,293, Visits: 32,028
Agreed. That way we can use the newer features that are not available in SQL Server 2000. Makes things easier.

By the way, to the OP, please don't post to multiple forums as it just fragments any responses you may get in response to your question.



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 #1551307
Posted Friday, March 14, 2014 3:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 2,933, Visits: 2,953
Do you know that cursors' performance is nearly the same to the set-based statements when they are run on up to about 1000 rows. In your case 2 million rows for a cursor will burn the cpu.
You should find a set-based solution, which is not complex for your case. You can use temp tables or CTEs...





Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1551386
Posted Friday, March 14, 2014 3:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 3,545, Visits: 7,653
Igor Micev (3/14/2014)
Do you know that cursors' performance is nearly the same to the set-based statements when they are run on up to about 1000 rows.

Not really, it might look that way with small sets of data, but it's mostly perception. A set based query will run several times faster than a cursor in most cases.

You should find a set-based solution, which is not complex for your case. You can use temp tables or CTEs...

Like the ones posted in this thread or the other?



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1551390
Posted Friday, March 14, 2014 3:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 2,933, Visits: 2,953
Luis Cazares (3/14/2014)
Igor Micev (3/14/2014)
Do you know that cursors' performance is nearly the same to the set-based statements when they are run on up to about 1000 rows.

Not really, it might look that way with small sets of data, but it's mostly perception. A set based query will run several times faster than a cursor in most cases.

[/quote]
It depends how many columns you're fetching into the cursor and what operations are then perform. However, for one or two columns and small number of rows I haven't seen significant differences. Once I was making some measures, and found out that number (1000) empiricallyl; but as I say, it depends.
Yes, set-based is faster in any case.




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1551393
Posted Friday, March 14, 2014 3:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
Igor Micev (3/14/2014)
Luis Cazares (3/14/2014)
Igor Micev (3/14/2014)
Do you know that cursors' performance is nearly the same to the set-based statements when they are run on up to about 1000 rows.

Not really, it might look that way with small sets of data, but it's mostly perception. A set based query will run several times faster than a cursor in most cases.


It depends how many columns you're fetching into the cursor and what operations are then perform. However, for one or two columns and small number of rows I haven't seen significant differences. Once I was making some measures, and found out that number (1000) empiricallyl; but as I say, it depends.
Yes, set-based is faster in any case.
[/quote]

It is the small number of rows that concerns me here. We don't generally design new processes against tables with millions of rows but we intend to use them there. And even if the table has only a couple hundred rows today, in the future it can grow to millions. Why write code that is just a time bomb? Your argument sounds like the pleas of somebody who was just asked why they wrote a cursor instead of set based solution. Just do it right the first time and then there is no need to worry about it.


_______________________________________________________________

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 #1551397
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse