Using cursors with SQL Connection

This topic includes the following sections:

• SQL Server database cursor types
• Closing cursors
• Reusing cursors

 

SQL Connection uses three types of cursor:

• A statement cursor is used for an SQL statement that will not return a result set. With a statement cursor, the SQL statement is parsed and optimized when the statement is executed (with %SSC_EXECUTE or %SSC_EXECIO) or when it is linked (with %SSC_SQLLINK). A statement cursor is created when you call %SSC_OPEN with the SSQL_NONSEL option.
• A database cursor is used for an SQL statement that returns a result set. A database cursor includes a cached execution plan (a parsed and optimized version of the statement) and a mechanism for traversing the result set. A database cursor is created when you call %SSC_OPEN with the SSQL_SELECT option.
• Logical cursors are what SQL Connection uses behind the scenes to access database and statement cursors, including soft-closed cursors. You allocate logical cursors when you initialize a database channel (with %SSC_INIT). In all other cases, you’ll work with statement cursors and database cursors (while logical cursors automatically streamline data access behind the scenes).

With SQL Connection, you interact with a cursor (a statement or database cursor) by doing the following:

• Opening or reusing the cursor with %SSC_OPEN. (For information on reusing a cursor, see Reusing cursors below. For information on opening multiple concurrent cursors, see Multiple cursors, cursor reuse, and linking statements.)
• Passing the cursor ID to SQL Connection routines that require it to access data or update the database (%SSC_BIND, %SSC_MOVE, and so forth).
• Hard-closing or soft-closing the cursor. See Closing cursors below.

For SQL Server there are several database cursor types, which are discussed in SQL Server database cursor types below.

For databases other than SQL Server, database cursors are generally forward-only cursors, which cannot scroll (i.e., they only support fetching rows sequentially from the start to the end of the result set). With these databases, forward-only cursors can be used for updates.

For improved performance when using VTX11 or VTX12_SQLNATIVE, we recommend setting cursors to read-only and forward-only (by setting the %SSC_CMD option SSQL_RO_CURSOR to “yes”) whenever statements do not use stored procedures, multiple result sets, or temporary tables. With VTX12_SQLNATIVE, however, you can use the %SSC_CMD option SSQL_COPT_SS_CURSOR_OPTIONS if you have multiple result sets (which are not supported by SSQL_RO_CURSOR ) or want more control over cursor options.

Note the following:

• Cursor settings specified in an %SSC_OPEN call apply only to the cursor opened by the call.
• Cursor settings specified in a call to %SSC_CMD apply to all cursors that will subsequently be opened on the specified channel.
• If you change a cursor’s type when reusing a cursor (when passing a cursor number in the cursor argument for %SSC_OPEN), the cache for cursor reuse for identical statements is flushed.

SQL Server database cursor types

To create a cursor for SQL Server, SQL Connection sends ODBC API commands to the SQL Server ODBC client. SQL Server then creates the database cursor based on these commands. The following summarizes how SQL Connection works with SQL Server cursors. See SQL Server documentation for details.

For SQL Server, there are several cursor types: forward-only, dynamic, keyset-driven, and static. By default, SQL Connection creates dynamic database cursors for SQL Server (for backward compatibility), but we recommend that you use read-only forward-only cursors unless you want to update data. To determine which cursor type is in use for a statement, use ODBC Driver Manager trace logging; see ODBC trace logging (Windows).

The %SSC_CMD and %SSC_OPEN routines enable you to set the cursor type and to set cursor scrolling options for certain cursor types. Scrolling options enable you to determine which row will be retrieved with the next fetch. (Non-scrolling database cursors always retrieve the next row.)

Forward-only

A forward-only cursor supports fetching rows sequentially from the start to the end of the result set. Forward-only cursors are generally the most efficient cursors for read operations. In some cases, an entire result set may be cached on the client or in a network buffer, so changes made to the database after the result set is established are not reflected in the result set. We recommend using forward-only cursors whenever possible. For SQL Server, these cursors

• do not support updates.
• cannot scroll.
• are server-side cursors but will result in cached client-side cursors (firehose cursors) if you set SSQL_RO_CURSOR or use SSQL_COPT_SS_CURSOR_OPTIONS with SSQL_COPT_CO_FIREHOSE. We recommend using SSQL_RO_CURSOR, unless you have multiple result sets (which aren’t supported by SSQL_RO_CURSOR ) or want more control over cursor options. Both are %SSC_CMD settings.

To create a forward-only cursor, before calling %SSC_OPEN call %SSC_CMD with SSQL_CURSOR_FORWARD_ONLY and SSQL_RO_CURSOR (or SSQL_COPT_SS_CURSOR_OPTIONS if necessary). Make sure the %SSC_OPEN call does not include an SSQL_SCROLL option.

