Easysoft ODBC-ODBC Bridge performance white paper

On Linux and UNIX, there are two versions of oobping. oobpings (a statically linked version) and oobpingd (a dynamically linked version linked against the libesoobclient shared object). These programs are located in installdir/easysoft/bin. To use oobpingd, you may need to set and export LD_LIBRARY_PATH, LD_RUN_PATH, or LIBPATH to include installdir/easysoft/oob/client and installdir/easysoft/lib.

oobping has the following command line:

oobping [-h host | -d ODBC_connection_string] {-t port}
        {-u osuser} {-p ospassword} {-e}

where:

In ODBC-ODBC Bridge 1.1.0.0 and later, oobping has the -e option, which times the requested operation. This can be invaluable in diagnosing a slow connection and determining which phase the problem is occurring in.

If you don't have a version that supports the -e switch, use the UNIX time function in front of the oobping command instead.

A simple example is:

==========
oobping -e -h myserver -t 8888
Host: myserver, Port: 8888
Attempting connection...OK
Examining Server...
    ODBC-ODBC Bridge server Version: 1.1.0.00
    ODBC-ODBC Bridge server Name: ODBC-ODBC Bridge
Time for execution: 0.16s
==========

If this is repeated with -u and -p, you can work out the extra time required to perform the operating system log on:

==========
oobping -e -h myserver -u myosuser -p mypassword
Host: myserver, Port: 8888
Attempting connection...OK
Examining Server...
    ODBC-ODBC Bridge server Version: 1.1.0.00
    ODBC-ODBC Bridge server Name: ODBC-ODBC Bridge
Trying to authenticate...OK
Time for execution: 0.52s
==========

This example clearly demonstrates the extra time required to authenticate a user.

The next example shows a full connection to the ODBC-ODBC Bridge server. The example command uses ODBC-ODBC Bridge server authentication and makes a remote ODBC connection:

==========
oobping -e -d "DSN=test;"
Using Connection string :
DSN=test;
Connected OK

01000:1:5701:[NetConn: 0172c620][Microsoft][ODBC SQL Server Driver]
  [SQL Server]Changed database context to 'test'.
01000:2:5703:[NetConn: 0172c620][Microsoft][ODBC SQL Server Driver]
  [SQL Server]Changed language setting to us_english.
OutConnectionString:
DSN=test;SERVER=myserver;TRANSPORT=tcpip;PORT=8888;TARGETDSN=test;
  UID=dbuser;PWD=dbauth;LOGONUSER=me;LOGONAUTH=mypassword;

Connected to database:  test
DBMS Name:  Microsoft SQL Server
Driver Name:  esoobclient
Driver Version:  01.00.0043
Disconnecting
Time for execution: 0.78s
==========

The output demonstrates the extra time required to connect to the remote ODBC data source.

Simple SQL advice

1. Only select the columns you need in your SQL queries. Generating result sets containing columns you don't need just wastes time in the ODBC driver. Worse still, some ODBC drivers have no choice but to read the column data from the database even though you never access it. Doing this also helps protect your application from table changes (for example, the addition of a new column).
2. Try batching your SQL. For example:

   select * from table1; select * from table2

   You can use SQLMoreResults to get the second result set.

3. If your database supports stored procedures, you can often realise performance gains by coding your SQL statements into them. In most cases:
   • The SQL statements are parsed and compiled once when the procedure is created and not when the procedure is used by your application.
   • Often a call to a stored procedure requires sending less data over the network between the ODBC driver and the database than the equivalent SQL.

Applications and interfaces

Using ODBC directly

In ODBC, there are multiple ways of retrieving query result sets and inserting data. Picking the right one for your application is crucial in determining throughput. Here, we examine the simplest ways of retrieving result sets and inserting data and then gradually improve the methods to get more speed. The code examples are pseudo code.

To provide meaningful results we have used a table containing 5 columns: an INTEGER, a CHAR(30), a VARCHAR(255), a TIMESTAMP, and a FLOAT. The table is created with 50000 rows.

Retrieving result sets

The simplest mechanism for issuing a query and retrieving a result set is:

SQLExecDirect("query producing a result set")
columns = SQLNumResultCols()
while (SQLFetch() succeeds)
{
   for (col = 1; col <= columns; col++)
   {
     column_data = SQLGetData(col, SQL_C_CHAR)
   }
}

Here, all the columns are retrieved as SQL_C_CHARs so the database has to convert the INTEGER column and the TIMESTAMP column to a string.

The C code for the above algorithm took 47 seconds to run.

One possible change that might speed the code up is to fetch the columns as their native types. For example, the INTEGER as a SQL_C_LONG, the TIMESTAMP as a SQL_C_TIMESTAMP, and the FLOAT as a SQL_C_FLOAT. This avoids the ODBC driver converting the native type to a SQL_C_CHAR string. The pseudo code for this looks like:

SQLINTEGER idata
SQLFLOAT fdata
SQL_TIMESTAMP_STRUCT ts
SQLCHAR cdata1[256]
SQLCHAR cdata2[256]

idata = SQLGetData(1, SQL_C_LONG);
cdata = SQLGetData(2, SQL_C_CHAR);
cdata2 = SQLGetData(3, SQL_C_CHAR);
ts = SQLGetData(4, SQL_C_TIMESTAMP);
fdata = SQLGetData(5, SQL_C_FLOAT);

