Introduction
When you formulate a query on a database, it can be handled either locally on the workstation or remotely on another computer or partially on both locations. On the local workstation, the Jet Engine handles the query. On the remote computer, the server database handles the processing.
The differences between local query processing and remote processing on the server must be taken into account when designing the application to ensure that a client/server database application works satisfactorily. This section explains the main differences, lists functions that are unknown to the server database and shows how operations at the ODBC interface can be traced.
Functionality Distribution Between Client and Server
When the application sends a query to the database, the Jet Engine checks whether the query can be processed by the server database alone. If this is possible, the query is passed to the server database which returns a result set and/or a message whether the query was successful or not. This is the simplest and less costly way to obtain the result.
The Jet Engine finds out on its own whether the query contains traps that make it impossible to the server database to execute the query without outside help. This happens always when the query contains references to functions or objects that cannot be resolved by the server database. As such a query requires the cooperation of several instances, it will always need more time than a query that can be executed by the server database alone. Therefore it is useful to familiarize oneself with the capabilities of the server database before designing an application and to formulate a query that uses these capabilities. Do not rely upon the Jet Engine that it transforms a query appropriately before executing it - it does it, but the loss in performance can be significant.
When formulating a query, your goal should be to simplify it to such an extent that the Jet Engine can pass it to the server database as it is. Section "tracing the odbc interface" below describes how you can find out that your efforts have been successful.
If you keep the following rules in mind, you can already relieve the Jet Engine:
· If your query accesses tables or views from multiple data sources (e.g. local tables and joined tables), the Jet Engine must perform at least a part of the query locally on the workstation.
· If a query contains a function that is unknown or whose name is unknown to the server database, the Jet Engine must perform the parameter of the function locally.
Unsupported Operators and Functions
When the Jet Engine receives a query for execution, it uses the "sqlgetinfo"" function of the odbc driver to ask for the facilities available on the server database. when the jet engine encounters a function or an operator that is unknown to the server database, then it performs this part of the statement locally.
When you use the following functions and operators in queries, you should ensure that they are supported by the server database or find out how they are called there.
General Operators
= - IS NULL
< > * IS NOT NULL
< / LIKE
> & MOD
< = AND NOT
> = DIV OR
+ IN
Mathematical Functions
ABS FIX SGN
ATN INT SIN
COS LOG SQR
EXP RND TAN
String Processing
ASC LEFT SPACE
CHR LEN STR
INSTR MID STRING
LCASE RTRIM TRIM
LTRIM RIGHT UCASE
Aggregate Functions
AVG MIN SUM
COUNT MAX
Type Conversion
CCur CLng CVDate
CDbl CSng
CInt CStr
Date and Time Functions
DATE DATEPART('ww') MONTH
DATEPART('ddd') DATEPART('www') NOW
DATEPART('hhh') DATEPART('yyy') SECOND
DATEPART('mmm') DATEPART('yyyy') TIME
DATEPART('nnn') DAY WEEKDAY
DATEPART('qqq') HOUR YEAR
DATEPART('sss') MINUTE
The following features are not supported by the server database:
· Outer Joins, if more than two tables are involved.
· The LIKE operator, applied to LONG or MEMO columns.
· Reports using several levels of grouping and totals.
· SQL extensions realized in the Jet Engine, such as "select top n" or "transform".
Tracing the ODBC Interface
Problems with the performance and functioning of the ODBC layer can make it necessary for you to trace the ODBC interface. However, this measure is to be used only as a last resort for getting to the bottom of things, since tracing all activities will slow down the execution of the application considerably and reading the trace file produced by this procedure requires special knowledge of the ODBC interface.
The versions 2.0, 2.5 and 3.0 are the best known versions of the Microsoft ODBC Manager. The following description refers to the ODBC Manager 2.0; later versions can slightly deviate from this description. Usually, the ODBC Manager is installed together with other Microsoft Software. It can only be obtained from Microsoft.
Interface between ODBC Driver and Microsoft Jet Engine
Click on the Options button to display the dialog box below, which allows you to activate and stop tracing (also automatically) and to define where the trace file will be stored.
The main difficulty in interpreting the trace file is in separating the essential from the irrelevant. Many functions are "negotiated" between the Jet Engine and ODBC driver; i.e., the Jet Engine calls a function and receives an error code on the basis of which it decides to follow a different strategy. This dialog is also part of the trace but not important for execution. The list of functions that can be called by the ODBC driver and their meanings are provided below and should help you to interpret the trace file.
SQLExecDirect: The application performs a non-parameterized
<SQL statement> query.
SQLPrepare: <SQL statement> The application prepares a parameterized query.
SQLExecute: The application performs a prepared,
(PARAMETERIZED QUERY) parameterized query.
SQLExecute: The application passes a data row that is
(GOTO BOOKMARK) clearly identified by the bookmark to
the workstation.
SQLExecute: The application passes several data rows to
(MULTI-RECORD FETCH) the workstation; each data row is defined by a bookmark.
SQLExecute: The application passes the content of a
(MEMO FETCH) LONG or MEMO column field to the workstation; each field is clearly identified by a bookmark.
SQLExecute: The application passes the content of a
(GRAPHIC FETCH) LONG or MEMO column field to the workstation; each field is clearly identified by a bookmark.
SQLExecute: The application passes the content of a data
(RECORD-FIXUP SEEK) row identified by any key to the workstation (This key need not be the bookmark key).
SQLExecute: The application updates a single data row
(UPDATE) identified by a bookmark.
SQLExecute: The application deletes a single data row
(DELETE) identified by a bookmark.
SQLExecute: The application inserts a new single data row
(INSERT) in a dynaset.
SQLExecute: The application inserts a new single data row
(SELECT INTO insert) in export mode.