title: Efficient way to test ODBC connection
body: <p>Our product is a TCP listening transaction processor. Incoming connections are assigned a thread to handle the connection and a DB connection to work with.</p>
  
  <p>Rather than costly approach of establishing new DB connection for each incoming client connection, we maintain a pool of database connections.</p>
  
  <p>The database connection pool fairly configurable: min / max sizes, growth rates, etc.</p>
  
  <p>Some details:</p>
  
  <ul>
  <li>Platform is Windows 2003 / 2008 R2  </li>
  <li>DB is SQL Server 2005 / 2008 R2  </li>
  <li>Connection method is ODBC  </li>
  <li>Programming language is C++  </li>
  </ul>
  
  <p><em>Finally</em>, the question:</p>
  
  <p>As the service could be running for several months without a restart, there's a real chance that some of the database connections in the pool become invalid. I want to have as quick a way as possible to test the validity of a given connection before assigning it to an incoming connection.</p>
  
  <p>Currently, I do this by executing the simple SQL statement "SELECT 123;", however I've found that this has significant negative performance impacts when parallel execution plans are used.</p>
  
  <p>Very briefly in code, what I'm doing is:</p>
  
  <pre><code>// ... at some point we decide pool needs another connection...
  
  // Set up database connection
  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &amp;env);
  SQLAllocHandle(SQL_HANDLE_DBC, env, &amp;conn);
  SQLDriverConnect(conn, 0, in_str, in_len, out_str, DIM(out_str), &amp;out_len, SQL_DRIVER_NOPROMPT);
  
  // 'conn' is placed in DB connection pool
  
  // ... some time later a new client connection comes in ...
  
  // Execute simple statement to test if 'conn' is still OK
  SQLAllocHandle(SQL_HANDLE_STMT, conn, &amp;stmt);
  SQLExecDirect(stmt, (SQLCHAR*)"SELECT 1;", SQL_NTS);
  
  // If 'conn' is OK, give it to incoming connection;
  // if not, get another connection from pool
  </code></pre>
  
  <p>Cheers,<br>
  Dave</p>