However, when we run this version of the program, we discover that it takes 62 seconds to run (longer). The main reasons for this are:

1. The float's representation on the server platform is not necessarily the same as the client platform, so ODBC-ODBC Bridge has to convert them from the server's representation to the client's representation.
2. The timestamp is a structure that the ODBC-ODBC Bridge splits up to transfer over the network and then has to put the timestamp back together at the client end.

Doubles are the same as floats in this respect and SQL_DATEs, SQL_TIMEs, and SQL_NUMERICs are the same as timestamps.

One way we might be able to speed things up is to use ODBC bound columns. With bound columns, you bind memory addresses to each column and when SQLFetch is called the entire row is returned in one go without having to call SQLGetData individually for each column. The pseudo code for this is:

SQLINTEGER idata
SQLFLOAT fdata
SQL_TIMESTAMP_STRUCT ts
SQLCHAR cdata1[256]
SQLCHAR cdata2[256]

SQLBindCol(1, &idata)
SQLBindCol(2, cdata1)
SQLBindCol(3, cdata2)
SQLBindCol(4, &ts)
SQLBindCol(5, &fdata)

SQLExecDirect("query producing a result set")
while (SQLFetch() succeeds)
{
  ; SQLFetch has placed the column data into our variables
}

The time for this implementation is 22 seconds, half of what we started with. The main reason for this being a lot faster is that the ODBC-ODBC Bridge server can send an entire row back to the client in one network call. A good rule of thumb for the ODBC-ODBC Bridge is: reducing the number of remote ODBC calls speeds the application up.

The last example used column-wise binding but we only retrieved one row at a time. ODBC has a facility for returning more than one row of column-bound columns at a time. To turn on this facility, you call SQLSetStmtAttr with an argument of SQL_ATTR_ROW_ARRAY_SIZE and pass in the number of rows you want in one go.

The pseudo code looks like this:

define ROWS 10
SQLINTEGER idata[ROWS]
SQLFLOAT fdata[ROWS]
SQL_TIMESTAMP_STRUCT ts[ROWS]
SQLCHAR cdata1[ROWS][256]
SQLCHAR cdata2[ROWS][256]

SQLSetStmtAttr(SQL_ATTR_ROW_ARRAY_SIZE, ROWS)

SQLBindCol(1, &idata)
SQLBindCol(2, cdata1)
SQLBindCol(3, cdata2)
SQLBindCol(4, &ts)
SQLBindCol(5, &fdata)


SQLExecDirect("query producing a result set")
while (SQLFetch() succeeds)
{
  ; SQLFetch has placed the column data into our variables
}

When we run this implementation, the time to retrieve the 50000 rows goes down to 5 seconds, one ninth the time of our first implementation.

The speed increase of using bound columns is usually greater the more columns there are in the result set. In addition, setting SQL_ATTR_ROW_ARRAY_SIZE greater than 1 tends to provide larger amounts of consecutive bytes for column data, which the ODBC-ODBC Bridge will favour for data compression when transferring the data over the network.

There is one other option, which is row-wise binding of columns. In row-size binding, you create a structure with one field for each column, ask for row-wise binding, and then pass the address of each field in the structure to SQLBindCol and the size of the structure. It is basically the same as column-wise binding except that you set SQL_ATTR_ROW_ARRAY_SIZE to a value greater than 1. With column-wise binding, each address passed to SQLBindCol is supposed to be the base address of an array of that column type. With row-wise binding, you create an array of your row structures.

For the ODBC-ODBC Bridge, row-wise binding is a little slower. The ODBC-ODBC Bridge client does not actually pass the call to set SQL_BIND_TYPE through to the driver when the type is anything other than xxx_BIND_BY_COLUMN. The ODBC-ODBC Bridge leaves the bind type set to column-wise binding, notes the application wants row-wise binding and converts the returned data to row-wise. This makes no difference to any application that uses row-wise binding as it works as ODBC dictates, but it's one of the few instances in the ODBC-ODBC Bridge where what the application asks for (in ODBC terms) is not mirrored at the server side.

Inserting data

These examples use the same table structure as in the previous section, but this time we want to populate the table with 50000 rows.

The simplest pseudo code to populate our table is:

SQLCHAR sql[1024]
for (i = 1; i <= 50000; i++)
{
    sql =  "INSERT INTO perf VALUES " +
          "(" + i + ", 'this is a char thirty', " +
          "'this is a varchar 255', " +
          "{ts '2002-08-01 11:12:13.123'}, " + i * 1.1 + ")"
    SQLExecDirect(sql);
}

Here, we construct the SQL INSERT statement once for each row and call SQLExecDirect once for each row. When we run this code, it takes 5 minutes and 10 seconds. Why? The first problem with this code is the database has to parse and prepare the INSERT statement once per row; this can be time consuming.

A much more efficient method is to use a parameterised insert. Here, parameters are using in place of the column data. Instead of the above SQL, we would use:

INSERT INTO PERF VALUES (?,?,?,?,?)

