Using cursors with SQL Connection

This topic includes the following sections:

• Closing cursors
• Reusing cursors
• Cursor types
• Specifying a cursor type

 

SQL Connection uses two types of cursor: database cursors and a special SQL Connection mechanism called logical cursors. A database cursor is a processed SQL statement (one that the database has parsed, optimized, and so forth—i.e., a cached execution plan) and/or the database mechanism for traversing and maintaining a position on a row in the result set. Logical cursors, on the other hand, are SQL Connection mechanisms for accessing cursors, including soft-closed cursors. You’ll allocate logical cursors when you initialize a database channel (%SSC_INIT), but otherwise, consider all discussions of cursors in this manual to be discussions of database cursors. (Behind the scenes, however, SQL Connection uses logical cursors to streamline data access.)

With SQL Connection, you interact with cursors by

• opening a new cursor or reusing a cursor when you pass an SQL statement to %SSC_OPEN.
• optionally using %SSC_CMD to set options for subsequently opened cursors.
• passing the cursor ID to other 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.

Closing cursors

Reusing cursors can improve performance, but cursors take up resources that you’ll generally want to release as soon as possible. So 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 and 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 removing 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.

Cursor types

The options argument for %SSC_OPEN and some of the %SSC_CMD options enable you to create a number of different cursor types (such as static, dynamic, and read-only), though not all databases support all of these types. Additionally, in some cases a cursor can be a scrolling cursor, which enables you to determine which row will be retrieved with the next fetch (by setting an SSQL_CMD_SCROLL option in a call to %SSC_CMD). Non-scrolling cursors, on the other hand, always retrieve the next row. Supported cursor types are briefly discussed below, but see your database documentation for more information, and see Specifying a cursor type below for information on how to select the cursor type for a statement. Note the following:

• To determine which type of cursor works best for your statements, perform timing tests and examine the database performance logs.
• If you change the SQL Connection cursor type when reusing a cursor (in other words, when passing a cursor number in the dbcursor argument for %SSC_OPEN), the cache for cursor reuse for identical statements is flushed.

Forward-only

Forward-only cursors are generally the most efficient cursors for read operations because for some databases, an entire result set may be cached on the client or in a network buffer. (Changes made to the database after the result set is established are not reflected in the result set.) Additionally, forward-only cursors

• are the only type of cursor most databases support. This is the default setting for all but VTX12_SQLNATIVE, which has a default setting of dynamic.
• may support updates, except when using VTX12_SQLNATIVE. Note that forward-only cursors don’t support positioned updates when using VTX11.
• cannot be scrolling cursors.
• are client-side cursors when accessing a SQL Server database.
• enable you to have multiple concurrently active statements. Note, however, that this may not be true if you are accessing SQL Server. With VTX12_SQLNATIVE, forward-only cursors generally don’t support multiple concurrently active statements because SQL Server returns the “default result set” (also known as a firehose cursor) when a cursor is set to forward-only (and SSQL_STANDARD, an %SSC_OPEN option). 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.) However, when using multiple active result sets (MARS) with SQL Server, this may not be a limitation.

Dynamic

Dynamic (also known as sensitive) is a SQL Server cursor type that reflects all changes made to the database by other users. 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 forward-only cursors. Additionally, dynamic cursors

• can be scrolling cursors.
• are server-side cursors.
• enable you to have multiple concurrently active statements.
• can be used only with VTX11 (if the database supports them) and VTX12_SQLNATIVE. It is the default setting for VTX12_SQLNATIVE (for application compatibility with other databases). However, we recommend using the forward-only setting when possible. See Specifying a cursor type.
• are the only cursors you can use to update data or delete rows when using VTX12_SQLNATIVE.

Keyset-driven

This is a SQL Server cursor type that is updated only with changes made to non-key columns in rows that existed when the statement was executed (not rows inserted after the result set was established). Keyset-driven cursors are usually the least efficient cursors. Additionally, keyset-driven cursors

• can be used only with VTX11 (if the database supports them) or VTX12_SQLNATIVE.
• don’t support updates.
• create a temporary table in the temporary database.
• can be scrolling cursors.
• are server-side cursors.
• enable you to have multiple concurrently active statements.

Static

Static (also known as insensitive) is a SQL Server cursor type that does not reflect any changes made to the database after the result set was established. Static cursors are the most efficient scrolling cursors. Additionally, static cursors

• can be used only with VTX11 (if the database supports them) or VTX12_SQLNATIVE.
• don’t support updates.
• create a temporary table in the temporary database.
• can be scrolling cursors.
• are server-side cursors.
• enable you to have multiple concurrently active statements.

Specifying a cursor type

To specify the type of cursor you want, use cursor options for %SSC_OPEN, %SSC_CMD, or both.

To create a forward-only cursor (a static cursor that cannot be made to scroll), call %SSC_CMD with the SSQL_CURSOR_FORWARD_ONLY option (or the SSQL_CURSOR_DEFAULT option) before calling %SSC_OPEN. Then make sure the %SSC_OPEN call does not include any of the SSQL_SCROLL options. Note the following:

• For all database drivers except VTX12_SQLNATIVE, the default cursor type is forward-only, so you can just omit any %SSC_CMD or %SSC_OPEN option that would set a type. (For compatibility with other databases, the default setting for VTX12_SQLNATIVE is dynamic.)
• For VTX12_SQLNATIVE, using %SSC_CMD to set SSQL_CURSOR_DEFAULT (or SSQL_CURSOR_FORWARD_ONLY) without setting any other cursors options (for example, scrolling options) causes SQL Server to return the default result set, which is also known as the firehose cursor. Generally, the default result set is faster than other cursor types for SQL Server, but there are some limitations. See the note under Forward-only.
• For SQL Server, you can also create a forward-only server-side cursor by specifying the %SSC_CMD command SSQL_RO_CURSOR when specifying dynamic, keyset-driven, or static cursors. Note, however, that in this case, SQL Server will not return the default result set (the firehose cursor). You must use %SSC_CMD to set SSQL_CURSOR_DEFAULT (or SSQL_CURSOR_FORWARD_ONLY) as mentioned above.

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

• Use the SSQL_CURSOR_TYPE options in a call to %SSC_CMD and then, if you want a scrolling cursor, pass SSQL_SCROLL in the %SSC_OPEN call. (Note that to make a cursor scrollable, you must use one of the SSQL_SCROLL options for %SSC_OPEN.)
• Without setting any SSQL_CURSOR_TYPE options (for %SSC_CMD), call %SSC_OPEN with one of the SSQL_SCROLL options.

  For VTX12_SQLNATIVE, if you use static or dynamic cursors and your application does not update the database, we recommend setting the SSQL_RO_CURSOR option (an %SSC_CMD option). This option instructs SQL Connection to use fast-forward cursors (SQL_CO_FFO) when possible. watch for implicit conversions, which adversely affect performance and indicate that you are using the wrong cursor type for the statement. To detect implicit cursor conversions, use SQL Connection logging (see SQL Connection logging). For example, if you are using dynamic or keyset-driven cursors, SQL Server will generate “Success with info” messages when it automatically changes cursor type. SQL Server uses pessimistic locking when using dynamic cursors and the (UPDLOCK ROWLOCK) hint. See Row locking. the default concurrency setting for a cursor is SQL_CONCUR_READ_ONLY. See Microsoft Developer Network (MSDN) documentation for more information on these topics.