ODBC driver performance on large result-sets

["Alistair Bayley" <alistair(at)abayley(dot)org>]

Attached are two C programs. They borh (I believe) execute the same
query against a PostgreSQL database on my WinXP laptop. The libpq
version runs quickly (in a second or less), while the ODBC version
takes about 40 seconds. Also, while the ODBC version is processing,
the memory usage for one of the postgres server processes goes up
quite a bit, to 140M.

Any suggestions as to what's causing the difference? And, is there
something I can do about it?

Thanks,
Alistair

#include <stdio.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>


SQLRETURN rc;

void printDiagRecs(SQLHANDLE h, SQLSMALLINT htype, SQLSMALLINT msgSeq)
{
  char strState[20];
  char strMsg[1025];
  SQLINTEGER errNum;
  SQLSMALLINT msgLen;
  SQLRETURN rc2;
  rc2 = SQLGetDiagRec(htype, h, msgSeq, strState, &errNum, strMsg, 1024, &msgLen);
  if (SQL_SUCCESS == rc2)
  {
    printf("%d %s %s\n", errNum, strState, strMsg);
    printDiagRecs(h, htype, msgSeq+1);
  }
  /* else { printf("printDiagRecs: rc2 = %d\n", rc2); } */
}

void checkRc()
{
  printf("rc = %d\n", rc);
  if (! SQL_SUCCEEDED(rc))
  {
  	exit(1);
  }
}

void checkError(SQLRETURN rc, SQLHANDLE h, SQLSMALLINT htype)
{
  //printf("checkError: rc = %d\n", rc);
  if (! SQL_SUCCEEDED(rc))
  {
  	printDiagRecs(h, htype, 1);
  	printf("There was an error - quitting.\n");
  	exit(1);
  }
}


SQLHANDLE allocHdl(SQLHANDLE parent, SQLSMALLINT htype)
{
  SQLHANDLE ptr;
  rc = SQLAllocHandle(htype, parent, &ptr);
  checkError(rc, parent, htype);
  return ptr;
}

SQLHENV allocEnv()  { return allocHdl(NULL, SQL_HANDLE_ENV); }
SQLHDBC allocConn(SQLHENV env)  { return allocHdl(env, SQL_HANDLE_DBC); }
SQLHSTMT allocStmt(SQLHDBC conn) { return allocHdl(conn, SQL_HANDLE_STMT); }


void setOdbcVer(SQLHENV env)
{
  rc = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void*) SQL_OV_ODBC3, 0);
  checkError(rc, env, SQL_HANDLE_ENV);
}


void odbc_connect(SQLHDBC conn, char* connstr)
{
  char outStr[1000];
  SQLSMALLINT outSz;
  rc = SQLDriverConnect(conn, NULL, connstr, strlen(connstr), outStr, 1000, &outSz, SQL_DRIVER_NOPROMPT);
  checkError(rc, conn, SQL_HANDLE_DBC);
}


void odbc_disconnect(SQLHDBC conn)
{
  rc = SQLDisconnect(conn);
  checkError(rc, conn, SQL_HANDLE_DBC);
}


void freeHdl(SQLSMALLINT htype, SQLHANDLE h)
{
  rc = SQLFreeHandle(htype, h);
  checkError(rc, h, htype);
}

void freeEnv(SQLHENV env) { freeHdl(SQL_HANDLE_ENV, env); }
void freeConn(SQLHDBC conn) { freeHdl(SQL_HANDLE_DBC, conn); }
void freeStmt(SQLHSTMT stmt) { freeHdl(SQL_HANDLE_STMT, stmt); }


void prepareStmt(SQLHSTMT stmt, char* sqltext)
{
  rc = SQLPrepare(stmt, sqltext, SQL_NTS);
  checkError(rc, stmt, SQL_HANDLE_STMT);
}

void bindParamDatetime(SQLHSTMT stmt, SQLUSMALLINT pos, char* val)
{
  SQLLEN inputSz;
  rc = SQLBindParameter(stmt, pos, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_TYPE_TIMESTAMP, 23, 0, val, strlen(val), &inputSz);
  checkError(rc, stmt, SQL_HANDLE_STMT);
}

void bindParamString(SQLHSTMT stmt, SQLUSMALLINT pos, char* val)
{
  SQLLEN inputSz;
  rc = SQLBindParameter(stmt, pos, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, val, strlen(val), &inputSz);
  checkError(rc, stmt, SQL_HANDLE_STMT);
}

