ASA Programming Guide
  Using SQL in Applications
    Adaptive Server Anywhere cursors

Cursor sensitivity and performance

There is a trade-off between performance and other cursor properties. In particular, making a cursor updatable places restrictions on the cursor query processing and delivery that constrain performance. Also, putting requirements on cursor sensitivity may constrain cursor performance.

To understand how the updatability and sensitivity of cursors affects performance, you need to understand how the results that are visible through a cursor are transmitted from the database to the client application.

In particular, results may be stored at two intermediate locations for performance reasons:

• Work tables    Either intermediate or final results may be stored as work tables. Value-sensitive cursors employ a work table of primary key values. Query characteristics may also lead the optimizer to use work tables in its chosen execution plan.

• Prefetching    The client side of the communication may retrieve rows into a buffer on the client side to avoid separate requests to the database server for each row.

Sensitivity and updatability limit the use of intermediate locations.

Any updatable cursor is prevented from using work tables and from prefetching results. If either of these were used, the cursor would be vulnerable to lost updates. The following example illustrates this problem:

1. An application opens a cursor on the following query against the sample database.

   SELECT id, quantity
   FROM product

   id	quantity
   300	28
   301	54
   302	75
   ...	...

2. The application fetches the row with id = 300 through the cursor.

3. A separate transaction updates the row is updated using the following statement:

   UPDATE product
   SET quantity = quantity - 10
   WHERE id = 300

4. The application updates the row through the cursor to a value of (quantity - 5 ).

5. The correct final value for the row would be 13. If the cursor had prefetched the row, the new value of the row would be 23. The update from the separate transaction is lost.

Similar restrictions govern sensitivity. For more information, see the descriptions of distinct cursor types.

Prefetching rows