Document Version 1.0.0.0
Publisher: Easysoft Limited

Thorp Arch Grange
Thorp Arch
Wetherby
LS23 7BA
United Kingdom

Copyright  1993-2005 by Easysoft Limited.
All rights reserved.

You may not reverse engineer, decompile or disassemble this
manual. Information in this document is subject to change without
notice. Companies, names, and data used in examples are fictitious
unless otherwise noted.

The names of companies referred to herein, their corporate logos, the
names of their hardware and software may be trade names, trademarks or
registered trademarks of their respective owners.

Easysoft and the Easysoft logo are registered trademarks of Easysoft
Limited.

OOB Performance White Paper
===========================

Contents
========

1.0 General
 1.1 Document Conventions
 1.2 Applications/Interfaces
2.0 General Advice
 2.1 ODBC/Logging
 2.2 OOB Authentication
 2.3 Connection Pooling
 2.4 Persistent Connections
 2.5 Windows: OOB Server threads versus processes
 2.6 UNIX: inetd versus standalone server
 2.7 Reverse Lookups on client IP addresses
 2.8 Access Control and OOB Server limits
 2.9 ODBC data sources
 2.10 client-side threads
 2.11 Connection Attempts and Licensing
  2.11.1 ConnectAttempts
  2.11.2 RetryCount and RetryPause
 2.12 Disabling auditing, access control and failing SQL log
 2.13 Trouble shooting slow connections
  2.13.1 Using oobping to help diagnose slow connections
 2.14 Simple SQL advice
3.0 Applications/Interfaces
 3.1 Using ODBC directly
  3.1.1 Retrieving Result-sets
  3.1.2 Inserting data
  3.1.3 Cursors
   3.1.3.1 In General
   3.1.3.2 Cursor Library
  3.1.4 Miscellaneous
 3.2 Perl
  3.2.1 DBD::ODBC
  3.2.2 DBD::Proxy
 3.3 PHP
 3.4 MS Access
 3.5 Miscellaneous Applications
4.0 Databases
 4.1 MS SQL Server
 4.1.1 Positioning of OOB Server
 4.1.2 Miscellaneous

1.0 General
===========

This paper aims to provide all the information to application
developers and users to get the best possible throughput from their
use of the Easysoft ODBC-ODBC Bridge (OOB). Most of this information
is OOB specific but in some cases it is driver or application/interface
specific.

1.1 Document Conventions
========================

Some sections in this document provide pseudo code. The pseudo code
uses constructs that should be familiar to anyone with some
programming experience.

Languages differ in their use of string concatenation.  Some use '+',
some use '.', both or don't need a string concatentation operator. We
have used a '+' character to denote string concatenation.

1.2 Applications/Interfaces
===========================

The applications and interfaces specifically addressed in this paper
are MS Access, Perl DBI/DBD::ODBC, PHP. You should realise that all of
these applications and interfaces are still being developed so in some
cases the best source of information will be the mailing lists and
news groups associated with those products.

2.0 General Advice
==================

2.1 ODBC/Logging
================

ODBC logging is your friend. If you want to see exactly what ODBC
calls are being made from your application or interface turn ODBC
tracing on in the ODBC driver manager.

For Windows this may be done via the ODBC administrator by selecting
the Tracing tab, entering a valid trace log filename and selecting
"Start Tracing Now".

For unix and the unixODBC driver manager, edit the odbcinst.ini file
and add a section like this:

[ODBC]
Trace		= No 
TraceFile		= /tmp/sql.log

Turning on tracing is extremely useful if you did not write the
application or interface and don't have the source code. The driver
manager will log all the ODBC calls and you will get a much better
idea of what ODBC calls are being made and the access patterns of the
application.

However, bare in mind that enabling tracing in the driver manager is
the last thing you want to do for high throughput as tracing is very
time consuming and can generate huge log files. 

Unfortunately there are a number of places and ways in which tracing
may be enabled and in many cases we see, some form of tracing is
slowing the database connection down. Check the following list to
make sure tracing is not being turned on in a production environment:

[1] Check all odbc.ini files for lines containing "Logging=n" where
    n > 0. Comment them out or set the value to 0.

[2] If you are using the unixODBC driver manager, check odbcinst.ini
    files and make sure the [ODBC] section does not have "Trace = yes".
    If it does set Trace to "No" or remove the line entirely.

[3] Make sure you have not turned on tracing at the server end.  You
    can check this through a web browser to http://server_machine:8890
    and then Configuration. Logging should be set to 0. Alternatively
    on UNIX examine the server odbc.ini/esoobserver.ini file or on
    Windows examine the registry for:

    HKEY_LOCAL_MACHINE\
    SOFTWARE\
    Easysoft ODBC-ODBC Bridge\
    Configuration\
    System\
    Settings\
    Logging

[4] If using Windows make sure tracing in the driver manager is not
    turned on. You can check this by going in to the ODBC
    Administrator and clicking on the tracing tab.

[5] Check your application is not turning ODBC tracing on by calling
    SQLSetConnectAttr(SQL_ATTR_TRACE, SQL_OPT_TRACE_ON).

2.2 OOB Authentication
======================

By default the OOB Server authenticates each user connecting to it.

The client passes an operating system username and password to the
server which the OOB Server then passes to the operating system to
validate. In both Windows and UNIX the OOB Server then becomes that
user for the duration of the client connection.

In Windows, in particular this can be quite a lengthy process
depending on where your primary domain controller is located and what
it is doing. If you are happy accessing the OOB Server without
operating system username/password authentication is safe for your
network then disabling OOB Server authentication can make a big
difference to connection times.

Here are some sample times from Linux to Windows where the Windows
machine is the PDC and is running MS SQL Server (which we use as the
database). The results are the average of 50 consecutive connections.

