Other methods of writing a cursor...
-
Hi All,
Quite often we are advised not to use a cursor, and try to find alternative methods instead. Since I'm a learner, I get a bit confused as to what other methods can their be?
Could you guys have a look at the following code for example - and advise what other methods it can be written in?
Thanks heaps in advance.
declare member_account cursor for
select portfolio_id, portfolio_name, apir_code, class_name, class_code
from #OA_portfolio_name
for read only
open member_account
fetch member_account into @portfolio_id, @portfolio_name, @apir_code, @class_name, @class_code
while ( @@sqlstatus != 2 )
begin
exec sp_OA_portfolio_earnings @member_account_id,.....
insert into #OA_port_percentage values (
@member_account_id,
@portfolio_id,.....
)
/* reset the values */
select @start_units = 0,.....
fetch member_account into @portfolio_id, @portfolio_name, @apir_code, @class_name, @class_code
end
CLOSE member_account
DEALLOCATE member_account
-
hi
Methods generally used to avoid cursors are
using temporary tables,table data types, other looping syntaxes and implementing a different logic if possible.
In many cases i have seen that using a different logic instead of cursors gives a good performance boost.
In this u can u cud try storing member info in a temp table and use a while loop .
"Keep Trying"
-
The best solution for replacing Cursors is by using SET based logic. While you may see some performance gains by using temp tables or table variables with while loops, these are still procedural in nature and SQL Server was designed to work with sets of data. Working one row at a time is a mindset that many new SQL Server developers and DBAs have a hard time breaking. For some good exapmles, search this site for RBAR (Row by
-
the while loop is the alternative, although from a sql point of view a cursor and a while loop hold the transactions differently ( by default ).
It's a pain dealing with row based ops but sometimes thats what you need, fast forward read only ( or something similar ) are best for reads. I don't very often use cursors but as a simple example stepping through index rebuilds is a good example where I do.
Derived tables can be very useful.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks guys. I'll try that. I also found a really cool example at http://www.sql-server-performance.com/dp_no_cursors.asp
Cheers
-
whilst I agree the while loop replaces the cursor the operation is still the same and while loops often do not run better than cursors. The real answer is to eliminate row based operations.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
couldn't agree with you any more. What do you suggest then?
-
Sorry I'm late with this reply...
The answer is...
.... rewrite sp_OA_portfolio_earnings to handle more than one row at a 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.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply