How to Block fetch over a Default Result Set - Microsoft SQLCAT ISV Program Management Team - Site Home

Problem: When I was design/performance reviewing an ISV ODBC application, I turned on SQL Server profiler and noticed that the application was using server cursors. This application simply sent a select query to the server and read all the results into its local cache. So I asked the application developer why he was asking for a server cursor instead of a default result set. It turned out that the developer did not explicitly ask for a server cursor. But when he did block fetches, as a side effect, the SQL Server ODBC driver asked for a server cursor...that is unexpected! So I went to my favorite search engine and queried on “SQL_ATTR_ROW_ARRAY_SIZE firehose” and I found out I was not alone asking the question: How can I do block fetches over a Default Result Set (fire hose cursor) instead of a server cursor?

Solution: It turns out that ODBC determines whether a server cursor or a Default Result Set should be used during prepare/execute time. If the multiple array binding happens before prepare/execute (row array size > 1), server cursor is used by ODBC. It could even re-prepare if the binding with array size > 1 is left before the next execute. So the solution is to temporarily set the row array size to 1 before the prepare/execute/re-execute and reset it back to the >1 value before fetching. That way, you get a fire hose cursor and the block-fetch behavior.

Results: When we made this change to the ISV application and executed a query returning 500K rows we noticed a 33% improvement (reduction) in elapsed time to process the result set - it cut down 1130 round-trip server cursor fetch calls to 0!
----------------------------------------------------------------- -- SQL Server Profiler Trace with Block Fetch over Server Cursors -- Notice multiple sp_cursorfetch calls ----------------------------------------------------------------- declare @p1 int set @p1=1073741825 declare @p2 int set @p2=180150003 declare @p5 int set @p5=4 declare @p6 int set @p6=1 declare @p7 int set @p7=-1 exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 char(2)',N'SELECT au_lname FROM pubs.dbo.authors where state = @P1',@p5 output,@p6 output,@p7 output,'CA' select @p1, @p2, @p5, @p6, @p7 exec sp_cursorfetch 180150003,2,0,10 -- there could be 100's of these calls exec sp_cursorfetch 180150003,2,0,10 ... ... exec sp_cursorexecute 1073741825,@p2 output,@p3 output,@p4 output,@p5 output,'UT' select @p2, @p3, @p4, @p5 exec sp_cursorfetch 180150005,2,0,10 ... ... exec sp_cursorunprepare 1073741825 ---------------------------------------------------------------------- -- SQL Server Profiler Trace with Block Fetch over a Default Result Set -- Notice there are no sp_cursorfetch calls ---------------------------------------------------------------------- declare @p1 int set @p1=1 exec sp_prepexec @p1 output,N'@P1 char(2)',N'SELECT au_lname FROM pubs.dbo.authors where state = @P1','CA' exec sp_cursorexecute 1073741825,@p2 output,@p3 output,@p4 output,@p5 output,'UT' select @p2, @p3, @p4, @p5 exec sp_execute 1,'UT' exec sp_unprepare 1

Sample Code:

//////////////////////////////////////////////////////////////
// Pseudo Code:
//
// Create and allocate ODBC Statement Handle 
// SQLSetStmtAttr (SQL_ATTR_ROW_ARRAY_SIZE) to 1 
// SQLPrepare Statement 
// While some condition 
//   SQLSetStmtAttr (SQL_ATTR_ROW_ARRAY_SIZE) to 1 
//   SQLExecute Statement 
//   SQLSetStmtAttr (SQL_ATTR_ROW_ARRAY_SIZE) to > 1 
//   SQLFetchScroll 
//   SQLMoreResults 
// SQLCloseCursor 
////////////////////////////////////////////////////////////

#include <stdio.h>
#include <tchar.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <odbcss.h>

#define MAXBUFLEN   255
#define ROW_ARRAY_SIZE 10
#define STATE_LEN 2


void ProcessLogMessages(SQLSMALLINT plm_handle_type,
                        SQLHANDLE plm_handle,
                        char *logstring, int ConnInd);

