Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Optimize ODBC
Categories: ODBC, Programming Examples
Introduction

In this article, we will discuss some techniques on how you can get the most out of ODBC and write fast applications that scale well.
The effort you put into writing good ODBC code is always well rewarded when your application is put in production.

Description

Array Insert
If you want to insert more than one row in a table, then we recommend that you use the ODBC array insert feature. This reduces server round trips and yields much faster insert performance as well as reducing demands on server resources as a whole.

Suppose that you want to insert 100 rows in a table. The following code snippet shows a typical way to do this:
{
SQLINTEGER val;
SQLINTEGER len = 0;

SQLPrepare(hstmt, "INSERT INTO T1 (C1) VALUES (?)", SQL_NTS);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_INTEGER, SQL_C_SLONG, 0,
0, &val, 0, &len);

for (val = 1; val <= 100; val++) {
SQLExecute(hstmt);
}
}
This code will result in 1+100 server round trips (one for the prepare).
You can reduce this to 2 server calls by using array insert as demonstrated below:
{
int i;
SQLINTEGER val[100];
SQLUSMALLINT rows_processed = 0;
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)100, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &rows_processed, 0);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_INTEGER, SQL_C_LONG, 0, 0,
&val, 0, NULL);
SQLPrepare(hstmt, "INSERT INTO T1 (C1) VALUES (?)", SQL_NTS);

/* fill array with values */
for (i = 0; i < 100; i++) {
val[i] = i;
}
SQLExecute(hstmt);
if (rows_processed != 100) {
/* possible error */
}
}
The example above uses the ODBC column-wise binding feature and
SQLSetStmtAttr(...,SQL_ATTR_PARAMSET_SIZE, ...) sets the array size of the parameter.
The statement attribute SQL_ATTR_PARAMS_PROCESSED_PTR is used to assign a deferred status variable which is filled by SQLExecute and contains the number of rows which were actually inserted.
If your application is deployed over a network with long ping times, using array insert will result in a huge improvement in speed.

Read Only Connections
We highly recommend that you set connections that only read data to read-only mode. This eliminates transaction checks. You set the connection to read-only mode with:
SQLSetConnectAttr(hdbc, SQL_ATTR_ACCESS_MODE, (SQLPOINTER)SQL_MODE_READ_ONLY, 0);
This has the same result as executing the "SET SESSION READ ONLY" statement.

Statement Caching
The server caches compiled statements and you can improve performance significantly if a statement doesn’t have to be recompiled. So, it is always good practice to use parameter markers in statements even if it's not obvious that the statement will be reused in the current user connection.
So, replace:
SQLExecDirect(hstmt, "SELECT C FROM T WHERE C = 10", SQL_NTS);
with:
SQLINTEGER val = 10;
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_INTEGER, SQL_C_LONG, 0, 0, &val, 0, NULL);
SQLExecDirect(hstmt, "SELECT C1 FROM T1 WHERE C1 = ?", SQL_NTS);
The compiled statement "SELECT C1 FROM T1 WHERE C1 = ?" is more likely to be reused than, "SELECT C FROM T WHERE C = 10" and if 1000 concurrent users prepare this statement, the first will be cached but probably not reused, the second will most likely be reused and does not have to be recompiled.
For more complex statements, the gains are even greater.

Prepare Statements
SQL statements that are executed more than once in an application should be prepared separately with SQLPrepare the first time and then executed with SQLExecute.
Note: we don’t usually recommend using SQLExecDirect as it can lead to unnecessary recompilation of statements.
It is always faster to execute a prepared statement with SQLExecute, even in the case where SQLExecDirect is used and the statement is cached, since it involves some calculations and actions to determine if a statement is already compiled.

Connection Pooling
Connecting to the database server can cause some overhead, especially over a slow network. Applications that connect and disconnect often should use the Connection Pooling feature that is provided by the ODBC Driver Manager.
The Driver Manager must know the state and attributes that the connections have. Because of this, you should not execute the following statements when you use Connection Pooling. Instead, make the appropriate SQLSetConnectAttr call. If you don’t, you might get a connection that has different characteristics to what you expected.

Statements you should not execute when using Connection Pooling:

SQL statementSQLSetConnectAttr Attribute(s)
SET SESSION READ ONLYSQL_ATTR_ACCESS_MODE SQL_MODE_READ_ONLY
SET SESSION READ WRITESQL_ATTR_ACCESS_MODE SQL_MODE_READ_ONLY
SET SESSION ISOLATION LEVEL READ UNCOMMITTEDSQL_ATTR_TXN_ISOLATION SQL_TXN_READ_UNCOMMITTED
SET SESSION ISOLATION LEVEL READ COMMITTEDSQL_ATTR_TXN_ISOLATION SQL_TXN_READ_COMMITTED
SET SESSION ISOLATION LEVEL REPEATABLE READSQL_ATTR_TXN_ISOLATIONS QL_TXN_REPEATABLE_READ
SET SESSION ISOLATION LEVEL SERIALIZABLESQL_ATTR_TXN_ISOLATION
SQL_TXN_SERIALIZABLE
SET TRANSACTION READ ONLYN/A
SET TRANSACTION READ WRITEN/A
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDN/A
SET TRANSACTION ISOLATION LEVEL READ COMMITTEDN/A
SET TRANSACTION ISOLATION LEVEL REPEATABLE READN/A
SET TRANSACTION ISOLATION LEVEL SERIALIZABLEN/A
ENTERN/A
LEAVE [RETAIN]N/A
START [WORK | TRANSACTION | BACKUP]N/A


Example

Here is an example of how you can use connection pooling:
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLSetEnvAttr(SQL_NULL_HANDLE, SQL_ATTR_CONNECTION_POOLING,
(SQLPOINTER)SQL_CP_ONE_PER_HENV, 0);
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);

for (;;) {
wait_for_event();

SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
SQLConnect(hdbc, "dsn", SQL_NTS, "user", SQL_NTS, "psw", SQL_NTS);
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
SQLExecDirect(hstmt,
"insert into t values (current_timestamp)", SQL_NTS);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}

An unused connection will be closed and removed from the pool after a certain length of time, the default timeout is 60 seconds. You can changed this in the ODBC Data Source Administrator by selecting the Connection Pooling tab and double-clicking on the MIMER driver.

Benefits

By using these techniques, you can reduce the response times for clients and increase overall database server performance thus allowing more concurrent users.

Links

Read more about ODBC at www.microsoft.com (search for 'odbc').
Last updated: 2010-08-26

 

Powered by Mimer SQL

Powered by Mimer SQL