We prepare this SQL once (by calling SQLPrepare, bind the parameters with SQLBindParameter, and then just keep calling SQLExecute, changing the data we bound each time. For example:

SQLINTEGER idata
SQLCHAR cdata1[256]
SQLCHAR cdata2[256]
SQL_TIMESTAMP_STRUCT ts
SQLREAL fdata

SQLPrepare(INSERT INTO perf VALUES (?,?,?,?,?)

SQLBindParameter(1, idata)
SQLBindParameter(2, cdata1)
SQLBindParameter(3, cdata2)
SQLBindParameter(4, ts)
SQLBindParameter(5, fdata)

for (i = 0; i <= 50000; i++)
{
    set idata, cdata1, cdata2, ts, fdata to whatever values you
    want to insert
    SQLExecute()
}

However, if you run this through the ODBC-ODBC Bridge, you'll find that it does not make any real difference. The reason for this is that in the first case with the straightforward SQL INSERT, you're making one network call per INSERT whereas with the parameterised INSERTs, there are two network calls; one for SQLExecute and one to send the parameters. (The ODBC-ODBC Bridge also has some extra work to pack up the parameters.)

With many databases, a very easy speed up if you are doing bulk inserts, copying large quantities of data from one place to another, is to turn autocommit off and commit the inserts at the end. This can half the insert time. For example:

SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF)
<pseduo code above>
SQLEndTran(SQL_HANDLE_DBC, connection_handle, SQL_COMMIT)

When we apply this change and run to Microsoft SQL Server, DB2, or Oracle, the time comes down to around 2 minutes 30 seconds; this is nearly half of what we started with. There are two things to remember about this:

1. If you only commit at the end of all your inserts then if you abort when any SQLExecute fails, none of your inserts are committed.
2. Individual inserts will not be visible to other users until you have finished all your inserts and committed them. If this bothers you then perhaps you can still get improved speed by committing less often.

One final change we can make is to use arrays of parameters (as we did in row fetching). To do this, you call SQLSetStmtAttr and set the SQL_ATTR_PARAMSET_SIZE to a number greater than 1. For example:

#define ROWS 10
SQLINTEGER idata[ROWS]
SQLCHAR cdata1[ROWS][256]
SQLCHAR cdata2{ROWS][256]
SQL_TIMESTAMP_STRUCT ts[ROWS]
SQLREAL fdata[ROWS]

SQLSetStmtAttr(SQL_ATTR_PARAMSET_SIZE, ROWS)

SQLPrepare(INSERT INTO perf VALUES (?,?,?,?,?)

SQLBindParameter(1, idata)
SQLBindParameter(2, cdata1)
SQLBindParameter(3, cdata2)
SQLBindParameter(4, ts)
SQLBindParameter(5, fdata)

for (i = 0; i <= 5000; i++)
{
    set idata, cdata1, cdata2, ts, fdata to whatever values you
    want to insert (* 10)
    SQLExecute()
}

Note this code only does 5000 iterations as you are inserting 10 rows at a time. The time for this version was 34 seconds. If you take this further, inserting 50 rows at a time, it falls again to 18 seconds, but there is usually a limit at which the speed increase bottoms out. This final figure works out as 2777 inserts a second.

Cursors

Overview

In ODBC, a cursor is automatically opened for each result set and an application retrieves rows through the cursor using SQLFetch, SQLFetchScroll, or SQLExtendedFetch. An application can change the cursor using SQLSetStmtAttr before executing a SQL statement, but it should be noted that the ODBC driver may change these settings as it sees fit. If this is the case, the ODBC driver returns an "option value changed" diagnostic.

The default settings for cursor options are:

SQL_ATTR_CURSOR_TYPE - SQL_CURSOR_FORWARD_ONLY
SQL_ATTR_CONCURRENCY - SQL_CONCUR_READ_ONLY
SQL_ROWSET_SIZE/SQL_ATTR_ROW_ARRAY_SIZE - 1

An application can change the SQL_ATTR_CURSOR_TYPE to one of the following types:

1. Static cursors (SQL_CURSOR_STATIC).

   In a static cursor. the result set is unchanging once a particular row has been fetched (for some ODBC drivers, the result set is built when the cursor is opened). The cursor does not reflect any changes made in the database with respect to fetched rows in the result set or values in the columns. A static cursor always returns the result set as it was at the time the cursor was opened or a row was fetched. Any rows added or deleted or column data changed since the cursor was created that satisfy the conditions of the query are not seen until the cursor is reopened. Static cursors are read-only.

2. Dynamic cursors (SQL_CURSOR_DYNAMIC).

   Dynamic cursors are the opposite of static cursors; they reflect all changes made to the rows in the result set as the user scrolls around the cursor. The data values and rows in the cursor can change dynamically on each fetch.

   The cursor shows all deleted, inserted, and updated records, whether they were made by the application with the dynamic cursor or by something else.

   You cannot use fetch absolute to position the cursor somewhere in the result set, as the size of the result set and the position of the rows within the result set are not constant.

3. Forward-only cursors (SQL_CURSOR_FORWARD_ONLY).

   This is the same as a dynamic cursor except that you can only fetch the rows in the result set in sequence from the start to the end of the cursor.

4. Keyset-driven (SQL_CURSOR_KEYSET_DRIVEN).

   With keyset-driven cursors, the rows in the result set and their order is fixed when the cursor is opened. Keyset-driven cursors are controlled using a set of unique identifiers (keys) known as the keyset. The keys are built from the set of columns that uniquely identify the rows. The keyset is the set of all the key values that made up the rows in the result set when the cursor was opened.

   Changes to the values of columns that are not in the keyset (made by the current application or something else) are reflected in the rows as the cursor is scrolled through. Inserts are not reflected (the cursor needs to be closed and reopened to reflect inserts). Rows that are deleted generate an invalid cursor position error if they are fetched. Likewise, updates to key columns operate like a delete of the old key followed by an insert on a new key and so the cursor does not see the new row.

5. Mixed cursors, for example, SQL_CURSOR_KEYSET_DRIVEN and SQL_KEYSET_SIZE=n.

   ODBC cursors support the concept of a rowset, which is the number of rows returned on an individual call to a fetch function. For example, an application that is capable of displaying 20 rows at a time in its window might set the rowset size to 20, so that each fetch returns one window's depth of rows.

Cursor operations are also affected by the concurrency options set by the application. These are SQL_CONCUR_READONLY, SQL_CONCUR_VALUES, SQL_CONCUR_ROWVER, and SQL_CONCUR_LOCK. SQL_CONCURRENCY affects how the database engine locks rows in the result set.

The locking behavior of cursors is based on an interaction between the concurrency options and the transaction isolation level set by the application. You set the transaction isolation level with SQL_ATTR_TXN_ISOLATION, which can be set to:

• Read committed (SQL_TXT_READ_COMMITTED)
• Read uncommitted (SQL_TXN_READ_UNCOMMITTED)
• Repeatable read (SQL_TXN_REPEATABLE_READ_COMMITED)
• Serializable (SQL_TXT_SERIALIZABLE)

In addition, an application can specify the characteristics or a cursor rather than specifying the cursor type. For example, scrollability by setting SQL_ATTR_CURSOR_SCROLLABLE or sensitivity by setting SQL_ATTR_CURSOR_SENSITIVITY. The driver then has to choose the cursor type that most efficiently provides that characteristic. Whenever an application sets any of the SQL_ATTR_CONCURRENCY, SQL_ATTR_CURSOR_SCROLLABLE, SQL_ATTR_CURSOR_SENSITIVITY, or SQL_ATTR_CURSOR_TYPE statement attributes, the ODBC driver makes any required change to the other statement attributes in this set of four, so their values remain consistent.

An application that sets statement attributes to specify both a cursor type and a cursor characteristic is running the risk of obtaining a cursor that's not the most efficient method available in that ODBC driver.

Choice of a cursor depends largely on how the database engine implements that cursor and the correct choice can have a dramatic effect on performance and locking. As a basic rule, use the cursor and concurrency level that has sufficient functionality for your application and no more. However, there are other considerations depending on your database engine such as:

• Size of result set.
• Performance of the cursor.
• Requirement for scrolling or positioning.
• Desired level of modification visibility to other applications.

Cursor library

The cursor library built into the ODBC Driver Manager attempts to imitate cursors when the ODBC driver does not have support for them. The principal settings are i) always use the cursor library (no matter what the ODBC driver supports) and ii) use the cursor library when required (if the ODBC driver does not support the required cursor). The cursor library only comes into play when an application binds columns. If the application binds columns and wants to move about the result set with SQLFetchScroll (for instance) then the ODBC Driver Manager will keep a copy of the rows retrieved in bound columns so the application can move around the result set even though the ODBC driver does not support this functionality.

