Friday, February 20, 2009

Requesting a Static Cursor

Did you know you can force Advantage to return a static cursor?

It is possible to force Advantage to return a static cursor on a SELECT statement that would normally result in a live cursor. To do this, use the {static} escape sequence after the SELECT keyword. For example:

   SELECT {static} * FROM emp WHERE hire_date < '1990-01-14'

Without the escape sequence, Advantage would create a live cursor for that query. With {static} specified, Advantage will create a static cursor.

From a performance standpoint, it is probably better in most circumstances to allow Advantage to create live cursors when possible. If, however, the WHERE clause is very restrictive (thus producing a small rowset) and the base table is very large, it may be faster to force a static cursor. This is because the live cursor would be implemented with a filter on the server, which would require an index scan. If the client forced the cursor to be static, however, the server may be able to quickly seek directly to the data for the rowset with minimal cost. And once the cursor is created, the server would not be required to do any filtering when the client traversed the rowset.

No comments:

Post a Comment