How It Works: SQL Server (SQLNCLI11) ODBC Driver–Keyset Cursor

  • Article

This blog is based on SQL Server 2014 CU7’s updated release of SQLNCLI11 and MSSQLODBC drivers.

The basics behind KEYSET cursor behavior are described here: https://msdn.microsoft.com/en-us/library/windows/desktop/ms675119(v=vs.85).aspx 

The critical part of the referenced link is the keyset cursors ability to see changes in the data.  

When you open the cursor the sp_cursoropen or sp_cursorprepexec procedure is called returning a cursor handle to the ODBC client. You then use the SQLFetch API to retrieve 1 or more rows.  You have a choice of using SQLBindCol or SQLGetData to retrieve the data for the given columns in your result set.

Shown here is a single row (SQLFetch) scenario using SQLGetData for each column instead of bindings.

ODBC Call Client TDS Request Server TDS Response
SQLPrepare SQLExecute sp_cursorprepexec Returns cursor handle to client
For Each ROW SQLFetch sp_cursoroption TXTPTR_ONLY (1) sp_cursorfetch Returns row data for all columns and only TEXTPTR values for blob column
For Each Column SQLGetData sp_cursor REFRESH (40) or sp_cursoroption TEXT_DATA (3) sp_cursor REFRESH (40) Returns row data for all columns and only TEXTPTR values for blob column If the column to be retrieved is a blob (TXTPTR ONLY) return TEXT_DATA and refresh row data for all columns

Formula: Same Row Returned = (Number of Columns +1)     (SQLGetData REFRESH for each column + original SQLFetch)

The chatty nature of REFRESH, as each column is retrieved, surprised me a bit until I did my own testing.   You can fetch column 2.  Change column 3 at the server and then allow the client to call SQLGetData for column 3 and you see the new data.   Without the REFRESH call the intent of the KEYSET cursor to handle data changes could not be accomplished.

Here is an annotated example.

select iID, strData, tData, dtEntry from tblTest

This results in the following behavior:

  1. SQLFetch – Return data for the row =  int, varchar, text_ptr, datetime
  2. SQLGetData(iID) - Return data for the row =  int, varchar, text_ptr, datetime
  3. SQLGetData(strData) - Return data for the row =  int, varchar, text_ptr, datetime
  4. SQLGetData(tData) - Return data for the row =  int, varchar, text_data, datetime
  5. SQLGetData(dtEntry) - Return data for the row =  int, varchar, *text_ptr, datetime

The same row data is returned to the client 5 times but the actual blob is only streamed beginning at step #4.

*As part of the investigation I found a bug.  Once the blob column retrieves the text_data the driver fails to issue TEXT_PTR only for subsequent column data retrieval.   The 5th event above would actually return the entire blob instead of the pointer.  To avoid the additional, TDS overhead, place your blob column at the end of the column list.

Note: Prior to the SQL 2014 CU7 fix the SQL Server ODBC drivers may not set the TEXT_PTR option.  This issue caused each REFRESH and the original FETCH to return the entire blob stream, impacting network bandwidth and performance. 

Optimizing The Round Trips

The way to optimize this is to use column bindings and SQLSetPos with a refresh option only when required.

Using SQLBindCol before the SQLFetch allows column data to be retrieved during the SQLFetch call.   Indicating a reasonable buffer size of the blob columns is the next step in your optimization.   Perhaps you can use an 4K buffer to retrieve the majority of your blobs.   Then use a local memcpy of the actual blob into your final destination.    An indirect column binding, if you will.

If the temporary blob buffer is too small the SQLFetch returns SUCCESS_WITH_INFO indicating string right truncation.   Looking at the bound length indicators you can determine which of the columns was too small.    Rebind the column and use SQLSetPos with a refresh to retrieve the data.  (Resetting of the binding may be necessary before the next SQLFetch invocation.)  

BYTE bBlob[4096];
SQLINTEGER iBlobState = 0;
SQLINTEGER istrDataState = 0;
WCHAR strData[65];
int iData;
WCHAR strData2[65];