time in seconds with authentication enabled (default) : 0.486s
time in seconds with authentication disabled          : 0.174s

If your PDC is busy or it takes longer to contact it the difference
between authenticated or not might be much larger.

Neither of these times are likely to bother a normal user logging in
to a workstation on your network but if say the OOB is running under
your web server to connect to your remote database thousands of times
a day then this may prove a significant difference.

To disable OOB Server authentication change the configuration in the
OOB Server HTTP administrator (see Configuring the OOB Server in the
manual).

2.3 Connection Pooling
======================

Connection pooling is where the driver manager keeps hold of open
connections when SQLDisconnect is called so they may be used
again. The driver manager stores the connection string used to
SQLDriverConnect (or SQLConnect) with each connection it opens so it
may be reused if SQLDriverConnect/SQLConnect is called with the same
connection string. Usually there is a timeout associated with a pooled
connection such that if the pooled connection is not used again within
the timeout period the driver manager will drop it.

Connection pooling can significantly speed up most applications which
repeatedly open and close ODBC connections.

With OOB there are two sides of the operation to consider, the client
end and the server end; as both connect to an ODBC driver manager.
Connection pooling at the OOB Server end will speed up access to the
final ODBC driver for you database. Connection pooling at the OOB
client end will speed up access to the OOB Server.

To enable connection pooling in Windows you need to edit the ODBC
driver manager configuration using the ODBC configuration utility.
Select the "Connection Pooling" tab, double click the driver you are
using, click "Pool Connections to this driver" and enter a timeout in
the field labelled "Time that unused connections remain in the pool in
seconds".

For the unixODBC driver manager, edit the odbcinst.ini file,
add "Pooling = Yes" to the [ODBC] section and then add a "CPTimeout" 
value to the driver section e.g.

[ODBC]
Trace = No
Trace File = /tmp/sql.log
Pooling = Yes

[OOB]
Description = Easysoft ODBC-ODBC Bridge
Driver = /usr/local/easysoft/oob/client/libesoobclient.so
Setup = /usr/local/easysoft/oob/client/libesoobsetup.so
FileUsage = 1
CPTimeout = 120

As an example of the difference connection pooling can make we run the
same test as in "OOB Authentication" above but with connection pooling
enabled. The average time per connection after 50 consecutive
connections was 0.007s.

There are a few additional considerations you should note:

[1] Pooled connections which have timed out are only closed when
    another connection to the same driver manager is made.

[2] Pooled connections are generally only process wide.

    This is a very important point especially with respect to the OOB
    Server which normally runs in a multi-threaded mode but can run in
    a multi-process mode. If the OOB Server is running in
    Multi-Process mode then connection pooling at the server is not
    viable as the OOB Server starts a new process for each incoming
    connection.

    In addition, a popular initial candidate for using pooled
    connections at the client end is a web server like Apache. Here
    again (like above) you need to remember that most web servers fork
    child processes to handle the connections. The pooled connections
    are only within the individual web server processes and not web
    server wide unless the web server is multi-threaded.

[3] Pooling connections may cause you to have more open database
    connections at any one time than you did prior to pooling
    connections (this may have licensing implications within your
    database).

    e.g. Suppose you enable connection pooling for driver X and run
    applications from one or more clients which make connections to
    multiple data sources all in the same database. If the connection
    pooling timeout is set to 120 seconds but 120s elapses before that
    connection is reused you effectively had a connection open for an
    additional 120s (over non-pooled scenario). If you have limited
    licenses for your database (or for OOB) then this could cause you
    to hit the license limit. The worst scenario would involve many
    clients which connect infrequently as each of their connections
    would be held open for at least 120s after they disconnected and
    possibly a lot longer if no other connections were made through the
    same driver manager.

    A classic case of this is a web server (see above). Most web
    servers create multiple processes to handle HTTP requests. If you
    enable connection pooling then you will potentially have N open
    database connections (where N is the number of HTTP Server
    processes). Since you generally have little control over how the
    main web server process hands off pages to its child processes
    with sufficient traffic you eventually end up with a lot of pooled
    connections.

    e.g. Assume you configure your web server to only allow it to
    start 5 child processes to handle connections. Assume you have
    enabled connection pooling, a timeout of 120 seconds and you have
    one page on your web site that requires ODBC access. Assume the
    page that requires ODBC access is hit once every 20 seconds. If
    the web server hands out the HTTP request for the ODBC enabled
    page to a different child each of the first 5 times the page is
    hit then you have 5 concurrent connections to the database after a
    100 seconds. Without connection pooling you would only have had 1
    concurrent connection at any one time.

Please refer to the OOB FAQ question "Why do my connections to the OOB
server seem to be dropped after a while?"

2.4 Persistent Connections
==========================

Persistent connections are really just the same mechanism as
Connection Pooling (above) except that in general the
application/interface does the pooling instead of the ODBC driver
manager.

Where possible we would recommend using ODBC driver manager connection
pooling over any application specific persistent connection method.

Please see "Connection Pooling" as most of the notes there are
applicable to persistent connections.

Please refer to the OOB FAQ question "Why do my connections to the OOB
server seem to be dropped after a while?"


2.5 Windows:OOB Server threads versus processes
===============================================

The OOB Server for windows may be configured to run in Multi-Process
mode or Multi-threaded mode. In Multi-Threaded mode (the default)
the OOB Server starts a new thread to handle each incoming client
connection. In Multi-Process mode the OOB Server starts a new process
for each incoming connection.

Multi-Process mode in the OOB Server was originally written to allow
connection to thread-unsafe ODBC drivers (e.g. JET implementations
before JET 4.0).