Try to avoid the cursor library.

If your ODBC driver has support for the cursor you require, asking the Driver Manager to use the cursor library with SQLSetConnectAttr(SQL_USE_CURSORS, SQL_CUR_USE_ODBC) is a waste of time. When you use SQL_CUR_USE_ODBC, the cursor library will generally convert all calls to SQLFetchScroll and SQLExtendedFetch into SQLFetch calls and will often ignore SQL_ROWSET_SIZE and SQL_ATTR_ROW_ARRAY_SIZE when calling the ODBC driver. If you set SQL_ROWSET_SIZE or SQL_ATTR_ROW_ARRAY_SIZE to a value greater than 1 (to get extra performance) but then stipulate you want to use the Driver Manager cursor library, each call your application makes to SQLFetchxxx to get n rows at a time will be converted into n calls to SQLFetch.

Miscellaneous tips

1. Avoid repeatedly calling SQLDescribeParam and SQLDescribeCol on the same SQL. It's much better to cache these results yourself as these metadata calls can be expensive.
2. If possible, leave calling SQLDescribeParam and SQLDescribeCol calls until after calling SQLExecute, as in some ODBC drivers, this causes extra work.
3. Try to avoid data conversions in SQLGetData, SQLBindCol, and so on. For example, calling SQLGetData and asking for a SQL_C_CHAR for a column that is a CHAR.

Perl

DBD::ODBC

This section discusses the use of Perl, the Perl modules DBI and DBD::ODBC, the unixODBC Driver Manager, and the ODBC-ODBC Bridge, which provides the ODBC driver. Perl DBI and DBD::ODBC are continually being improved, so you should check you have latest versions.

The first thing we should point out is that you need to be realistic about your expectations with Perl. You are never going to get the kind of insert or select speeds from a Perl script that we have demonstrated with compiled C code in the previous sections.

DBI provides a generic interface to databases. DBD::ODBC is the DBI driver for accessing ODBC drivers. DBD::ODBC is written to work with any ODBC driver and as such it's difficult to provide specific optimisations like the ones in the previous ODBC section.