void bindParamInt(SQLHSTMT stmt, SQLUSMALLINT pos, int val)
{
  SQLLEN inputSz;
  rc = SQLBindParameter(stmt, pos, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &val, 4, &inputSz);
  checkError(rc, stmt, SQL_HANDLE_STMT);
}

void executeStmt(SQLHSTMT stmt)
{
  rc = SQLExecute(stmt);
  checkError(rc, stmt, SQL_HANDLE_STMT);
}

void bindColString (SQLHSTMT stmt, SQLUSMALLINT pos, char* buffer, SQLLEN size, SQLLEN* outSz)
{
  rc = SQLBindCol(stmt, pos, SQL_CHAR, buffer, size, outSz);
  checkError(rc, stmt, SQL_HANDLE_STMT);
}

void bindColInt (SQLHSTMT stmt, SQLUSMALLINT pos, int* buffer)
{
  SQLLEN outSz;
  rc = SQLBindCol(stmt, pos, SQL_INTEGER, buffer, 4, &outSz);
  checkError(rc, stmt, SQL_HANDLE_STMT);
}

void bindColDatetime (SQLHSTMT stmt, SQLUSMALLINT pos, char* buffer, SQLLEN* outSz)
{
  rc = SQLBindCol(stmt, pos, SQL_TYPE_TIMESTAMP, buffer, 50, outSz);
  checkError(rc, stmt, SQL_HANDLE_STMT);
}


void fetch(SQLHSTMT stmt)
{
  rc = SQLFetch(stmt);
  if (rc == SQL_NO_DATA) return;
  checkError(rc, stmt, SQL_HANDLE_STMT);
}


void createConn(SQLHENV *penv, SQLHDBC *pconn, char* connstr)
{
  SQLHENV env;
  SQLHDBC conn;
  printf("allocEnv\n");
  env = allocEnv();
  printf("setOdbcVer\n");
  setOdbcVer(env);
  printf("allocConn\n");
  conn = allocConn(env);
  printf("odbc_connect\n");
  odbc_connect(conn, connstr);
  *penv = env;
  *pconn = conn;
}

char* manyRows = " \
select 1 from \
  ( select 1 from tdual union select 0 from tdual) t1 \
, ( select 2 from tdual union select 0 from tdual) t2 \
, ( select 3 from tdual union select 0 from tdual) t3 \
, ( select 4 from tdual union select 0 from tdual) t4 \
, ( select 5 from tdual union select 0 from tdual) t5 \
, ( select 6 from tdual union select 0 from tdual) t6 \
, ( select 7 from tdual union select 0 from tdual) t7 \
, ( select 8 from tdual union select 0 from tdual) t8 \
, ( select 9 from tdual union select 0 from tdual) t9 \
, ( select 10 from tdual union select 0 from tdual) t10 \
, ( select 11 from tdual union select 0 from tdual) t11 \
, ( select 12 from tdual union select 0 from tdual) t12 \
, ( select 13 from tdual union select 0 from tdual) t13 \
, ( select 14 from tdual union select 0 from tdual) t14 \
, ( select 15 from tdual union select 0 from tdual) t15 \
, ( select 16 from tdual union select 0 from tdual) t16 \
";


char* manyRows2 = " \
select 1 from \
  ( select 1 union select 0) t1 \
, ( select 2 union select 0) t2 \
, ( select 3 union select 0) t3 \
, ( select 4 union select 0) t4 \
, ( select 5 union select 0) t5 \
, ( select 6 union select 0) t6 \
, ( select 7 union select 0) t7 \
, ( select 8 union select 0) t8 \
, ( select 9 union select 0) t9 \
, ( select 10 union select 0) t10 \
, ( select 11 union select 0) t11 \
, ( select 12 union select 0) t12 \
, ( select 13 union select 0) t13 \
, ( select 14 union select 0) t14 \
, ( select 15 union select 0) t15 \
, ( select 16 union select 0) t16 \
";