Note that forward-only cursors generally don’t support multiple concurrently active statements because SQL Server automatically returns a default result set. This default result set must be processed before another statement can be issued, which limits the client to one active SQL statement at a time. (If you attempt to issue an SQL statement while a previous statement is still active, you may get a “Connection is busy with results for another hstmt” error.) This may not be a limitation when using multiple active result sets (MARS).

Dynamic

Dynamic (also known as “sensitive”) is a SQL Server cursor type that reflects all changes made to the rows in its result set by other users while the cursor is open. The data values, order, and rows included in the result set can change with each fetch.

A dynamic cursor may be better than a forward-only cursor for a large result set if only part of the result set will be read, or if the result set is too large for the network buffer used for a forward-only cursor. Additionally, dynamic cursors

• can be scrolling cursors.
• enable you to have multiple concurrently active statements.
• are the only cursors you can use to update data or delete rows with SQL Server.
• are server-side cursors that use a stored procedure call behind the scenes to retrieve each row.

Dynamic is the default SQL Server cursor type for SQL Connection (for backward compatibility), but we recommend using forward-only read-only cursors whenever possible. If you use a dynamic cursor, be sure to change the cursor type back to forward-only as soon as possible.

SQL Server uses pessimistic locking when using dynamic cursors and the UPDLOCK and ROWLOCK hints. See Row locking for more information.

To create a dynamic cursor, do one of the following:

• Use %SSC_CMD to set the cursor type to SSQL_CURSOR_DYNAMIC. Then, if you want a scrolling cursor, pass SSQL_SCROLL in the %SSC_OPEN call.
• Call %SSC_OPEN with SSQL_SCROLL_DYNAMIC.

Keyset-driven

Keyset-driven cursors reflect changes made by other users to non-key columns. Row membership (rows included in the result set), order of rows, and key columns are fixed when the cursor is opened and remain unchanged for the life of the cursor. Keyset-driven cursors are usually the least efficient cursors. These cursors

• can be scrolling cursors.
• enable you to have multiple concurrently active statements.
• do not support updates.
• are server-side cursors that use a stored procedure call behind the scenes to retrieve each row.

To create a keyset-driven cursor, do one of the following:

• Use %SSC_CMD to set the cursor type to SSQL_CURSOR_KEYSET_DRIVEN. Then, if you want a scrolling cursor, pass SSQL_SCROLL in the %SSC_OPEN call.
• Call %SSC_OPEN with SSQL_SCROLL_READONLY. SSQL_SCROLL_ASENSITIVE also creates a static cursor unless it is used with SSQL_FORUPDATE (in which case it creates a keyset-driven cursor). See The options argument for more information.

If you use a keyset-driven cursor, be sure to change the cursor type back to forward-only as soon as possible.

Static

Static (also known as insensitive) is a SQL Server cursor type that displays the result set as it was when the cursor was opened. A static cursor does not reflect changes made to the database after the result set was established. Static cursors

• can be scrolling cursors (they are the most efficient scrolling cursors).
• enable you to have multiple concurrently active statements.
• are required in some cases for ORDER BY and GROUP BY clauses.
• do not support updates.
• are server-side cursors.

Each static cursor is built as a work table in tempdb when the cursor is opened (before the first row is returned), so the size of the cursor result set cannot exceed the maximum row size allowed by SQL Server.

To create a static cursor, do one of the following:

• Use %SSC_CMD to set the cursor type to SSQL_CURSOR_STATIC. Then, if you want a scrolling cursor, pass SSQL_SCROLL in the %SSC_OPEN call.
• Call %SSC_OPEN with SSQL_SCROLL_STATIC (or SSQL_SCROLL_ASENSITIVE).

If you use a static cursor, be sure to change the cursor type back to forward-only as soon as possible.

Important notes on SQL Server cursors

• We recommend setting cursors to read-only (by the %SSC_CMD option SSQL_RO_CURSOR to “yes”) unless you are updating, inserting, or deleting. This setting does not always change the way a cursor works, but it is best to set it whenever possible for improved performance.
• Watch for implicit cursor conversions. These adversely affect performance and indicate that you are using the wrong cursor type for a statement. To detect implicit cursor conversions, use SQL Connection logging. For example, if you are using a forward-only cursor, SQL Server will generate a “Success with info” message if it automatically changes the cursor type to static or dynamic.
• Intermediate sort results (e.g., for creating or rebuilding indexes) may cause a cursor to become static.
• The default concurrency setting for a SQL Server cursor is SQL_CONCUR_READ_ONLY.
• The %SSC_CMD option SSQL_TXN_ISOLEVEL sets the ODBC cursor isolation level. By default, this is set to SSQL_TXN_READ_COMMITTED for SQL Server. However, for lock timeouts to work with SQL Azure, SSQL_TXN_ISOLEVEL must be set to SSQL_TXN_READ_UNCOMMITED.
• The %SSC_CMD option SSQL_TXN_SS_SNAPSHOT enables you to create a single view of the database (a snapshot) for the duration of a connection. See SSQL_CMD_ODBC_CONNATTR.
• The %SSC_OPEN scrolling options (except SSQL_SCROLL) override the SSQL_CURSOR_TYPE options (set with %SSC_CMD). So if you set SSQL_CURSOR_STATIC (with %SSC_CMD), for example, and then set SSQL_SCROLL_DYNAMIC in the %SSC_OPEN call, the cursor will be opened as a scrolling dynamic cursor. On the other hand, if you set SSQL_CURSOR_STATIC and then set SSQL_SCROLL (in %SSC_OPEN), the cursor will be opened as a scrolling static cursor.

