Cursor versus noncursor performance comparison

Table 14-2 shows statistics gathered against a 5000-row table. The cursor code takes over 4 times longer, even though it scans the table only once.

Table 14-2: Sample execution times against a 5000-row table

Procedure

Access method

Time

increase_price

Uses three table scans

28 seconds

increase_price_cursor

Uses cursor, single table scan

125 seconds

Results from tests like these can vary widely. They are most pronounced on systems that have busy networks, a large number of active database users, and multiple users accessing the same table.

In addition to locking, cursors involve more network activity than set operations and incur the overhead of processing instructions. The application program needs to communicate with Adaptive Server regarding every result row of the query. This is why the cursor code took much longer to complete than the code that scanned the table three times.

Cursor performance issues include:

If there is a set-level programming equivalent, it may be preferable, even if it involves multiple table scans.