SQLHDBC runTest(char *connstr)
{
  SQLHENV env;
  SQLHDBC conn;
  SQLHSTMT stmt;

  createConn(&env, &conn, connstr);
  printf("allocStmt\n");
  stmt = allocStmt(conn);

  //printf("prepareStmt\n");
  //prepareStmt(stmt, "create table tdual (dummy varchar(1) primary key)");
  //printf("executeStmt\n");
  //executeStmt(stmt);
  //printf("prepareStmt\n");
  //prepareStmt(stmt, "insert into tdual values ('X')");
  //printf("executeStmt\n");
  //executeStmt(stmt);

  printf("prepareStmt\n");
  prepareStmt(stmt, manyRows2);
  printf("executeStmt\n");
  executeStmt(stmt);

  int col1Buf;
  SQLLEN col1Sz;
  bindColInt(stmt, 1, &col1Buf);

  fetch(stmt);
  int rowcount = 0;
  while (rc == SQL_SUCCESS)
  {
    rowcount++;
    fetch(stmt);
  }

  printf("rowcount: %d\n", rowcount);

  printf("freeStmt\n");
  freeStmt(stmt);
  printf("disconnect\n");
  odbc_disconnect(conn);
  printf("freeConn\n");
  freeConn(conn);
  printf("freeEnv\n");
  freeEnv(env);
}


int main(int argc, char** argv)
{
  runTest("DSN=postgres");
}

#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"

static void exit_nicely(PGconn *conn)
{
   PQfinish(conn);
   exit(1);
}

void check_error(PGconn *conn, PGresult *res, ExecStatusType rc, char *msg)
{
   if (PQresultStatus(res) != rc)
   {
       /* fprintf(stderr, msg, PQerrorMessage(conn)); */
       fprintf(stderr, "%s: %s\n", msg, PQerrorMessage(conn));
       PQclear(res);
       exit_nicely(conn);
   }
}

char* manyRows = " \
select 1 from \
  ( select 1 from tdual union select 0 from tdual) t1 \
, ( select 2 from tdual union select 0 from tdual) t2 \
, ( select 3 from tdual union select 0 from tdual) t3 \
, ( select 4 from tdual union select 0 from tdual) t4 \
, ( select 5 from tdual union select 0 from tdual) t5 \
, ( select 6 from tdual union select 0 from tdual) t6 \
, ( select 7 from tdual union select 0 from tdual) t7 \
, ( select 8 from tdual union select 0 from tdual) t8 \
, ( select 9 from tdual union select 0 from tdual) t9 \
, ( select 10 from tdual union select 0 from tdual) t10 \
, ( select 11 from tdual union select 0 from tdual) t11 \
, ( select 12 from tdual union select 0 from tdual) t12 \
, ( select 13 from tdual union select 0 from tdual) t13 \
, ( select 14 from tdual union select 0 from tdual) t14 \
, ( select 15 from tdual union select 0 from tdual) t15 \
, ( select 16 from tdual union select 0 from tdual) t16 \
";


char* manyRows2 = " \
select 1 from \
  ( select 1 union select 0) t1 \
, ( select 2 union select 0) t2 \
, ( select 3 union select 0) t3 \
, ( select 4 union select 0) t4 \
, ( select 5 union select 0) t5 \
, ( select 6 union select 0) t6 \
, ( select 7 union select 0) t7 \
, ( select 8 union select 0) t8 \
, ( select 9 union select 0) t9 \
, ( select 10 union select 0) t10 \
, ( select 11 union select 0) t11 \
, ( select 12 union select 0) t12 \
, ( select 13 union select 0) t13 \
, ( select 14 union select 0) t14 \
, ( select 15 union select 0) t15 \
, ( select 16 union select 0) t16 \
";


int main(int argc, char **argv)
{
   const char *conninfo;
   PGconn     *conn;
   PGresult   *res;
   int         nFields;
   int         i,
               j;
       Oid paramTypes[10];

   /*
    * If the user supplies a parameter on the command line, use it as the
    * conninfo string; otherwise default to setting dbname=postgres and using
    * environment variables or defaults for all other connection parameters.
    */
   if (argc > 1)
       conninfo = argv[1];
   else
       conninfo = "user=postgres";

   /* Make a connection to the database */
   conn = PQconnectdb(conninfo);

   /* Check to see that the backend connection was successfully made */
   if (PQstatus(conn) != CONNECTION_OK)
   {
       fprintf(stderr, "Connection to database failed: %s"
         , PQerrorMessage(conn));
       exit_nicely(conn);
   }

   res = PQexec(conn, manyRows2);
   check_error(conn, res, PGRES_TUPLES_OK, "FETCH ALL failed");

   printf("start query\n");

   /* next, print out the rows */
   for (i = 0; i < PQntuples(res); i++)
   {
       j = (int) PQgetvalue(res, i, 0);
   }

   printf("rowcount: %d\n", PQntuples(res));
   PQclear(res);

   /* close the connection to the database and cleanup */
   PQfinish(conn);

   return 0;
}