Closing cursors

Reusing cursors can improve performance, but cursors take up resources that you’ll generally want to release as soon as possible. SQL Connection includes two ways to close cursors: soft closing (%SSC_SCLOSE) and hard closing (%SSC_CLOSE).

• Soft closing (%SSC_SCLOSE) frees some database resources on SQL Server, but on other databases it increases the chance that the database will retain the cached results, if results are still pending for the operation. Soft closing may enable the database to cache the execution plan and just rebind variables. Results depend on your database.

In general, consider soft closing the cursor if your program will redo the same operation soon or frequently, or if your program just retrieves one row or keeps retrieving until the database has no more data for the statement. See Reusing cursors for more information.

• Hard closing (%SSC_CLOSE) frees all memory for reuse and frees database resources associated with the cursor, including the removal of the cached execution plan and all locks, closing the database cursor, and so forth. In general, consider hard closing the cursor if you know that your program will not reuse a statement soon or frequently, or if no more data will be retrieved for the statement. Additionally, note that %SSC_COMMIT, %SSC_RELEASE, and %SSC_ROLLBACK will also hard close cursors.   

  Because databases hold a cache of previously used statements, a database cache may reach its limit (resulting in a severe decrease in performance) if you do not hard close.

  With Synergy .NET in a multi-threaded environment, cursors are not automatically closed when threads terminate.

Reusing cursors

For optimal performance, reuse a cursor if the statement will be reused soon or frequently. Cursor reuse can significantly improve database and network performance. It saves time opening the cursor, and it uses less memory. SQL Connection enables you to reuse cursors if

• the database allows cursor reuse.
• the cursor is still open or has been soft-closed.
• the SQL statement in %SSC_OPEN is identical in every way to the SQL statement previously used with the cursor.

In other words, reuse a cursor if you are processing the same SQL statement several times with the same or different bind data.

In brief, these are the steps that an SQL Connection application and the database take to process an SQL statement if you don’t reuse a cursor:

1.

Open cursor.

2.

Process the SQL statement:

• Check cache. Is there an identical statement in the cache? If so, use cached entry and go to step 3.
• Parse. Is the statement correct? Does the syntax make sense?
• Bind. Do specified data objects (tables and columns) exist?
• Check authorization. Is the user allowed to access the data?
• Plan access. How is this data to be accessed?
• Optimize. How can data be retrieved more efficiently?

3.

Bind parameters.

4.

Execute the statement (e.g., fetch data).

5.

Close the cursor.

When a cursor is reused, however, the application skips the initial step of processing the SQL statement. This alone saves a great deal of overhead since initial processing is typically a very expensive process, using as much as 10 times the resources used for other steps. The reused cursor rebinds only the variables containing new data and eliminates the need for re-parsing the entire statement. You can then fetch new data and rebind the host variables as many times as necessary.

SQL Connection reuses cursors in conjunction with the %SSC_OPEN, %SSC_SCLOSE (as mentioned in Closing cursors above), %SSC_REBIND, %SSC_EXECUTE, and %SSC_EXECIO functions. For an example, see the example section for %SSC_CLOSE.

Note the following:

• When using SQL statements with conditions, use bind variables (not literals) for the conditions to ensure that the SQL statement matches the SQL statement originally used with the cursor.
• Avoid using string arithmetic to build SQL statements. Instead, use literals.
• Be sure to end transactions explicitly. If you do not explicitly commit or roll back, the database determines how to end the transaction, which may lead to unexpected results, including running out of resources.
• You can use Vortex API logging to find out how well cursor usage is optimized. See Using Vortex API logging to verify optimization for information.
• If you are fetching a row and you plan to perform a positioned update, you can use %SSC_SQLLINK to link the update statement to the open SELECT cursor rather than opening another cursor for the update statement.
• If you change a cursor’s type when reusing a cursor (in other words, when passing a cursor number in the cursor argument for %SSC_OPEN), the cache or cursor reuse for identical statements is flushed.