Sybase Cursors and Hitlist Management

The SYB connector uses Sybase read-only cursors to manage the Uniface hitlist. For each Uniface entity, one cursor is used for each hitlist (select) stored procedure, and an extra cursor is used for all other types of hitlist requests.

Cursor names are created by adding a suffix to the entity name. If the entity name is longer than 21 characters, only the first 21 characters are used to generate the cursor name. The 21st position truncation point is used on all platforms.

Users are not allowed to create cursors whose names conflict with cursors generated by the SYB connector, shown in the table:

SYB connector cursors
Name I/O operation
entity_CUR_ALL Retrieve an entire table.
entity_CUR entity Select all of the rows in a table which match a particular value for index number #I.
entity_CUR Any other type of select.

Transactions

When a Uniface commit or rollback instruction is issued, the connector closes all open cursors.

By default, the Sybase close on endtran option is disabled, so any commit and rollback commands that are not issued by the Sybase connector should not affect open cursors.

Note:  For this reason, you should not use the Sybase close on endtran option, because doing so would cause the connector to believe a cursor is open when it is not.

Rows Fetched Over the Network

The Sybase connector sets the Sybase CT_CURSOR_ROWS parameter to the size of one step of the Uniface stepped hitlist so that the number of rows sent over the network in each batch matches the step size. This provides the best performance.

Same-Select Mechanism for Hitlist Management

Unfortunately, Sybase cursors can result in very poor performance when multiple sessions are busy with cursors. As an alternative to the use of cursors, you can choose to use the same select mechanism—for each entity and each step in the hitlist, the same select statement that was used to retrieve the first results is repeated.

Note:  This mechanism is also inefficient, so it should only be used when there is no other alternative. In this case, it may provide acceptable performance.

To use this mechanism, set the same select connector option, and fine-tune the performance by setting the rowcount and step size options:

  • step size defines the number of rows that Uniface requests at one time and keeps in memory
  • rowcount defines the number of rows the Sybase server keeps in its memory per request.

You can adjust these values to fine-tune performance with same select:

  • To improve Uniface performance, keep step size small and rowcount high
  • To improve the Sybase server performance, lower the rowcount.

For more information, see same select and rowcount.

Related Topics