int _tmain(int argc, _TCHAR* argv[])
{
  SQLHENV henv = SQL_NULL_HENV;
  SQLHDBC hdbc1 = SQL_NULL_HDBC;
  SQLHSTMT hstmt1 = SQL_NULL_HSTMT;
  RETCODE	rc;

  SQLUINTEGER    NumRowsFetched;
  SQLUSMALLINT   RowStatusArray[ROW_ARRAY_SIZE], i;

  typedef struct {
    SQLCHAR      szName[40+1];
    SQLINTEGER   szNameLenOrInd;
  } AUTHOR_NAME;

  AUTHOR_NAME authorNameArray[ROW_ARRAY_SIZE];

  // Allocate the ODBC Environment and save handle.
  rc = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

  // Notify ODBC that this is an ODBC 3.0 application.
  rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
    (SQLPOINTER)SQL_OV_ODBC3,
    SQL_IS_INTEGER);
  rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

  SQLCHAR      ConnStrIn[MAXBUFLEN] =
    "DRIVER={SQL Native Client};SERVER=SIVAR6000\\yukon;" //SQL Native Client or SQL Server
    "Trusted_Connection=yes;DATABASE=pubs;";


  SQLCHAR      ConnStrOut[MAXBUFLEN];
  SQLSMALLINT   cbConnStrOut = 0;
  SQLCHAR szState[STATE_LEN+1];
  SQLINTEGER cbState = SQL_NTS;


  rc = SQLDriverConnect(hdbc1,      // Connection handle
    NULL,         // Window handle
    ConnStrIn,      // Input connect string
    SQL_NTS,         // Null-terminated string
    ConnStrOut,      // Address of output buffer
    MAXBUFLEN,      // Size of output buffer
    &cbConnStrOut,   // Address of output length
    SQL_DRIVER_NOPROMPT);

  if ( (rc != SQL_SUCCESS) &&
    (rc != SQL_SUCCESS_WITH_INFO) ) {
      ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,
        "SQLConnect() Failed\n\n", FALSE);
      return(9);
    }
  else {
    ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,
      "\nConnect Successful\n\n", FALSE);
  }

  // Allocate statement handle, and then execute command.
  rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
  if ( (rc != SQL_SUCCESS) &&
    (rc != SQL_SUCCESS_WITH_INFO) ) {
      ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,
        "SQLAllocHandle(hstmt1) Failed\n\n",
        TRUE);
      return(9);
    }

    rc = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER)sizeof(AUTHOR_NAME), 0);

    rc = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROW_STATUS_PTR, RowStatusArray, 0);
    rc = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROWS_FETCHED_PTR, &NumRowsFetched, 0);


    // Set row array to 1 just before SQLPrepare 
    // this will tell SQL Server ODBC driver to open a firehose cursor

    // rc = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)1, 0);

    // Note: I commented the above since the driver holds SQLPrepare  
    // until it get a SQLExecute and then sends sp_prepexec
    // setting row array to 1 just before SQLExecute just seems to be enough...
    // you may want to test and ensure the above is not needed

    rc = SQLPrepare(hstmt1, (SQLCHAR*)"SELECT au_lname FROM pubs.dbo.authors where state = ?", SQL_NTS);
    if ( (rc != SQL_SUCCESS) &&
      (rc != SQL_SUCCESS_WITH_INFO) ) {
        ProcessLogMessages(SQL_HANDLE_STMT, hstmt1,
          "SQLExecute() Failed\n\n", TRUE);
        return(9);
      }

      rc = SQLBindCol(hstmt1, 1, SQL_C_CHAR, authorNameArray[0].szName, sizeof(authorNameArray[0].szName), 
        &authorNameArray[0].szNameLenOrInd);


      rc = SQLBindParameter(hstmt1, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, STATE_LEN, 0, 
        szState, 0, &cbState);

      int someCondition = 0;

      while (someCondition < 2) // execute twice
      {
        if (0 == someCondition) 
          strcpy ((char*)szState, "CA");
        else if (1 == someCondition)
          strcpy ((char*)szState, "UT");
        else
        {
          printf ("we should not be here...\n");
          return (9);
        }

        // set row array size to 1 just before execute
        rc = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)1, 0);
        rc = SQLExecute (hstmt1);
        if ( (rc != SQL_SUCCESS) &&
          (rc != SQL_SUCCESS_WITH_INFO) ) {
            ProcessLogMessages(SQL_HANDLE_STMT, hstmt1,
              "SQLExecute() Failed\n\n", TRUE);
            return(9);
          }
          // set row array size to > 1 just before fetch
          rc = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)ROW_ARRAY_SIZE, 0);

          while ((rc = SQLFetchScroll(hstmt1,SQL_FETCH_NEXT,0)) != SQL_NO_DATA) {
            for (i = 0; i < NumRowsFetched; i++) {
              if (RowStatusArray[i] == SQL_ROW_SUCCESS|| RowStatusArray[i] == 
                SQL_ROW_SUCCESS_WITH_INFO) {

                  if (authorNameArray[i].szNameLenOrInd == SQL_NULL_DATA)
                    printf(" NULL      ");
                  else
                    printf("%s: %s\t\n", szState, authorNameArray[i].szName);

                }
            }
          }
          while ( ( rc = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )
            ;
          someCondition++;

      }
      // Close the cursor.
      rc = SQLCloseCursor(hstmt1);
      SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
      SQLDisconnect(hdbc1);
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
      SQLFreeHandle(SQL_HANDLE_ENV, henv);

      return 0;
}