Some of the methods used by DBD::ODBC are not the most efficient use of ODBC for specific situations but are a side-effect of the generic nature of DBD::ODBC. For example, when some SQL is executed DBD::ODBC does not know if it was a SELECT statement or an INSERT statement, so a typical insert is followed by SQLNumResultCols, SQLRowCount, and SQLMoreResults calls, all of which are redundant. This is not a criticism of DBD::ODBC. It's simply a recognition of the difficulty of writing a super-efficient DBD module without knowing what the SQL is. These additional calls to SQL functions cause remote procedure calls to the ODBC-ODBC Bridge server, which slow Perl scripts down.

As an example, using the same table described in the earlier ODBC section, the following code took 15 minutes (55 inserts per second):

use DBI;
my $dbh = DBI->connect() || die "connect";
my $i;
my $sql;

for ($i = 1; $i <= 50000; $i++) {
    $sql = "INSERT INTO perf VALUES (" . $i .
        ", 'this is a char thirty', " .
        "'this is a varchar 255'," .
        "{ts '2002-08-01 11:12:13.123'}, " .
        $i*1.1 . ")";
    $dbh->do($sql);
}
$dbh->disconnect()

Unlike the C examples in the previous section, using parameterised inserts in Perl is quite a bit faster, so the above code can be written as:

use DBI;

my $dbh = DBI->connect() || die "connect";

my $sql = "INSERT INTO perf VALUES (?,?,?,?,?)";
my $stmt = $dbh->prepare($sql);

$stmt->bind_param(2, 'this is a char thirty');
$stmt->bind_param(3, 'this is a varchar 255');
$stmt->bind_param(4, '2008-08-01 11:12:13.123');

for (my $i = 1; $i <= 50000; $i++) {
    $stmt->bind_param(1, $i);
    $stmt->bind_param(5, $i*1.1);
    $stmt->execute();
}
$dbh->disconnect();

and the time for the 50000 inserts drops to 10 minutes (83 inserts a second). Using arrays of bound parameters like this:

use DBI;

my $dbh = DBI->connect()|| die "connect";

my $i;
my $sql;
$sql = "INSERT INTO perf VALUES (?,?,?,?,?)";
my $stmt = $dbh->prepare($sql);
my (@data1, @data2, @data3, @data4, @data5);

for ($i = 0; $i < 10; $i++)
{
    $data1[$i] = $i;
    $data2[$i] = "this is a char thirty";
    $data3[$i] = "this is a varchar 255";
    $data4[$i] = "2002-08-01 11:12:13.123";
    $data5[$i] = $i * 1.1;
}
$stmt->bind_param_array(1, \@data1);
$stmt->bind_param_array(2, \@data2);
$stmt->bind_param_array(3, \@data3);
$stmt->bind_param_array(4, \@data4);
$stmt->bind_param_array(5, \@data5);

$data2 = "this is a char thirty";
$data3 = "this is a varchar 255";
$data4 = "2002-08-01 11:12:13.123";

my %status;

for ($i = 1; $i <= 5000; $i++) {
    $data5 = $i * 1.1;
    $stmt->execute_array(\%status);
}
$dbh->disconnect();

does not seem to make any real difference. This is probably because at this time, array bound parameters are actually executed in ODBC terms with SQL_PARAMSET_SIZE = 1, so it is unsurprising that there is no speed increase when compared with the previous example.

Using the same table of 50000 rows and writing simple Perl to fetch the data, we might use:

#!/usr/bin/perl
use DBI;
my $dbh = DBI->connect()|| die "connect";

my $sql;
$sql = "SELECT * FROM perf";
$stmt = $dbh->prepare($sql);
$stmt->execute();
my @row;
while(@row = $stmt->fetchrow_array()){};
$dbh->disconnect();

This takes around 93 seconds (537 rows a second).

Using bound columns, we would write:

use DBI;
my $dbh = DBI->connect()|| die "connect";

my $sql;
$sql = "SELECT * FROM perf";
$stmt = $dbh->prepare($sql);
my $nf = $sth->{NUM_OF_FIELDS};
my @cols;
for (my $i = 1; $i <= $nf; $i++) {
    $sth->bind_col($i, \$cols[$i]);
}
$stmt->execute();
my @row;
while($stmt->fetch()){};
$dbh->disconnect();

but this appears to take a similar amount of time. This is no surprise, for the same reasons given in the earlier ODBC section.

So, the conclusion here is that if you want the best insert or fetch speed use C, but if you want the flexibility and portability of Perl, you need to accept it's going to be slower.

DBD::Proxy

For anyone wanting a Perl-only solution, DBD::Proxy offers similar functionality to that of the ODBC-ODBC Bridge. DBD::Proxy transfers DBI calls and data between the client and server. Since it is only doing the DBI calls, and the ODBC-ODBC Bridge is doing all the ODBC calls beneath DBI, you would expect the ODBC-ODBC Bridge to be slower than DBD::Proxy, however this is not usually the case.

Using the same tables listed in the ODBC section in this document, the results below are for 50000 inserts and a select on all columns for the 5000 rows to an ISAM database using the Easysoft ODBC driver. All values are in seconds and the average of 3 runs.

CPUu and CPUs are user and system CPU times.

