Our method is a TCP listening transaction processor. Incoming connections are designated a thread to handle connection along with a DB connection to utilize.
Instead of pricey approach of creating new DB connection for every incoming client connection, we conserve a pool of database connections.
The database connection pool fairly configurable: min / max dimensions, growth rates, etc.
- Platform is Home windows 2003 / 2008 R2
- DB is SQL Server 2005 / 2008 R2
- Connection technique is ODBC
- Programming language is C++
Finally, the question:
Because the service might be running for many several weeks with no restart, there is a real chance that a few of the database connections within the pool become invalid. I wish to have as quick a means as you possibly can to check the validity of the given connection before setting it for an incoming connection.
Presently, I actually do this by performing the easy SQL statement "Choose 123", however I have discovered that it has significant negative performance impacts when parallel execution plans are utilized.
Very briefly in code, what I am doing is:
// ... at some point we decide pool needs another connection... // Set up database connection SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env); SQLAllocHandle(SQL_HANDLE_DBC, env, &conn); SQLDriverConnect(conn, 0, in_str, in_len, out_str, DIM(out_str), &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, &stmt); SQLExecDirect(stmt, (SQLCHAR*)"SELECT 1;", SQL_NTS); // If 'conn' is OK, give it to incoming connection; // if not, get another connection from pool
Well the state strategy is SQLGetConnectAttr( SQL_ATTR_CONNECTION_DEAD )