There are a few differences between running the server Multi-Threaded
and Multi-Process which can impact throughput and resources:

[1] Windows tends to create threads quicker than processes. As a result
    connection times for a Multi-Threaded OOB Server are slightly less.
    
    For our connection test mentioned above, the multi-threaded server
    handled 150 consecutive connections from the same application in
    20.3 seconds compared with 22.7 seconds in the multi-process
    server.

[2] Windows seems to allocate CPU in a fairer way between processes
    than threads in a process. As a result, the Multi-Process OOB
    Server tends to be quicker after connection when there are
    multiple concurrent connections.

[3] The Multi-Threaded OOB Server uses less resources (machine wide
    handles, memory specifically).

2.6 UNIX: inetd versus standalone server
========================================

The OOB Server for UNIX is installed by default under {x}inetd.  This
means inetd listens on a port on behalf of the OOB Server and when a
connection is made to that port it runs a shell script which starts an
OOB Server process which inherits the socket connection. Some sites
also use tcpwrappers in their inetd configuration file to provide
access control to the OOB Server.

The OOB Server may be run in standalone mode without inetd. In this
mode the OOB Server listens on the port and forks a child process to
handle each connection.

Running the OOB Server in standalone mode is quite a bit quicker
because there is a lot less to do (one less fork, server configuration
file read on startup instead of once per connection etc). It also has
the advantage that you can run the OOB HTTP administration server to
see what is going on in the OOB Server.

See "OOB the Server configuration" chapter in the manual or FAQ
question "Can I run the OOB Server on UNIX without inetd SuperServer"
for how to do this.

The inetd server only has one advantage; if the main standalone OOB
Server terminates no connections are possible until it is restarted
This is not true of an inetd started server.

If you are using {x}inetd and making alot of connections in short
periods of time please see the OOB FAQ question "Why is inetd
terminating the OOB Server service?".

2.7 Reverse Lookups on client IP addresses
==========================================

When a client connects to the OOB Server it can do a reverse lookup on
the client's IP address to find out who they are. The resulting string
may be used in the OOB's audit trail file and various notices the OOB
Server issues.

To do this successfully, you need a working DNS server or a hosts
lookup table with the clients IP address. If you DNS server is not
contactable most DNS clients will make multiple attempts and this will
slow connections down alot.

For fastest possible connections disable the OOB Server's ReverseLookup
in its configuration (see the OOB Server configuration chapter of
the manual for how to do this).

Please see the question "Do you have to have DNS working for correct
operation of the OOB?" in the OOB FAQ for further relevant
information.

2.8 Access Control and OOB Server limits
========================================

The standard OOB Server contains access control rules defining which
clients can access the server. The Enterprise server contains an
additional access control list for access to specific data sources.

These access control lists are a vital part of the security built
in to OOB but then they can also consume time during initial
connection. For this reason you should try and keep your access control
rules short and concise.

There are two connection-based limits in the OOB Server;
MaxThreadCount and MaxClientConnect. MaxThreadCount limits the total
number of threads or processes the OOB Server creates to handle client
connections. MaxClientConnect limits the total number of concurrent
connections from a particular client.

Both of these limits are useful in throttling the OOB Server but they
have one draw back. At any one time, the OOB Server does not know
accurately how many active connections there are (see the OOB manual
for a description of why). As a result, when you set either of these
limits the OOB Server has to cycle through the created
threads/processes and find out if any have terminated. If you have
many concurrent connections this can lengthen the connection time
quite considerably.

2.9 ODBC data sources
=====================

A data source name is a logical name for a data repository or
database.  Any attributes that define a data source are stored under
the DSN for retrieval by the driver.

In the case of the OOB, the data source name is the key to a set of
attributes in the odbc.ini or the system information (in
Windows).

When the application calls SQLDriverConnect or SQLConnect with a DSN,
the driver manager uses the DSN to locate the relevant driver, loads
the driver and calls SQLDriverConnect/SQLConnect in the driver with
the same connection strings.

The OOB client can look up other attributes it needs for the
connection in the odbc.ini or system information.

When the OOB Server calls SQLDriverConnect or SQLConnect it is acting
like an ordinary ODBC application.

You should consider exactly what happens in the driver manager when
you call SQLDriverConnect/SQLConnect with a data source name. In
Windows the driver manager needs to locate the data source name in the
registry, pull out the name of the driver, load the driver and then
pass the ODBC calls on to the driver. The same is true for UNIX except
the registry is replaced with the odbcinst.ini and odbc.ini
files. Parsing these files when there are a large number of DSNs can
make a large difference to connection times.

This is a slightly exaggerated example but here are some timings for
an odbc.ini file containing one DSN and an odbc.ini file containing
60 DSNs with the DSN we want last in the file:

timings for 50 connects/disconnects
one DSN in odbc.ini file                               : 28s
60 DSNs in odbc.ini file and required DSN last in file : 42s

NOTE: In later unixODBC versions the difference is significantly
less if all the connects/disconnects are done in the same process
because the unixODBC driver manager caches values parsed from the
ini files.

There is another aspect with respect to connection attributes.  If
your application calls SQLConnect/SQLDriverConnect with a DSN name and
nothing else then the OOB client needs to examine the
registry/odbc.ini to obtain the other attributes it needs to complete
the connection to the server (e.g. Server, TargetDSN etc). Any
connection attribute that may be specified in the registry/odbc.ini
may be specified in the connection string. For optimum speed or
convenience (DSN-less connections) you may want to pass all the
required connection attributes in the connection string.

On UNIX there is one last potential possibility for slowing down the
connection which is related to how the OOB client ODBC driver and the
unixODBC ODBC driver manager locate DSNs. The OOB client looks for DSNs
in the following places (in order):

