5
Performance Tuning

This chapter provides suggestions for tuning and customizing the way Oracle and MS Access work together.

ODBC Driver Selection

There are a number of ODBC drivers available for Oracle. In addition to the driver supplied by Oracle, drivers are also available from Microsoft, Visigenic, Intersolve, and others.

The performance of ODBC drivers can vary. If you are building a large-scale application, you will need to profile the different ODBC drivers with your application. The best way to determine the performance is with ODBC or OCI `spy' programs. These programs will show you the calls that Jet makes to the ODBC API and will show you the calls the Oracle ODBC driver makes against OCI.

Server-based Parameter Table

When MS Access first makes a connection to an Oracle database, it checks to see if a special parameter table is present. The table has the following definition:

create table MSysConf(

CONFIG NUMBER,
nValue INTEGER
)

The following table illustrates how Config and nValue column values can customize the way MS Access works with Oracle:

Config nValue Meaning

101

0

Do not allow the user to store the USERID and PASSWORD in attachments. This is important to set for secure installations.

101

1

Allow the user to store the USERID and PASSWORD in table attachments (default).

102

D

MS Access delays D seconds between each background chunk fetch when managing dynasets.

103

N

MS Access fetches N rows on each background chunk fetch when populating a dynaset.

Config	nValue	Meaning
101	0	Do not allow the user to store the USERID and PASSWORD in attachments. This is important to set for secure installations.
101	1	Allow the user to store the USERID and PASSWORD in table attachments (default).
102	D	MS Access delays D seconds between each background chunk fetch when managing dynasets.
103	N	MS Access fetches N rows on each background chunk fetch when populating a dynaset.

It is recommended that you create an MSysConf table in each Oracle database even if you plan on using the defaults. It will be easier to change the values in the table than to remember how to create and name the table at a later time.

Jet's Query Processor

Jet's Query Processor includes a cost-based optimizer that can make decisions on joining heterogeneous data via an ODBC connection.

Application Startup Performance

You can tune MS Access to speed up the process of establishing an ODBC connection at application startup time. In the case of MS Access 2.0, you make entries or changes in the MSACC20.INI file, found in the Windows subdirectory. In MS Access 95, you modify the registry entries under Jet > 3.0 > Engines > ODBC.

When MS Access opens a connection to an ODBC database, it goes through several steps to determine the level of functionality provided by the particular ODBC driver.

If you are relying on Oracle to provide full security, you can bypass attempts by MS Access to login to Oracle using its own user/group/password information with the following entry:

TryJetAuth = 0 (MS Access 2.0)
JetTryAuth = 0 (MS Access 95)
TryJetAuth = 0 (MS Access 97)

This step will save between one and two seconds when making the first connection to Oracle.

Runtime Performance

The most important issue related to runtime performance in a client/server configuration is the reduction of network traffic.

Form Loading Time

Performance is both perceived and actual. You should eliminate any requests for data from Oracle while a form is loading. You can do this by including a button that will retrieve information. You can also cache the last information that a form displayed locally.

Qualified and Restricted Queries

You can reduce network traffic by requesting only the columns you need from a table. You should also use the most restrictive qualifications possible to reduce the size of the query result set.

Snapshots vs. Dynasets

If possible, use Forward Only Snapshots to work with Oracle data, especially when the result set is small. For larger result sets, and for queries which must be updated, use a dynaset. Even if you are not going to update data, a dynaset will be faster than a snapshot if the result set is large. This is because only the key values are retrieved for the dynaset, not the full set of complete rows.

Drop-Down Lists

MS Access tries to minimize the amount of network traffic when it needs to populate a drop-down list box. When a snapshot is used to populate a drop-down list, MS Access uses the same batch fetching of records that it uses to populate a grid or a form. MS Access will fetch an initial `chunk' of data (100 rows) and then periodically retrieve sets of 100 rows from the server.

This process works smoothly unless you makes an entry that does not match a row already fetched. In this case, MS Access will begin fetching records from the server until a match is found or until all records are retrieved. If the returned set is large, this step can be lengthy and will freeze the User Interface.

MS Access does not share queries for drop-down lists. You cannot define a snapshot query and reference it from multiple list boxes and expect to use information that is retrieved once. Instead, MS Access will treat each `activation' of a query as independent.

If a drop-down list is short (< 100 records), it is probably sufficient to have MS Access perform its normal operations. If the list is long, you may want to build a synchronized shadow table in MS Access. Store the table information locally in MS Access and periodically synchronize the local table with information from Oracle.

5Performance Tuning