When these numbers are used to calculate the inserts per second and rows retrieved per second:

As we have shown earlier, turning autocommit off and committing all 50000 inserts at the end speeds up the inserts per second with most databases, but this works equally for both Easysoft ODBC-ODBC Bridge and DBD::Proxy.

DBD::Proxy does have some optimisation settings like proxy_no_finish, proxy_quote, and RowCacheSize. The ODBC-ODBC Bridge has block fetch mode, but this does not apply to ODBC 2.0 applications (which DBD::ODBC is before version 0.45_2) and after 0.45_2 DBD::ODBC binds the columns itself anyway.

With the following settings the select test was run again:

proxy_quote = "local"
proxy_no_finish = 1
RowCacheSize = 100

which puts the final rows retrieved per second at:

So, in their default and best configurations we could find, the ODBC-ODBC Bridge was quicker than DBD::Proxy when inserting. The ODBC-ODBC Bridge is also quicker than DBD::Proxy under its default configuration when retrieving rows. However, if memory constraints permit you to use RowCacheSize with higher values, DBD::Proxy is quicker than ODBC-ODBC Bridge at row retrieval.

We started by saying ODBC-ODBC Bridge RPCs every ODBC call but DBD::Proxy acts at a higher level and only has to RPC DBI calls. One area DBD::Proxy might possibly do a lot better is over encrypted connections. Our experience is that encryption of these sorts of data streams can be time consuming. The more network calls made, the worse the effect of encryption becomes. There are so many different encrpytion tunnels, (for example, SSH, zebedee, and so on) we have not verified this.

PHP

Most of the comments made under the Perl section apply equally to PHP. PHP is however different from Perl DBI DBD::ODBC in that it provides a PHP interface to ODBC. ODBC functions are wrapped in C code written for PHP. However, a lot of the flexibility of ODBC is hidden from the PHP user and this reduces the opportunity for tuning PHP code for the fastest ODBC access. PHP always uses bound columns when retrieving a result set and all our experiments showed very little difference in speed between different insertion and retrieval methods.

Having said that, PHP is quite fast. The standard table we have used throughout this document of 50000 rows containing 5 mixed type columns is retrieved in 90 seconds (555 rows a second).

If you're using PHP with Microsoft SQL Server, there are two important points you should note:

1. PHP attempts to set the cursor type in any ODBC driver that successfully returns SQL_FD_FETCH_ABSOLUTE in the SQLGetInfo call for SQL_FETCH_DIRECTION. The cursor that's requested depends on the version of PHP. Older versions (around 4.0.x and 4.1.x) set the cursor to SQL_CURSOR_DYNAMIC but later versions (4.2.x) seem to set the cursor to SQL_CURSOR_FORWARD_ONLY.

   We believe the original reason for setting the cursor to SQL_CURSOR_DYNAMIC was because, for Microsoft SQL Server, this is a server-side cursor that allows Multiple Active Statements. Server side cursors in Microsoft SQL Server are more expensive. SQL_CURSOR_FORWARD_ONLY is the default cursor and this is not a server-side cursor and does not allow Multiple Active Statements .

   The other drawback of Microsoft SQL Server server-side cursors is that they do not support all SQL statements. Server cursors do not support any SQL statements that generate multiple result sets, therefore they cannot be used to execute a stored procedure or a batch containing more than one SELECT.

2. Please refer to the notes in the Enabling ODBC support in PHP under Apache about PHP and PHP code hanging when using multiple active statements.

Microsoft Access

There appear to be four main methods of ODBC access from Microsoft Access:

1. ADO

   ActiveX Data Objects support different cursor types, are a wrapper over OLE DB, and are often used under IIS.

   A typical ODBC access pattern for ADO is:

   SQLSetConnectAttr(SQL_MAX_ROWS=0)
   SQLSetStmtAttr(PARAM_BIND_TYPE = 0)
   SQLSetStmtAttr(PARAM_BIND_OFFSET_PTR = 0)
   SQLSetDescField(OCTET_LENGTH_PTR = 0)
   SQLParamOptions(1,0)
   SQLExecDirect(select * from table)
   SQLRowCount
   SQLNumResultCols
   Loop through columns
   {
     SQLDescribeCol(col)
     SQLColAttribute(to get SQL_COLUMN_UNSIGNED, 
                            SQL_COLUMN_UPDATABLE and
       SQL_COLUMN_LABEL)
   }
   SQLSetStmtAttr(ROW_BIND_TYPE=n where n>0)
   SQLSetStmtAttr(ROW_BIND_OFFSET_PTR=ptr where ptr != 0)
   SQLSetStmtAttr(RETRIEVE_DATA=0)
   Loop through columns:
   {
     SQLBindCol
   }
   SQLSetStmtAttr(ROWSET_SIZE=1)
   Loop though rows:
   {
     SQLExtendedFetch
   }

   Unfortunately, this is one of the slowest ways of getting column data with the ODBC-ODBC Bridge. The ODBC-ODBC Bridge deliberately does not support row-wise binding ODBC calls; instead, it binds the columns using column-wise binding (to avoid structure alignment problems between different machines). An entire row is retrieved in one call to SQLExtendedFetch (which is good) but then ODBC-ODBC Bridge needs to translate that from column-wise binding to row-wise binding for the application. This method also uses more memory because the ODBC-ODBC Bridge client needs to allocate memory for one row of column-bound columns.

   Setting BlockFetchSize in the ODBC-ODBC Bridge is irrelevant here as the application does its own binding.

