Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-02 : 10:54:36
|
| John F writes "While optimizing a stored procedure that is particularly hairy stored procedure. I found an instance of a cursor looping through a small number of rows nested inside a cursor looping through a larger number of rows. The outer cursor loop seems to be unavoidable, but the inner one appears to be overkill (based on my knowledge of how expensive these suckers can be). The inner loop is just using the cursor to iterate through several values (which don't change with each iteration of the outer loop) and act on them.I know, I am confused too. Let me draw a picture with some pseudo-code.-----------------------------Open BigCursorOpen SmallCursorWhile (BigCursor Still Has More rows) MoveSmallCursor back to top While (SmallCursor has more rows) For each row in SmallCursor do something Fetch Next row from smallCursor Wend Do some more stuff Fetch next row from BigCursorWendclose bigcursor and smallcursor----------------------------Notice that I don't re-open or query the smallcursor inside the big loop, this is because the same values are used regardless of the outer loop. Also note that the smallcursor rarely if ever has more than 4 rows contained in it and only one field is returned.It seems that I could optimize this code by just grabbing the SmallCursor data once and re-using it in the loop without all the nested cursor nonsense, however I can't find an array or similar datatype to let me store 2-3 values and repeatedy loop through them other than a cursor. Any help would be appreciated."robvolk -- sorry, had to edit this, it wasn't displaying correctly.Edited by - robvolk on 05/02/2002 11:24:32 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-02 : 11:26:42
|
| I would recommend that you post the entire procedure. I'm sure that the cursor can be eliminated (both of them, actually) but it's impossible to know how to change the code to accommodate it without seeing it. |
 |
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2002-05-02 : 11:37:50
|
You'll probably have to do some fancy joins and/or use temporary tables / table variables. There are quite a few examples of other peoples' queries (including mine) being rewritten without cursors. Do a search for cursors or just read pretty much any post by nr  .If you post some DDL & DML, it would make it easier to help you. |
|
|
|
|
|
|
Alternatives to Cursors