ODBCINI (environment variable)
<cwd>odbc.ini
<cwd>.odbc.ini (UNIX only, not VMS)
<home>odbc.ini
<home>.odbc.ini (UNIX only, not VMS)
/etc/odbc.ini (SYS$ROOT:odbc.ini for VMS) - system DSNs only
<wherever unixODBC stores DSNs>

Obviously it takes time to examine the file system for all these
files, read them and look for the DSN. The sooner your DSN is found
the quicker the initial connect will be. In particular, if you have
built the unixODBC driver manager yourself the configure script
defaults sysconfdir (where unixODBC looks for system DSNs) to
/usr/local/etc/odbc.ini. The OOB client does not look in
/usr/local/etc/odbc.ini itself, partly because unixODBC can be
configured to use any directory. Instead, if the OOB client has not
found your DSN it attempts to dynamically load unixODBC's libodbcinst
shared object and then use the SQLPrivateProfileString API to retrieve
DSN attributes.  This process takes alot longer than if OOB finds the
DSN itself. One way to speed this up to locate the odbc.ini unixODBC
uses for system DSNs and then symbolically link /etc/odbc/ini to that
file. In this way OOB locates the DSN alot quicker and unixODBC
continues to work.


2.10 Client-side threads
========================

One way you might think will automatically increase the throughput
from your application is to make it multi-threaded. This can be true
but there are a few simple situations to think about first:

[1] Can you divide your database access up in a meaningful way to
    distribute the work between threads?

[2] Are you using a thread-safe OOB client? The Windows OOB client
    ODBC driver is thread-safe but for UNIX there are separate
    distributions for thread-safe and thread-unsafe drivers. The UNIX
    distributions containing thread-safe OOB clients and unixODBC
    driver manager have "-mt" in the distribution name and contain OOB
    client shared objects with "_r" in the name.

[3] Making multiple threads use the same connection is not going to
    produce any performance increase. The reason for this is that the
    OOB client's network library serialises access to the socket
    connected to the server (there is one socket per ODBC
    connection). To get the maximum benefit from multiple threads
    using ODBC each thread should run with its own ODBC connection
    handle.

2.11 Connection Attempts and Licensing
======================================

There are a few remaining configurable parameters for the OOB client
and server which can affect connection times.

2.11.1 ConnectAttempts
======================

For the OOB client ConnectAttempts specifies how many attempts the
client will make to connect to a server.

The OOB client will make ConnectAttempts connection attempts if the
server operating system returns ECONREFUSED (connection refused). The
reason for this is that on some platforms (notably NT workstation) the
listen backlog can only be set to a low level so if there is a sudden
rush of connections the operating system may turn some down before the
OOB Server sees them. When ECONREFUSED is received by the client it
waits connection_attempt * 0.1 seconds before trying again up to a
maximum of ConnectAttempts times.

Here is an example where ConnectAttempts is 5 (the default) and the
server always refuses the connection:

connect attempt 1 - refused by server
wait 0.1 seconds
connect attempt 2 - refused by server
wait 0.1*2 seconds
.
.
.
connect attempt 5 - refused by server
client gets error diagnostic:

08001:1:4:[Easysoft ODBC (Client)]
  Client unable to establish connection
HY000:2:4:[Easysoft ODBC (Client)]
  Connection refused, connect(), after 5 attempts

and here is an example where the first few attempts are refused due to
the listen backlog queue being full but by the third attempt the
backlog is cleared and the connection accepted:

connect attempt 1 - refused by server
wait 0.1 seconds
connect attempt 2 - refused by server
wait 0.1 * 2 seconds
connect attempt 3
 connection accepted by server operating system

ConnectAttempts is 5 by default but it may be modified in the
OOB Settings off the DSN dialogue (Windows) or in the DSN section
of your odbc.ini file. e.g.

[mydsn]
Driver = OOB
.
.
ConnectAttempts = 3

Please note: OOB used to have a global setting (NetConnectRetryCount) 
for all DSNs but this is deprecated now.

The value of ConnectAttempts takes on extra importance if you are
using OOB Enterprise Server or OOB2 Server and you have specified
multiple servers and ports in a client DSN entry. In this scenario you
specifically want an OOB client which cannot contact the first OOB
Server to fallover to the second and subsequent servers as quickly as
possible. For this situation you want ConnectAttempts to be as
low as possible e.g. 1.

2.11.2 RetryCount and RetryPause
================================

The RetryCount and RetryPause configurable parameters affect what the
OOB Server does under two situations:

[1] resource use on your server is such that the OOB Server is unable to
    create a new thread or process to handle the current incoming
    connection.

[2] you have used all your available license slots.

In each of these cases the RetryCount value specifies the number of
additional attempts the server will make to obtain this resource and
the RetryPause is the time in seconds it will wait between attempts.

The default values are RetryCount=5 and RetryPause=3.

The default values are set in an attempt to let you maximise the use
of your license slots. If you have many clients connecting and
disconnecting then there may be times when all your license slots are
in use for short periods of times. Rather than just return to the
client, the server is assuming (with the default values) that a
license slot will become available soon. However, the net affect of
the default values when no license slots become available is to delay
informing the client that there are no license slots available for 15
seconds.

Only you can determine what your desired behavior here should be.

2.12 Disabling auditing, access control and failing SQL log
===========================================================

The OOB Server contains functionality to:

o log all failing SQL (see the OOB HTTP administrator and
  the LogFailingSQL setting). This is a valuable debugging
  aid but costs resources (in the storing of the SQL until a
  time when it is known the SQL has succeeded or failed)
  and if the SQL fails costs time to write the failing SQL
  to the log.

