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 12»»

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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:58 AM
Points: 32, Visits: 177
I have around 2 million records which needs to be updated. Any suggestions to make this faster would be appreciated.

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 #1551281
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:21 PM
Points: 3,645, Visits: 7,961
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/Topic1551279-65-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 #1551288
Posted Friday, March 14, 2014 12:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:58 AM
Points: 32, Visits: 177
Thanks, please consider this one.

I am newbee on this.

What I am trying to do is set @rec_num to 0 and then
Set @Nrec_num = @Nrec_num + 1
Set @SQLexec = 'Update dbo.tblname Set rec_num = '+Cast(@Nrec_num as varchar)+' from dbo.voters where part_no = '+@part_no
Exec(@SQLexec)

Can you please help me with this?
Post #1551294
Posted Friday, March 14, 2014 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 13,455, Visits: 12,318
Pritz (3/14/2014)
Thanks, please consider this one.

I am newbee on this.

What I am trying to do is set @rec_num to 0 and then
Set @Nrec_num = @Nrec_num + 1
Set @SQLexec = 'Update dbo.voters Set rec_num = '+Cast(@Nrec_num as varchar)+' from dbo.voters where part_no = '+@part_no
Exec(@SQLexec)

Can you please help me with this?


Same concepts as Luis posted previously. Drop the loop and there is absolutely no need to use dynamic sql for this. Probably need ROW_NUMBER for this one but without ddl and sample data it is just guesswork.


_______________________________________________________________

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 #1551299
Posted Friday, March 14, 2014 12:44 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 23,397, Visits: 32,241
Something like this:


with basedata as (
select
*, rn = row_number() over (order by part_no)
from dbo.voters
)
update basedata set
rec_num = rn;



Edit: Removed extra order by in CTE




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 #1551300
Posted Friday, March 14, 2014 12:46 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:21 PM
Points: 3,645, Visits: 7,961
Noting that you're on 2005 (as you noted in the other thread), you could use the DENSE_RANK function.

Update dbo.voters 
Set rec_num = DENSE_RANK() OVER( ORDER BY part_no)
from dbo.voters


with basedata as (
select
*, rn = DENSE_RANK() over (order by part_no)
from dbo.voters
)
update basedata set
rec_num = rn;

I might be wrong, but you could help us to avoid guessing if you posted some sample data and expected results as explained on the article linked in my signature.


EDIT: Code correction



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 #1551301
Posted Friday, March 14, 2014 12:58 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 23,397, Visits: 32,241
Luis Cazares (3/14/2014)
Noting that you're on 2005 (as you noted in the other thread), you could use the DENSE_RANK function.

Update dbo.voters 
Set rec_num = DENSE_RANK() OVER( ORDER BY part_no)
from dbo.voters

I might be wrong, but you could help us to avoid guessing if you posted some sample data and expected results as explained on the article linked in my signature.


get the following message when running your code above:

Msg 4108, Level 15, State 1, Line 11
Windowed functions can only appear in the SELECT or ORDER BY clauses.



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 #1551312
Posted Friday, March 14, 2014 1:03 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 23,397, Visits: 32,241
Fixed my code 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 #1551314
Posted Friday, March 14, 2014 1:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:58 AM
Points: 32, Visits: 177
got this error:

Windowed functions can only appear in the SELECT or ORDER BY clauses.
Post #1551316
Posted Friday, March 14, 2014 1:07 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 23,397, Visits: 32,241
Pritz (3/14/2014)
got this error:

Windowed functions can only appear in the SELECT or ORDER BY clauses.


First, for what code. Posting "got this error" really doesn't tell anyone anything.

Second, recheck the previous posts, you will find I corrected my code.



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

Add to briefcase 12»»

Permissions Expand / Collapse