sqlrc = SQLBindCol(hstmt, 1, SQL_C_BINARY, bBlob, 1024, &iBlobState); // Initial 1K for sample
sqlrc = SQLBindCol(hstmt, 2, SQL_C_WCHAR, strData, 64, &istrDataState);
sqlrc = SQLBindCol(hstmt, 3, SQL_INTEGER, &iData, 0, NULL);
sqlrc = SQLBindCol(hstmt, 4, SQL_C_WCHAR, strData2, 64, NULL);

sqlrc = SQLFetch (hstmt); // Retrieves data from SQL Server, streaming entire TEXT_DATA, you want this to work the majority of the time !! if(true == truncated)
{
// Allocate new buffer as needed
sqlrc = SQLBindCol(hstmt, 1, SQL_C_BINARY, bBlob, iBlobState, &iBlobState);
sqlrc = SQLSetPos(hstmt, iRow, SQL_REFRESH, SQL_LOCK_NO_CHANGE); // Round trip to SQL Server, streaming entire TEXT_DATA – Avoid this path as much as possible to optimize performance
}

// Final storage, local copy instead of across the wire retrieval for actual length
memcpy(bFinal, bBlob, iBlobState);

The optimization technique allows you to retrieve the majority of rows in a single network trip. Using SQLGetData for data retrieval encounters

the additional overhead.

Binding Array

ODBC allows binding of an array for a column and a multiple row fetch operation.  This can also be helpful but requires a bit more work to retrieve the truncated blob data.  In order to retrieve a specific row that encountered the short transfer, clear current bindings, set the rowset position for the cursor and use SQLGetData.  

  BYTE bBlob[2][64];

  SQLINTEGER  iBlobState[2];
  WCHAR strData[2][64];

  int iData;

  WCHAR strData2[2][64];

  sqlrc = SQLBindCol(*hstmt, 1, SQL_C_BINARY, bBlob, 64, iBlobState);

  sqlrc = SQLBindCol(*hstmt, 2, SQL_C_WCHAR, strData, 128, NULL);

  sqlrc = SQLBindCol(*hstmt, 3, SQL_INTEGER, &iData, 0, NULL);

  sqlrc = SQLBindCol(*hstmt, 4, SQL_C_WCHAR, strData2, 128, NULL);

 

  sqlrc = SQLFetch(*hstmt);

  if (SQL_SUCCESS_WITH_INFO && iBlobLen is short)

  {

         //======================================================================a

         // Clear all bindings so we don't have any references to mess up memory

         //

         // NOTE: This means the SQLGetData can be different from the original fetch data

         //======================================================================

         sqlrc = SQLBindCol(*hstmt, 1, SQL_C_BINARY, NULL, 0, NULL);

         sqlrc = SQLBindCol(*hstmt, 2, SQL_C_WCHAR, NULL, 0, NULL);

         sqlrc = SQLBindCol(*hstmt, 3, SQL_INTEGER, NULL, 0, NULL);

         sqlrc = SQLBindCol(*hstmt, 4, SQL_C_WCHAR, NULL, 0, NULL);

         // For just the blob row

         sqlrc = SQLSetPos(*hstmt, 2, SQL_POSITION, SQL_LOCK_NO_CHANGE);          // Update position to row with short transfer of blob

         BYTE bBlob2[64] = {};

         sqlrc = SQLGetData(*hstmt, 1, SQL_C_BINARY, bBlob2, 64, NULL);     // Get blob that was short into new buffer not original array

         sqlrc = SQLSetPos(*hstmt, rows_fetched, SQL_POSITION, SQL_LOCK_NO_CHANGE); // need to call this after to advance to next set of rows as on next SQLFetch call

  }

  // FIX BINDINGS AND LOOP ON NEXT FETCH GROUP

Make sure you have the correct driver version installed: https://blogs.msdn.com/b/psssql/archive/2015/07/14/getting-the-latest-sql-server-native-client.aspx

Bob Dorr - Principal SQL Server Escalation Engineer