o perform auditing of all connections, connection failures,
  OS authentication failures etc. Each auditable event is a write
  to the audit trail file. Avoiding auditing will speed up the
  OOB Server. (see the AuditODBCAccess setting in the OOB HTTP
  administrator).

o perform access control. There are two types of access control:
  i) access control rules defining which client machines can connect
     to the OOB Server. Processing this access control list adds a small
     amount of processing time to the main OOB Server.
  ii) DSN access control defining which client machines and users
      can connect to a particular DSN. Handling this form of access
      control requires slightly more processing than (i) since the
      ODBC connection string has to be parsed to ascertain the DSN.

Using any or all of these options has a small effect on resources and
processing in the OOB Server. If you are looking for the optimum
performance and are prepared to lose these features then disable them
in the OOB HTTP Administrator.

2.13 Trouble shooting slow connections
======================================

If you believe connections to the OOB Server from the client are
taking too long then there are a number of things you can do to check
this out. However, first you need to determine if the connection
time to expect is realistic. As a rough guide we have found:

In our experiments it takes less than 1/10 second to connect an
application linked directly with the OOB client on Linux to a data
source on NT over a 100Mb Ethernet link where both machines are on the
same LAN and are mostly idle and OOB Server authentication is
disabled. This is a small fraction of a second slower for interpreted
languages like Perl or if there is a driver manager (like unixODBC)
between the application and the OOB client. It can be considerably
slower on Windows if OOB Server authentication is enabled as a lot
depends on what you PDC is doing and where it is located on your
network.

If you are still convinced something might not be configured correctly
then check the following:

[1] Are the client and server machines on the same Local Area Network
    (LAN)?  Connection times can increase dramatically if they are
    not. Try pinging the server from the client and see what return
    times you get.

    e.g.

    ping www.easysoft.com
    PING www.easysoft.com (194.131.236.4): 56 data bytes
    64 bytes from 194.131.236.4: icmp_seq=0 ttl=255 time=0.6 ms
    64 bytes from 194.131.236.4: icmp_seq=1 ttl=255 time=0.1 ms

    In this case less than 0.6ms - quick. Machines not on the same LAN
    can show a much greater time and this will be reflected in your
    ODBC connect times.

    e.g.

    ping www.whitehouse.gov
    PING www.whitehouse.gov (193.50.203.51) : 56 data bytes.
    64 bytes from 193.50.203.51: icmp_seq=0 ttl=240 time=34.5 ms
    64 bytes from 193.50.203.51: icmp_seq=1 ttl=240 time=35.2 ms

    This is relatively slow.

[2] Check that ODBC tracing is not turned on. See "2.1 ODBC/Logging".

[3] If the ReverseLookup configurable parameter in the OOB Server is
    enabled disable it and test again. See "2.7 Reverse Lookups on
    client IP addresses".

[4] Check you are not running out of license slots temporarily.
    See "2.11.2 RetryCount and RetryPause".

[5] Check the size of your odbc.ini files/registry and the position of
    the DSN you are using within it. See "2.9 ODBC data sources".

[6] If you are using MS SQL Server see the advice under
    "4.1 MS SQL Server".

[7] If you are using Windows 2000 see the OOB FAQ and the question
    "Why have connection times to MS SQL Server slowed down since
    upgrading to Windows 2000 Server?"

[8] Work through the examples of using oobping in "2.13.1 Using oobping
    to help diagnose slow connections.

2.13.1 Using oobping to help diagnose slow connections
=====================================================

oobping is a small program distributed with the OOB since version
1.0.0.35. oobping is a valuable tool for checking OOB connectivity and
diagnosing connection problems or connection timing issues.

A full description of oobping may be found in the OOB manual and the
OOB FAQ entry "How do I use oobping?".

For Windows distributions you will find oobping.exe in
installdir\Easysoft\Easysoft ODBC-ODBC Bridge.

For UNIX OOB distributions there are two versions of oobping called
oobpings (a statically linked version) and oobpingd (a dynamically
linked version linked against the libesoobclient shared object) which
may be found in /usr/local/easysoft/bin. To use oobpingd you may need
to set and export your LD_LIBRARY_PATH/LD_RUN_PATH/LIBPATH to include
/usr/local/easysoft/oob/client and /usr/local/easysoft/lib.

oobping has the following command line:

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

where:

-h host
   The name or IP address of the machine where the OOB Server is.
-d ODBC_connection_string
   The ODBC connection string is that defined by ODBC.
   i.e. a list of semi-colon separated attribute=value pairs.
   e.g. DSN=test;UID=fred;PWD=bloggs;
   
   If you specify -u and/or -p as well as -d
   "LogonUser=xxx;LogonAuth=yyy;" will be added to the end of the
   connection string where xxx and yyy are the values specified for
   -u and -p.
-t port
   The port on which the OOB Server is listening
-u osuser
   A valid username on the "host" operating system
-p ospassword
   A password for the user specified with -u
-e Show time to complete the requested operation (post OOB 1.1.0.0)

From OOB after version 1.1.0.0, 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 supporting the -e switch then you can
use the common 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...
    OOB Server Version: 1.1.0.00
    OOB Server Name: OOB
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 logon:

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

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

As a final example the next example shows a full connection to the
OOB Server, OOB server authentication and 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
==========

Here you can see the extra time required to connect to the remote
ODBC data source.

2.14 Simple SQL advice
======================

This is some simple advice on SQL use which may help you prevent
wasting valuable cpu cycles in your code:

[1] When issuing queries only select the columns you need. Generating
    result-sets containing columns you don't need just wastes time in
    the driver. Worse still, some drivers will 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 (e.g. the addition of a new column).

