Kelly's Space

Coding 'n stuff

Improving cursor performance

In NetCOBOL for .NET v3.1 you can get fast forward, read-only cursors provided you configure the ODBC settings in your configuration file accordingly.  By default, all cursors will be read only.  Unfortunately, many people use cursors for updating as well, and usually get this to work by changing the @SQL_CONCURRENCY option to something other than READ_ONLY.  This option will affect all cursors in the application, and turn read only cursors into dynamic ones (which perform much slower than their read only counterparts).

You can get around this by using SQL Options in the NetCOBOL configuration section.  Basically you create two options, one for read only cursors, and the other for updatable cursors.  Then, set the @SQL_ODBC_CURSOR_DEFAULT and @SQL_ODBC_CURSOR_UPDATE settings to point to these two options so that NetCOBOL can pick the correct SQL Option depending on the cursor type.  You are probably asking: How does it know what type the cursor is?  The answer is that it looks for the FOR UPDATE clause in the cursor declaration.  In order for this setup to work, you will need to change all of the cursor declarations for any cursor that is used for updating.

Here’s a sample of what your SQL settings might look like:

   1: <sqlSettings>
   2:     <sqlOptionInf>
   3:         <option name="OPTION1" description="Read Only Cursor Settings
   4:             <add key="@SQL_CONCURRENCY" value="READ_ONLY" />
   5:             <add key="@SQL_ROW_ARRAY_SIZE" value="1" />
   6:             <add key="@SQL_CURSOR_TYPE" value="FORWARD_ONLY" />
   7:         </option>
   8:         <option name="OPTION2" description="Update cursor settings">
   9:             <add key="@SQL_CONCURRENCY" value="ROWVER" />
  10:             <add key="@SQL_ROW_ARRAY_SIZE" value="1" />
  11:             <add key="@SQL_CURSOR_TYPE" value="DYNAMIC" />
  12:         </option>
  13:     </sqlOptionInf>
  14:     <connectionScope>
  15:         <add key="@SQL_CONNECTION_SCOPE" value="THREAD" />
  16:     </connectionScope>
  17:     <serverList>
  18:         <server name="SERVER1" type="odbc" description="SERVER1">
  19:             <add key="@SQL_DATASRC" value="VidRent" />
  20:             <add key="@SQL_USERID" value="" />
  21:             <add key="@SQL_PASSWORD" value="" />
  22:             <add key="@SQL_ODBC_CURSOR_DEFAULT" value="OPTION1" />
  23:             <add key="@SQL_COMMIT_MODE" value="MANUAL" />
  24:             <add key="@SQL_ODBC_CURSOR_UPDATE" value="OPTION2" />
  25:         </server>
  26:     </serverList>
  27:     <sqlDefaultInf>
  28:         <add key="@SQL_SERVER" value="SERVER1" />
  29:         <add key="@SQL_USERID" value="video" />
  30:         <add key="@SQL_PASSWORD" value="AGMEJJBJKNFPGMAOBPNE" />
  31:     </sqlDefaultInf>
  32: </sqlSettings>

Here is a sample SQL cursor declaration for an updatable cursor:

   1: EXEC SQL DECLARE VIDCUSTBR CURSOR FOR                     
   2:     SELECT                                                   
   3:       VIDEO_NUMBER,
   4:       TITLE,
   5:       MEDIA_TYPE,
   6:       STATUS,
   7:       CUSTOMER_NUMBER,
   8:       DATE_OUT                                     
   9:     FROM                                                     
  10:       VIDMAST                                         
  11:     WHERE                                                    
  13:     FOR UPDATE                                
  14: END-EXEC.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: