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_C_SLONG, SQL_INTEGER, 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_C_LONG, SQL_INTEGER, 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_C_LONG, SQL_INTEGER, 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 statement SQLSetConnectAttr Attribute(s)
SET SESSION READ ONLY SQL_ATTR_ACCESS_MODE SQL_MODE_READ_ONLY
SET SESSION READ WRITE SQL_ATTR_ACCESS_MODE SQL_MODE_READ_ONLY
SET SESSION ISOLATION LEVEL READ UNCOMMITTED SQL_ATTR_TXN_ISOLATION SQL_TXN_READ_UNCOMMITTED
SET SESSION ISOLATION LEVEL READ COMMITTED SQL_ATTR_TXN_ISOLATION SQL_TXN_READ_COMMITTED
SET SESSION ISOLATION LEVEL REPEATABLE READ SQL_ATTR_TXN_ISOLATIONS QL_TXN_REPEATABLE_READ
SET SESSION ISOLATION LEVEL SERIALIZABLE SQL_ATTR_TXN_ISOLATION
SQL_TXN_SERIALIZABLE
SET TRANSACTION READ ONLY N/A
SET TRANSACTION READ WRITE N/A
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED N/A
SET TRANSACTION ISOLATION LEVEL READ COMMITTED N/A
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ N/A
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE N/A
ENTER N/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 in the Mimer SQL Documentation Set and at www.microsoft.com

Graphic Element - Cube