How to Block fetch over a Default Result Set
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
#include
#include
#include
#include
#include
#include
#include
#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