void ProcessLogMessages(SQLSMALLINT plm_handle_type,
                        SQLHANDLE plm_handle,
                        char *logstring, int ConnInd)
{
  RETCODE      plm_retcode = SQL_SUCCESS;
  UCHAR      plm_szSqlState[MAXBUFLEN] = "",
    plm_szErrorMsg[MAXBUFLEN] = "";
  SDWORD      plm_pfNativeError = 0L;
  SWORD      plm_pcbErrorMsg = 0;
  SQLSMALLINT   plm_cRecNmbr = 1;
  SDWORD      plm_SS_MsgState = 0, plm_SS_Severity = 0;
  SQLINTEGER   plm_Rownumber = 0;
  USHORT      plm_SS_Line;
  SQLSMALLINT   plm_cbSS_Procname, plm_cbSS_Srvname;
  SQLCHAR      plm_SS_Procname[MAXNAME], plm_SS_Srvname[MAXNAME];

  printf(logstring);

  while (plm_retcode != SQL_NO_DATA_FOUND) {
    plm_retcode = SQLGetDiagRec(plm_handle_type, plm_handle,
      plm_cRecNmbr, plm_szSqlState, &plm_pfNativeError,
      plm_szErrorMsg, MAXBUFLEN - 1, &plm_pcbErrorMsg);

    // Note that if the application has not yet made a
    // successful connection, the SQLGetDiagField
    // information has not yet been cached by ODBC
    // Driver Manager and these calls to SQLGetDiagField
    // will fail.
    if (plm_retcode != SQL_NO_DATA_FOUND) {
      if (ConnInd) {
        plm_retcode = SQLGetDiagField(
          plm_handle_type, plm_handle, plm_cRecNmbr,
          SQL_DIAG_ROW_NUMBER, &plm_Rownumber,
          SQL_IS_INTEGER,
          NULL);
        plm_retcode = SQLGetDiagField(
          plm_handle_type, plm_handle, plm_cRecNmbr,
          SQL_DIAG_SS_LINE, &plm_SS_Line,
          SQL_IS_INTEGER,
          NULL);
        plm_retcode = SQLGetDiagField(
          plm_handle_type, plm_handle, plm_cRecNmbr,
          SQL_DIAG_SS_MSGSTATE, &plm_SS_MsgState,
          SQL_IS_INTEGER,
          NULL);
        plm_retcode = SQLGetDiagField(
          plm_handle_type, plm_handle, plm_cRecNmbr,
          SQL_DIAG_SS_SEVERITY, &plm_SS_Severity,
          SQL_IS_INTEGER,
          NULL);
        plm_retcode = SQLGetDiagField(
          plm_handle_type, plm_handle, plm_cRecNmbr,
          SQL_DIAG_SS_PROCNAME, &plm_SS_Procname,
          sizeof(plm_SS_Procname),
          &plm_cbSS_Procname);
        plm_retcode = SQLGetDiagField(
          plm_handle_type, plm_handle, plm_cRecNmbr,
          SQL_DIAG_SS_SRVNAME, &plm_SS_Srvname,
          sizeof(plm_SS_Srvname),
          &plm_cbSS_Srvname);
      }
      printf("szSqlState = %s\n",plm_szSqlState);
      printf("pfNativeError = %d\n",plm_pfNativeError);
      printf("szErrorMsg = %s\n",plm_szErrorMsg);
      printf("pcbErrorMsg = %d\n\n",plm_pcbErrorMsg);
      if (ConnInd) {
        printf("ODBCRowNumber = %d\n", plm_Rownumber);
        printf("SSrvrLine = %d\n", plm_Rownumber);
        printf("SSrvrMsgState = %d\n",plm_SS_MsgState);
        printf("SSrvrSeverity = %d\n",plm_SS_Severity);
        printf("SSrvrProcname = %s\n",plm_SS_Procname);
        printf("SSrvrSrvname = %s\n\n",plm_SS_Srvname);
      }
    }
    plm_cRecNmbr++; //Increment to next diagnostic record.
  } // End while.
}

Siva Raghupathy
ISV Program Manager
SQL Server Development Team