Choosing a Cursor Type
Choosing a cursor type depends on several variables that include:
- Size of the result set.
- Percentage of the data likely to be needed.
- Performance of the cursor open.
- Need for cursor operations, such as scrolling or positioned updates.
- Level of visibility to data modifications made by other users.
Rules for Choosing a Cursor Type
Some rules to follow when choosing a cursor type are:
- Basetable and forward-only cursors open faster than static or keyset-driven cursors. Internal temporary work tables must be built when static and keyset-driven cursors are opened, but they are not required for basetable and forward-only cursors.
- Static and keyset-driven cursors increase the usage of the temporary database. Static server cursors build the entire cursor in the temporary database. Keyset-driven cursors build the keyset in the temporary database.
- If the base table is retrieved as is, without any projection or selection, then a base table cursor is recommended because it has the least overhead.
- Static cursors consume the most resources because they duplicate the data.
See Also
Concepts
Types of Cursors
Cursors and Locking
Implicit Cursor Conversions
Updating Keyset-driven Cursors