March 14, 2014 at 12:17 pm
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-2 [integer],
@SQLexec [varchar](500)
Set @b-2 = 0
Declare Cur1 Cursor
For Select A From dbo.tblname
Open Cur1
Fetch from Cur1 into @a
WHILE @@FETCH_STATUS = 0
Begin
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
March 14, 2014 at 12:31 pm
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.
March 14, 2014 at 12:38 pm
Before doing anything I just want to verify that you are actually using SQL Server 2000 (or older).
March 14, 2014 at 12:39 pm
It's 2005.
March 14, 2014 at 12:47 pm
I suggest that all answers should be posted on the other thread.
March 14, 2014 at 12:51 pm
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.
March 14, 2014 at 3:04 pm
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,My blog: www.igormicev.com
March 14, 2014 at 3:18 pm
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?
March 14, 2014 at 3:25 pm
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,My blog: www.igormicev.com
March 14, 2014 at 3:42 pm
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 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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply