Optimizing Read-only Cursor Performance for SQL CLR stored procedures

To help you to optimize read-only cursor performance for SQL CLR stored procedures, you should understand how OpenESQL processes them. SQL CLR stored procedures cannot use the MARS (Multiple Active Result Sets) directive, requiring OpenESQL to provide a work-around for processing active results sets. The solution implemented depends on the version of Visual COBOL.

Starting with Enterprise Developer 2.1, Hot Fix 8, OpenESQL defaults to using the FAST FORWARD rather than DYNAMIC server cursor option for read-only, non-scrollable COBOL cursors in SQL CLR stored procedures. FAST FORWARD cursors typically generate better query access plans than dynamic cursors, but the query plan might not be quite as efficient as a firehose cursor.

In this scenario, you could avoid using a server cursor completely by switching to an OpenESQL DATASET cursor. This change isolates code change to the DECLARE CURSOR statement itself. A drawback to this approach, however, is that it buffers the entire result set in the memory used by the stored procedure. If the result set is large or the procedure is called concurrently from many clients, this can drain memory from SQL Server while the cursor is open. Also, while DATASET cursors are not restricted to read-only cursors, they cannot be locked, which means that pessimistic concurrency is not available (although positioned updates are still supported). This could cause the application logic to fail.

You can avoid code change completely by using the OPTION=OPTIMIZESPCURSORS directive or, for Enterprise Developer 2.2 Update 1 and later, the OPTIMIZESPCURSORS directive. In this case OpenESQL opens a firehose cursor. Should any other database access occur before the cursor is closed, OpenESQL converts the remainder of the result set to a DATASET cursor.

The following table summarizes your options:

Source code change SQL Server cursor Temporary database overhead Optimal query plan Memory overhead Per statement control
singleton select Yes Firehose No Yes No Yes
read-only cursor No FAST FORWARD Yes (but less than an updatable cursor) Maybe No Yes
DATASET cursor Yes (DECLARE CURSOR only) Firehose No Yes Yes Yes
read-only cursor with OPTIMIZESPCURSORS No Firehose No Yes Maybe No
updateable cursor (for comparison) No Dynamic Yes No No Yes