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.
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:
Locking at the page and table level
Network resources
Overhead of processing instructions
If there is a set-level programming equivalent, it may be preferable, even if it involves multiple table scans.
