SQL Server - While Loop vs "LOCAL STATIC READ_ONLY FORWARD_ONLY" Cursor

Question

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?

Answer 1

Cursors in SQL Server are very slow. On other RDBMSes .e.g Sybase they are OK.

Below is practical approach of how to deal with them:

In my experience of "optimising" old dodgy code, the main problem with cursors is when they are based on a complex query. By complex query I mean a query that has more than a few joins and/or complex join conditions. What the cursor does is, for every iteration, it has to run this join operation, which can take more time than operations inside the body of the loop.

In cases like these it is way more efficient to run a single select into a temp table and then use the temp table in the cursor, an alternative way is to use STATIC or INSENSITIVE keyword (MSDN). One important aspect to consider is concurrency; by saving results of the main cursor query into temp table you prevent changes to the underlying tables being visible to your cursor.

The second aspect to consider are select queries inside a cursor. This is important as each query is run for each cursor iteration and therefore a select on a large table with consume a lot of resources.

I have seen some especially "dodgy" code where:

• A table is queried to return a single value using one of the cursor's fetch variables as filter. - This table should be JOINed to the main cursor query. This way this table will be queried only once and results saved to temp table.
• A table is queried to return some data based on some conditions and then later on queried again to return more data (different columns) based on the same conditions. - These two selects should be combined into one so that all data (all columns) can be returned at once.

If you have nested cursors (one inside the other), it is killer. Try removing nesting.

If you have many places with cursors prioritise fixing of the ones that match one of the cases above.

P.S. While loop on is own will not save you. You still need to use temp tables and have proper indexes on temp tables. See: https://dba.stackexchange.com/questions/84365/why-choose-a-top-query-and-temporary-table-instead-of-a-cursor-for-a-loop

The above link to Aaron Bertrand blog which discusses performance along with recommendations for cursor options.