SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor taking long


Cursor taking long

Author
Message
PJ_SQL
PJ_SQL
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 652
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
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16339 Visits: 19076
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
PJ_SQL
PJ_SQL
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 652
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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25908 Visits: 17519
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 Modens 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)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39039 Visits: 38514
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

Cool
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)
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16339 Visits: 19076
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39039 Visits: 38514
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.

Cool
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)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39039 Visits: 38514
Fixed my code posted earlier.

Cool
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)
PJ_SQL
PJ_SQL
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 652
got this error:

Windowed functions can only appear in the SELECT or ORDER BY clauses.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39039 Visits: 38514
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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search