September 17, 2002 at 11:10 am
Is it important for the performance the point that a cursor is declared? Must be in any place or at the beginning?
September 18, 2002 at 1:22 am
If you are worried about performance, see if you can rewrite the sp so you don't use a cursor.
eg. Derived tables, table variables etc
Steven
September 19, 2002 at 1:11 pm
Thats ok. But is it always possible to avoid using a cursor? How to loop through a recordset and make some calculations and updates?
September 20, 2002 at 5:37 am
No it is not always possible to avoid a cursor and sometimes that solution does work best. As for looping you also have the WHILE loop option. The best thing is find an answer then evaluate it for other possible soutions or get someone else to look at as the more eyes can sometimes see things you dont'.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 20, 2002 at 12:22 pm
It is quite clear, but what about the original question on where to place a cursor's declaration?
September 20, 2002 at 12:57 pm
I'll take a stab at this.
Since cursors reside in SQL Server memory I think the following guidelines should be considered:
1) Define the cursor right before you open it and start processing records for it.
2) Close and deallocate the cursor immediately upon no longer needing it.
This will allow your application to only use memory for the shortest period possible. Allowing more system resources for other processing.
Someone please correct me if you don't agree with these guidelines.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 23, 2002 at 5:37 am
Greg is exactly right.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 1, 2002 at 5:23 am
One other issue concerning performance, if you have to use a cursor then always try to declare it as FAST FORWARD. Basically this gives you an in memory read-only recordset which in my experience is the fastest.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply