Updating table while open cursor

(Note: These examples do no real processing.) I take the idcol field from the record and print it to the screen for each record in the table — simple stuff. As noted in the section on cursor types above, this is a combination of a READ_ONLY and a FORWARD_ONLY cursor.To get a true look at how many reads are performed on the database, I'll need to use SQL Server Profiler.

updating table while open cursor-82

Free 2 way no registraton cams - Updating table while open cursor

So, for this example, the WHILE loop is almost three times as slow as the cursor approach.

However, if I were to change the cursor example from a FAST_FORWARD to a STATIC, the number of logical reads goes from 11114 to 41327.

Remember that the type of cursor you use will have a huge impact on the performance of your looping construct.

The next time you're faced with a looping situation use the tools discussed in this column and make a concerted effort to examine the performance of each approach.

The following snippet performs the same operation as the cursor above but through the use of a WHILE loop.

Notice that it is necessary to perform a query on the base table for each record returned.

This tool will give me a combined aggregation of the reads taken for each record rather than each individual record, as would be indicated through SET STATISTICS IO ON.

The cursor example above produces 11114 logical reads from the database.

Also, the use of an ORDER BY statement is necessary, as is the use of a primary key column.

Comments are closed.