2. Dynasets

   Dynasets can be used where there are one or more unique columns (if there aren't any unique columns, JET drops back to Snapshot mode).

   What JET does depends on whether the DB engine can support Multiple Active Statements. If it can, recent Jets (JET 4+) appear to make the following ODBC calls:

   SQLSpecialColumns
   SQLGetTypeInfo(various)
   SLColumns
   SQLStatistics (to locate unique columns)
   stmt1 = SQLExecDirect(select uniqcol1,uniqcoln from table)
   stmt2 = SQLPrepare(select col1,col2,coln from table where uniqcol1=? and uniqcoln=?)
    From here on it enters a loop reading one row from stmt1 to use
   as parameters in stmt2:
    Loop
   {
     SQLFetch(stmt1)
     SQLGetData(stmt1, uniqcol1)
     .
     .
     SQLGetData(stmt1, uniqcoln)
     SQLBindParameter(stmt2, p1)
     .
     .
     SQLBindParameter(stmt2, pn)
     SQLExecute(stmt2)
     SQLFetch(stmt2)
     SQLGetData(stmt2, col1)
     .
     .
     SQLGetData(stmt2, coln)
     SQLFreeStmt(SQL_CLOSE)
   }

   This method means that on stmt1 the one call to SQLExecDirect returns a result set containing the unique columns for every row in the table. and on stmt2, each SQLExecute returns a result set containing only one row (the row that contains the current values for uniqcol1..n).

   The ODBC-ODBC Bridge has a block fetch mode that may be used to speed up this ODBC access pattern. In this scenario, setting the ODBC-ODBC Bridge's connection attribute, BlockFetchSize to 1 is the quickest. In this mode, the ODBC-ODBC Bridge binds the columns in the result sets and asks for one row at a time; the row data is cached by the ODBC-ODBC Bridge when SQLFetch is called and returned to Jet when it calls SQLGetData for each column.

   With BlockFetchSize set to 1, the time taken to retrieve an entire table is often as little as half of that as when BlockFetchSize is set to 0, although this depends on the number of columns and unique columns in your table.

3. ODBC direct

   ODBC direct uses a forward-only cursor and the SQL is passed through to the DB engine.

   A typical ODBC access pattern looks like this:

   SQLGetTypeInfo(for all types)
   SQLSetStmtAttr(SQL_ROWSET_SIZE, 100)
   SQLExecDirect(select * from table)
   SQLNumResultCols
   Loop through columns
   {
     SQLColAttributes(coln)
     SQLBindCol(coln)
   }
   Loop until no more data
   {
     SQLExtendedFetch(SQL_FETCH_NEXT, 1)
   }

   This is a particularly fast way of reading a result set as it:

   • Reads from the start to the end of the result (forward only).
   • Sets the ROWSET_SIZE to 100 and retrieves 100 rows at a time.

   For the ODBC-ODBC Bridge, this is one of the fastest ways of retrieving a result set (as was demonstrated in Using ODBC directly). This is always faster than Jet's dynaset mode.

4. Snapshot

   Snapshot produces a read-only result set where you can use first, next, last, and so on, and is the fallback for dynaset if the table does not contain unique columns.

   A typical ODBC access pattern looks like this:

   SQLSpecialColumns
   SQLGetTypeInfo(various)
   SQLColumns
   SQLGetTypeInfo(various)
   SQLExecDirect(select col1, col2, coln from table)
   Loop through rows:
   {
     SQLFetch
     SQLGetData(col1)
     .
     .
     SQLGetData(coln)
   }

   This access pattern is greatly speeded up by using the ODBC-ODBC Bridge's block fetch mode. Setting BlockFetchSize to 100 reduces the time taken to retrieve the table massively and since the result set is read-only, this is safe to do.

Miscellaneous applications

For applications where you don't have access to the source code or cannot build it yourself, there is still one possible optimisation. The ODBC-ODBC Bridge contains a block fetch mode that can be used to retrieve multiple rows of data at once and then hand them off to the application one column at a time.

Many applications make ODBC calls like this:

SQLExecDirect(select * from table)
while(SQLFetch() succeeds)
{
    SQLGetData(column1)
    .
    .
    SQLGetData(columnn)
}

There is nothing wrong with the code, but it is certainly not the quickest method to retrieve result sets by using the ODBC-ODBC Bridge. As discussed in Using ODBC directly, binding columns to the result set and asking for multiple rows per fetch is a lot faster. If your application uses the above method but you are not able to change it then you may be able to use the ODBC-ODBC Bridge's block fetch mode. The ODBC-ODBC Bridge's block fetch mode comes in to play if you set the BlockFetchSize connection attribute and call a fetch API when certain other conditions do not apply. The following conditions must be true if the ODBC-ODBC Bridge is to enter block fetch mode:

• Cursor type must be FORWARD_ONLY (the usual default).
• RowArraySize must be 1.
• There mustn't be any bound columns.
• if the ODBC driver is Microsoft SQL Server then the application must be ODBC 3.x.

  The Microsoft SQL Server driver will not return RowsProcessedPtr to an ODBC 2.0 application.

• The row bind offset must be 0g (the ODBC default).
• You must not be using SQLExtendedFetch

If BlockFetchSize is set in the ODBC-ODBC Bridge client DSN, and the above conditions apply, the ODBC-ODBC Bridge client allocates space for column or row data and binds the columns. SQLFetch is called and now the ODBC-ODBC Bridge client has n rows of data. As the application calls SQLFetch, the ODBC-ODBC Bridge client moves through the bound result set one row at a time. When the application calls SQLGetData, there is not a round trip to the server. The column data is supplied from memory. The only difference here is that the ODBC-ODBC Bridge client does not know what data type the application will ask for the columns as, so all columns are bound as SQL_C_DEFAULT. When the application calls SQLGetData and specifies the data type required, the ODBC-ODBC Bridge client does the conversion. The application should behave as it always has but result sets will be retrieved more quickly.

Do not use block fetch mode if your application does positioned updates or deletes. BlockFetchSize = 1 should be safe for any application and is often still faster than not enabling block fetch mode.

To turn on block fetch mode, add the attribute BlockFetchSize=n to your ODBC connection string or DSN. A value of 0 means block fetch mode is turned off (the default). A value for n (where n>0) is the number of rows to fetch in one go. BlockFetchSize may be set up to 100 but there is a balance between memory use and speed. As BlockFetchSize is increased, more memory at the client end is required.

Databases

Microsoft SQL Server

There are a number of entries in the ODBC-ODBC Bridge knowledge base relevant to connecting to Microsoft SQL Server.

Positioning of the ODBC-ODBC Bridge server

With the ODBC-ODBC Bridge, there are two possible configuration scenarios:

1. The ODBC-ODBC Bridge server is installed on the same machine as Microsoft SQL Server.
2. The ODBC-ODBC Bridge server is installed on a different machine to Microsoft SQL Server.

Normally it is OK to do 1) but in some situations responsiveness of the ODBC-ODBC Bridge server is poor when installed on the same machine as Microsoft SQL Server.