[2] Try batching your SQL. An Application that builds several SQL
    statements to execute often realizes better performance if it
    batches the statements together and sends them to the server all
    at once. e.g.

    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:

    [a] The SQL statements are parsed and compiled once when the
        procedure is created and not when the procedure is used by
        your application.

    [b] 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.

3.0 Applications/Interfaces
===========================

3.1 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, a integer, a char(30), a varchar(255), a timestamp and a
float. The table is created with 50000 rows.

3.1.1 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 way it could be easily modified that might speed it
up is to fetch the columns as their native types, e.g. the integer
as a SQL_C_LONG, the timestamp as a SQL_C_TIMESTAMP and the float as
a SQL_C_FLOAT. This would avoid 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 see it takes 62
seconds to run (longer). The main reasons for this are:

[1] the floats representation on the server platform are not
    necessarily the same as the client platform so OOB has to convert
    them from the server's representation to the clients
    representation.

[2] the timestamp is a structure which the OOB splits up into the
    fields of the structure 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 it 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
OOB Server can send an entire row back to the client in one network
call. A good rule of thumb for OOB 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 enable this 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
is down to 5 seconds, 1/9 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 > 1 tends to provide larger amounts of
consecutive bytes for column data which OOB will favor for data
compression when transferring the data over the network.

There is one other alternative, 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 when you set
SQL_ATTR_ROW_ARRAY_SIZE > 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 OOB, row-wise binding is a little slower. The OOB 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.  OOB 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 which uses row-wise binding as
it works as ODBC dictates but it is one of the few instances in OOB
where what the application asks for (in ODBC terms) is not mirrored at
the server side.

3.1.2 Inserting data
====================

Unsurprisingly, much of the examples in the previous section apply
to 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 used a parameterized 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. e.g.

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 OOB you'll find it does not make
any real difference. The reason for this is that in the first case with
straight forward SQL insert you are 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 (OOB also has some
extra work to pack up the parameters).

With many databases a very easy speed up if you are doing bulk
inserts, say 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.

e.g.

Add the above pseudo code:

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 MS SQL Server, DB2 or Oracle
(others may be the same) 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 > 1. e.g.

#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.

3.1.3 Cursors
=============

3.1.3.1 In General
==================

In ODBC a cursor is automatically opened on 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 driver may change these settings as it sees
fit in which case it returns an "option value changed" diagnostic (see
later).

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 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 i.e.  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/deleted or column data
    changed since the cursor was created that satisfy the conditions
    of the query are not seen (although static cursors may detect the
    same applications own updates, deletes or inserts) 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 i.e. the data values and rows in the cursor
    can change dynamically on each fetch.

    The cursor shows all deleted, inserted and updated statements
    whether they were made by the application with the dynamic cursor
    or 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 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 which are not in the keyset
    (whether 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
    see inserts). Rows which 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

    e.g. 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 which is capable of displaying only 20
    rows at a time in the window might set the rowset size to 20 so
    that each fetch returned 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 made 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 e.g. 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 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 is not the most efficient method available in that driver
of meeting the applications requirements.

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

o size of result-set
o percentage of data likely to be needed
o performance of the cursor
o requirement for scrolling or positioning
o desired level of modification visibility to other applications

3.1.3.2 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 principle settings are i) always use the cursor library (no matter
what the ODBC driver supports) and ii) use the cursor library when
required (i.e. 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 and avoid the cursor library.

If your driver has support for the cursor you require then asking the
driver manager to use the cursor library with
SQLSetConnectAttr(SQL_USE_CURSORS, SQL_CUR_USE_ODBC) is a time waster.
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_SIZS when calling the ODBC driver. i.e. if you set
SQL_ROWSET_SIZE or SQL_ATTR_ROW_ARRAY_SIZE &gt; 1 (to get extra
performance) but then stipulate you want to use the driver manager
cursor library then each call your app makes to SQLFetchxxx to get
n rows at a time will be converted into n calls to SQLFetch.


3.1.4 Miscellaneous
===================

Here are a few miscellaneous tips:

[1] Avoid repeatedly calling SQLDescribeParam and SQLDescribeCol on
    the same SQL. It is much better to cache these results yourself
    as these metadata calls can be expensive.

[2] If possible leave calling SQLDescribeParam/SQLDescribeCol until
    after calling SQLExecute as in some drivers this causes extra
    work.

[3] Try to avoid data conversions in SQLGetData, SQLBindCol etc.
    e.g. calling SQLGetData asking for a SQL_C_CHAR for a column which
    is a char.

3.2 Perl
========

3.2.1 DBD::ODBC
===============

This section discusses use of Perl, perl modules DBI and DBD::ODBC,
the unixODBC driver manager and OOB providing 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 and DBD::ODBC the specific
driver under DBI to access ODBC drivers. DBD::ODBC is written to work
with any ODBC driver and as such it is 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 required for the generic
nature of DBD::ODBC e.g. when some SQL is executed DBD::ODBC does not
know if it was a select statement or an insert so a typical insert is
followed by SQLNumResultCols(), SQLRowCount() and SQLMoreResults()
calls all of which prove useless on a simple insert. This is not a
criticism of DBD::ODBC it is simply recognising the difficulty of
writing a super efficient DBD without knowing what the SQL is. These
additional calls to SQL functions cause remote procedure calls to the
server in OOB which slow perl scripts down.

As an example and using the same table described in the above 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 $i;
my $sql;
$sql = "insert into perf values (?,?,?,?,?)";
my $stmt = $dbh->prepare($sql);
$stmt->bind_param(1, $i);
$stmt->bind_param(2, $data2);
$stmt->bind_param(3, $data3);
$stmt->bind_param(4, $data4);
$stmt->bind_param(5, $data5);

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

for ($i = 1; $i <= 50000; $i++) {
    $data5 = $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 there is
no speed up over 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 under the above ODBC section.

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

3.2.2 DBD::Proxy
================

For anyone wanting a Perl only solution DBD::Proxy offers similar
functionality that OOB provides. DBD::Proxy transfers the DBI calls
and data between the client and server. Since it is only doing the DBI
calls and OOB is doing all the ODBC calls beneath DBI you would expect
OOB 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
in seconds and the average of 3 runs.

CPUu and CPUs are user and system CPU times.

+-------+-------------------------+------------------------+
|Test   | ODBC-ODBC Bridge        | DBD::Proxy             |
+-------+-------------------------+------------------------+
|       | CPUu  | CPUs  | Elapsed | CPUu  | CPUs | Elapsed |
|-------+-------+-------+---------+-------+------+---------+
|insert | 19.73 | 22.72 |  84.18  | 58.17 | 5.31 | 151.54  |
|-------+-------+-------+---------+-------+------+---------+
|select |  7.01 |  7.09 |  31.94  | 2.52  | 0.61 |  48.33  |
+-------+-------+-------+---------+-------+------+---------+

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

+---------------------------+------------------+------------+
|                           | ODBC-ODBC Bridge | DBD::Proxy |
+---------------------------+------------------+------------+
| inserts per second        | 594              | 330        |
| rows retrieved per second | 1565             | 1035       |
+---------------------------+------------------+------------+

Of course, as we have shown turning auto commit off and committing all
the 50000 inserts at the end speeds up the inserts per second with
most databases but this works equally for both ODBC-ODBC Bridge and
DBD::Proxy.

DBD::proxy does have some optimisation settings like proxy_no_finish,
proxy_quote and RowCacheSize. OOB has Block Fetch Mode but it 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

+-------+-------------------------+------------------------+
|Test   | ODBC-ODBC Bridge        | DBD::Proxy             |
+-------+-------------------------+------------------------+
|       | CPUu  | CPUs  | Elapsed | CPUu  | CPUs | Elapsed |
|-------+-------+-------+---------+-------+------+---------+
|select |  7.31 |  7.17 |  32.19  |  3.6  | 0.14 |  16.12  |
+-------+-------+-------+---------+-------+------+---------+

which puts the final rows retrieved per second at:

+---------------------------+------------------+------------+
|                           | ODBC-ODBC Bridge | DBD::Proxy |
+---------------------------+------------------+------------+
| rows retrieved per second | 1553             | 3102       |
+---------------------------+------------------+------------+

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


We started by saying OOB RPC's 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 alot 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,
(e,g, SSH, zebedee) we have not verified this.

3.3 PHP
=======

Most of the comments made under the Perl section above apply equally
to PHP. PHP is however different from Perl's DBI/DBD::ODBC in that it
provides a PHP interface to ODBC i.e. 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 are using PHP with MS 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 which is requested depends on
    the version of PHP. Older versions (around 4.0.n/4.1.n) set the
    cursor to SQL_CURSOR_DYNAMIC but later versions (4.2.n) 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 MS SQL Server this is a
    server-side cursor which allows multiple active statements. Server
    side cursors in MS SQL Server are more expensive (see the section
    on cursors in this document). SQL_CURSOR_FORWARD_ONLY is the
    default cursor and this is not a server-side cursor but does not
    allow multiple active statements.

    The other drawback of MS SQL Server server-size 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 see the notes in the OOB Apache_PHP document about PHP and
    PHP code hanging when using multiple active statements.

3.4 MS Access
=============

There appear to be 4 main methods of ODBC access from MS Access:

[1] ADO

    ActiveX Data Objects supports different cursor types, is a wrapper
    over OLE DB and is 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 OOB. OOB deliberately does not support rowise binding
    ODBC calls; instead it binds the columns using column-wise binding
    (to avoid the structure alignment problems between different
    machines). An entire row is retrieved in one call to
    SQLExtendedFetch (which is good) but then OOB needs to translate
    that from column-wise binding to row-wise binding for the
    application. This method also uses more memory because the OOB client
    needs to allocate memory for one row of column-bound columns.

    Setting BlockFetchSize in OOB is irrelevant here as the
    application does its own binding.

[2] Dynaset

    Dynaset can be used where there are one of more unique columns (if
    there aren't any unique columns JET drops back to Snapshot mode -
    see below).

    What JET does depends on whether the DB engine can support
    multiple active statements. If it can then 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)
    }

    As you can see, 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).

    OOB contains a Block Fetch Mode (see separate entry in this
    document) which may be used to speed up this ODBC access pattern.
    In this scenario setting the OOB's connection attribute,
    BlockFetchSize to 1 is the quickest. In this mode OOB, binds the
    columns in the result-sets and asks for one row at a time; the row
    data is cached by OOB when SQLFetch is called and returned to Jet
    when it calls SQLGetData for each column.

    With BlockFetchSize=1 the time taken to retrieve an entire table
    is often as little as half that when BlockFetchSize=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
    [a] reads from the start to the end of the result - forward only and
    [b] sets the ROWSET_SIZE to 100 and retrieves 100 rows at a time.

    For OOB 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. This is always slightly faster
    than OOB's Block Fetch Mode (which is irrelevant here as the
    application does its own binding) since the OOB client knows what
    datatypes the application wants to bind for each column before the
    column data is returned to the application.

[4] Snapshot

    Snapshot produces a read-only result-set where you can use
    first/next/last etc 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 scenario is greatly speeded up by using OOB's Block Fetch
    Mode.  Setting BlockFetchSize=100 will reduce the time to retrieve
    the table massively and since the result-set is read-only this is
    safe to do.

3.5 Miscellaneous Applications
==============================

