I have created many cursors in my application to do row by row operations in each cursor single run I selected only 500 or 1000 records so that the cursor can be completed as quickly as postilion in single run, in other words I have selected limited number of records for single cursor run.
To perform the cursor faster and not to put load on server I have used following two ways of declaring cursor.
Declaration 1: DECLARE DB_CURSOR_01 CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
Declaration 2: DECLARE DB_CURSOR_02 CURSOR FAST_FORWARD FOR
Note: I am not using the default declaration of cursor, I am using other types of cursors to make it work faster, and according to my knowledge the declaration 1 mention above is faster then declaration 2, correct me if I am wrong.
Question: The other way of doing row by row operations is through "While loop using temporary table". So now my question if I convert all of my cursors to while loop using temporary table will it help to improve server performance?
Actually our DBA pointed out that server performance is effecting because of the cursor and if I put that much effort to convert all of those cursors into while loop will it give me the performance benefit? or the way I declared the cursor in declaration 1 mention above will be same performance as while loop?