Microsoft introduced changes to Microsoft SQL Server 7.0 that may cause connections to an ODBC-ODBC Bridge server and subsequent operations to be very slow. These settings elevate the priority of SQL Server to such an extent that other processes on the machine do not get a look in. (For example, when someone executes a query, Task Manager even stops displaying.) If you are running Microsoft SQL Server, and the ODBC-ODBC Bridge server is running on the same machine as SQL Server, you should try the following:

1. Turn off the following settings, which are available from the Properties menu of the Enterprise Manager. The settings are Boost SQL Server Priority on Windows NT and Use NT fibres. When these are set, Windows is so busy servicing SQL Server, the accept() in the ODBC-ODBC Bridge server can take some time due to Windows not scheduling any other processes.
2. Move the ODBC-ODBC Bridge server onto another Windows machine.

In general, we would recommend running the ODBC-ODBC Bridge server on a different machine to SQL Server for best responsiveness.

Further evidence for this comes from a typical scenario we tried running an in-house ODBC benchmark through to:

• An ODBC-ODBC Bridge server on the same machine as Microsoft SQL Server.
• An ODBC-ODBC Bridge server on a different machine to Microsoft SQL Server.

The tests cover the creation of tables containing different column types, insertion by using SQLExecDirect, parameterised inserts, and the selection of rows by using various methods including parameterised selects.

Here we used Microsoft SQL Server Enterprise Edition v8.00.760(SP3) on a 2 CPU machine with 1 GB RAM. SQL Server was configured to use only 1 CPU. We turned off Boost SQL Server Priority on Windows NT and Use NT fibres. The SQL Server machine was doing nothing else. In the case where the ODBC-ODBC Bridge server was on a different machine, it was running NT 4.00.1381 and was on the same LAN as the Microsoft SQL Server machine. Clearly, in this case it is a lot faster to run the ODBC-ODBC Bridge server on a separate machine to Microsoft SQL Server.

Also, it is worth avoiding putting the ODBC-ODBC Bridge server on your Primary Domain Controller (PDC) or Secondary Domain Controller (SDC) if you have OS authentication turned on, as experiments at Easysoft have shown this to be faster.

Miscellaneous

Here are a few tips for speeding up access to Microsoft SQL Server through the ODBC-ODBC Bridge:

1. If you are inserting a lot of data and don't need to know how many rows were inserted. prefix your SQL with SET NOCOUNT ON. This tells Microsoft SQL Server you do not need to know the number of rows affected and so you are not going to need a useful result from SQLRowCount.
2. SQLPrepare and SQLExecute are often more expensive than SQLExecDirect. The main reason for this is that SQL Server does not directly support SQLPrepare and SQLExecute. The Microsoft SQL Server ODBC driver provides SQLPrepare and SQLExecute support by creating a temporary procedure; this is one extra round trip to the database over SQLExecDirect. There is also an impact on your tempdb as this is where temporary procedures are created (refer to the SQLSetStmtAttr attributes SQL_USE_PROCEDURES_FOR_PREPARE).

   If you're writing an application that issues specific SQL statements and intend distributing that to multiple users in your organisation, a procedure would be more appropriate. Having many users issuing the same SQL in SQLPrepare and SQLExecute is going to start filling your tempdb up with multiple occurrences of same temporary procedure.