For applications where you don't have access to the source code or
cannot build it yourself then there is still one possible
optimisation. OOB contains a Block Fetch Mode which 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 are written making 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 results-sets via OOB. As discussed in "3.1
Using ODBC directly" binding columns to the result-set and asking for
multiple rows per fetch is alot faster. If your application uses the
above method but you are not able to change it then you may be able to
use OOB's Block Fetch Mode. OOB'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 OOB is to enter Block Fetch Mode:

[1] Cursor type must be FORWARD_ONLY (the usual default)

[2] RowArraySize must be 1

[3] there mustn't be any bound columns

[4] if the driver is MS SQL Server then the application must be ODBC
    3.

    The MS SQL Server driver will not return RowsProcessedPtr to an
    ODBC 2.0 app.

[5] the row bind offset must be 0 (the ODBC default)

[6] you must not be using SQLExtendedFetch

If BlockFetchSize is set and the above conditions apply then the OOB
client allocates space for column/row data and binds the
columns. SQLFetch is called and now the OOB client has N rows of
data. As the application calls SQLFetch the OOB client moves through
the bound result-set one row at a time. When the application calls
SQLGetData there is not round trip to the server, the column data is
supplied from memory. The only major difference here is that the OOB
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
OOB client will perform the conversion.  The application should behave
as it always has but result-sets will be retrieved more quickly.

Do not use Block Fetch Mode (where BlockFetchSize > 1) if you
application performs positioned updates or deletes. BlockFetchSize = 1
should be safe for any application and is often still faster then
not enabling Block Fetch Mode.

To enable Block Fetch Mode add the attribute BlockFetchSize=n to your
connection string or enter n into the OOB ODBC driver DSN dialogue. A
value of 0 means Block Fetch Mode is disabled (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.

4.0 Databases
=============

4.1 MS SQL Server
=================

There are a number of entries in the OOB FAQ relevant to connecting to
MS SQL Server.

4.1.1 Positioning of OOB Server
===============================

With OOB there are basically two possible configuration scenarios.

[1] The OOB Server is installed on the same machine as MS SQL Server.

[2] The OOB Server is installed on a different machine to MS SQL
    Server.

Normally it is OK to do [1] but in some situations responsiveness
of the OOB Server is poor when installed on the same machine as
MS SQL Server. 

Microsoft introduced changes to MS SQL Server 7.0 which may cause
connections to an OOB Server and MS SQL 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. (e.g.  when someone is issuing a query, TaskManager
even stops displaying). If you are running MS SQL Server 7 and the OOB
Server is running on the same machine as SQL Server you should try the
following:

[1] The settings 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 NT is so
    busy servicing SQLServer, the accept() in the OOB Server can take
    some time due to NT not scheduling any other process.

[2] Move the OOB Server onto another Windows machine.

In general we would recommend running the OOB Server on a different
machine to SQL Server for best responsiveness.

Further evidence for this comes from a typical scenario we tried here
at Easysoft running an in house ODBC benchmark through OOB to an OOB
Server on the same machine as MS SQL Server and to an OOB Server on a
different machine to MS SQL Server. The tests cover the creation of
tables containing different column types, insertion via SQLExecDirect,
parameterised inserts and the selection of rows via various methods
including parameterised selects.

Times in seconds.
Test OOB_Server_on_same_machine OOB_Server_on_different_machine
T0             17.86                      16.05
T1             17.46                      15.83
T2             8.3                        7.2  
T3             0.51                       0.45 
T4             0.34                       0.32 
T5             1.66                       0.9  
T6             0.11                       0.1  
T7             8.16                       7.66 
T8             9.49                       8.01 
T9             8.07                       7.51 
T10            8.55                       8.1  
T11            0.17                       0.45 
T12            0.12                       0.23 
T13            7.9                        7.75 
T14            7.74                       7.39 
T15            7.78                       7.54 
T16            8.5                        8.4  
T17            7.09                       2    
T18            17.62                      4.45 
T19            17.82                      4.38 
T20            7.99                       2.58 
T21            7.95                       3.13 
T22            1.75                       1.57 
T23            1.64                       1.55 
T24            16.75                      4.84 
T25            16.67                      4.88 
T26            34.29                      8.21 
T27            27.88                      8.46 
T28            28.94                      10.27
T29            28.95                      10.31
T30            39.67                      13.41
T31            40.08                      13.74

Here we used MS SQL Server Enterprise Edition v8.00.760(SP3) on a 2
cpu machine with 1Gb with SQL Server configured to use only 1 cpu, NT
fibers disabled and boost priority disabled. The SQL Server machine
was doing nothing else. In the case where the OOB Server was on a
different machine it was running NT 4.00.1381 and was on the same LAN
as the MS SQL Server machine. Clearly, in this case it is a lot faster
to run the OOB Server on a separate machine to MS SQL Server.

Also, it is worth avoiding putting the OOB Server on your PDC 
(Primary Domain Controller) or SDC (Secondary Domain Controller)
if you have OS authentication enabled (the default) as experiments at
Easysoft have shown this to be faster.

4.1.2 Miscellaneous
===================

Here are a few tips for speeding up access to MS SQL Server through OOB:

[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 MS SQL Server you do not need to know the number of rows
    affected i.e. you are not going to need a useful result from
    SQLRowCount().

[2] SQLPrepare/SQLExecute is often more expensive than SQLExecDirect.
    the main reason for this is that SQL Server does not directly
    support SQLPrepare/SQLExecute. The MS SQL Server ODBC driver
    provides SQLPrepare/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 (see SQLSetStmtAttr
    attributes SQL_USE_PROCEDURES_FOR_PREPARE).

    If you are writing an application which 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/SQLExecute is going to start filling your
    tempdb up with multiple occurrences of same temporary procedure.
