Document Version 1.0.1 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 document. 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.
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.
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.
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.
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:
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
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.
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).
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:
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.
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?
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?
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:
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.
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?.
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.
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.
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.
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:
There are a few remaining configurable parameters for the OOB client and server which can affect connection times.
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.
The RetryCount and RetryPause configurable parameters affect what the OOB Server does under two situations:
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.
You can modify these values in the OOB HTTP Administrator:
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.
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:
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 msThis is relatively slow.
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 entries
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) |
test | ODBC-ODBC Bridge | DBD::Proxy | ||||
---|---|---|---|---|---|---|
CPUu | CPUs | ElapsedCPUu | 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:
test | 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 | ElapsedCPUu | CPUs | Elapsed | ||
select | 7.31 | 7.17 | 32.19 | 3.6 | 0.14 | 16.12 |
which puts the final rows retrieved per second at:
test | 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.
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:
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.
There appear to be 4 main methods of ODBC access from MS Access:
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.
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.
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
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.
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.
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:
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.
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.
There are a number of entries in the OOB FAQ relevant to connecting to MS SQL Server.
With OOB there are basically two possible configuration scenarios.
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 SQLServer 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:
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.
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.
Here are a few tips for speeding up access to MS SQL Server through OOB:
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.