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 Enterprise Developer.

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: