5
Application Performance Tuning

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

This chapter includes the following sections:

• Selecting ODBC Drivers
  
  
• Using Server-based Parameter Table
  
  
• Improving Application Startup Performance
  
  
• Speeding Up Microsoft Jet database engine Operations
  
  
• Eliminating the Microsoft Jet Database Engine

Selecting ODBC Drivers

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, MERANT, and others.

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

Using Server-based Parameter Table

If you are administering an Oracle database that uses Microsoft Access as a front end, you can create a special parameter table in the Oracle database named MSysConf to help you control communication between the Microsoft Access application and the Oracle database. When Microsoft Access first makes a connection to an Oracle database, it checks to see if the MSysConf table is present within the Oracle user schema that you are connecting to. You can use the MSysConf table to prevent storage of user logon information within a linked table, which increases the security of the application. You can also use the MSysConf table to optimize the record retrieval characteristics. The following table illustrates the structure of the MSysConf table within Oracle:

Config

NUMBER(10)

chValue

VARCHAR2(255)

nValue

NUMBER(10)

Comments

VARCHAR2(255)

The following table illustrates the Config and nValue column values that you can use to customize the way Microsoft Access interfaces with Oracle:

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

Improving Application Startup Performance

You can tune Microsoft Access to speed up the process of establishing an ODBC connection at application startup time. When Microsoft Access opens a connection to an ODBC database, it determines 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 Microsoft Access to login to Oracle using the user, group, and password information of Microsoft Access.

The value of the TryJetAuth attribute determines whether login authentication is bypassed. The default value of the TryJetAuth attribute, equal to 1, causes Microsoft Access to attempt connection to the Oracle database using the Microsoft Access login information. You should set the TryJetAuth attribute equal to 0 in order to bypass the Microsoft Access login authentication. This step saves between one and two seconds when making the first connection to Oracle.

In Microsoft Access 2.0, you change the TryJetAuth attribute by modifying the msacc20.ini file, located in the Windows subdirectory. In Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000, you must modify the Windows registry using the Registry Editor (regedit). Table 5-1 displays the registry key location depending on the version of Microsoft Access you have installed on the system.

Table 5-1 Registry Key Location

Speeding Up Microsoft Jet database engine Operations

In order to ensure that the Microsoft Access forms and reports operate against the data residing in the Oracle database, the Migration Workbench generates ODBC link tables in the Microsoft Access database. These ODBC link tables reference the tables in the Oracle database. For more information, see the Modifying the Microsoft Access Database. Using ODBC link tables within an Microsoft Access application incurs a performance penalty. Specifically, although the connection time is faster due to caching of some ODBC data source information within the ODBC link table object, DML operations using ODBC link tables are slower.

The CurrentDB object is used extensively within Microsoft Access VBA code to reference database information. Prior to migration, the CurrentDB object references the local Microsoft Access tables. After the Migration Workbench has modified the Microsoft Access database, the CurrentDB object references the ODBC link tables.

In order to speed up Microsoft Jet database engine operation with ODBC data sources, it is necessary to minimize the direct use of the ODBC link tables. One way of doing this within the VBA code is to use the Jet Workspace Database object instead of the CurrentDB object. The Database object does not reference the ODBC link tables. Instead, it requires a direct connection to the Oracle tables.

The modifications required within the VBA code are minimal as both the CurrentDB object and the Database object expose the same set of properties and methods.

To replace the CurrentDB object references with Database object references:

1. Create a global variable of type Database. For example:
   
   

   Global dbOracle As Database
   
   

2. During the initialization stage, create the Database object by creating a connection to the Oracle database as follows:
   
   

   Dim dsn As String
   Dim uid As String
   Dim pwd As String
   Dim odbcConnectStr As String
   
   dsn = "ora817"
   uid = "system"
   pwd = "manager"
   
   ' build up the connect string
   odbcConnectStr = "ODBC;DSN=" & dsn & ";UID=" & uid & ";PWD=" & pwd
   
   ' use Microsoft JET Workspace to make a connection to the Oracle database
   Set dbOracle = DBEngine.Workspaces(0).OpenDatabase(dsn,_
   dbDriverCompleteRequired, False, odbcConnectStr)
   
   

3. Replace all references to CurrentDB with a reference to dbOracle.

   Note: Since the Database object references the Oracle tables directly, it is important to specify the Oracle table names instead of the Microsoft Access table names when you are performing table operations. The table names can be different between Microsoft Access and Oracle due to the different object naming restrictions imposed by both databases.

You cannot remove the ODBC link tables from Microsoft Access. This is because although the VBA code no longer references the ODBC link tables, the forms and reports within the database still directly reference these ODBC link tables. The effort involved in modifying the forms and reports so that they do not reference the ODBC link tables is considerable. It involves re-coding forms and reports as well as reprogramming all record navigation and manipulation. The Microsoft Jet database engine has not been bypassed as a result of these modifications. Because the Microsoft Jet database engine is still in use, a full table scan is still performed when retrieving records from the Oracle database. For more information on removing the Microsoft Jet database engine bottleneck, see the Eliminating the Microsoft Jet Database Engine topic.

Eliminating the Microsoft Jet Database Engine

The principal reason for the significant performance degradation of a Microsoft Access application using an ODBC data source is the Microsoft Jet database engine. The problem with Microsoft Jet database engine is that it always performs a full table scan when a table is queried. This means that when a Microsoft Access form or report references an Oracle table through an ODBC link the Microsoft Jet database engine must retrieve the entire contents of the table into local memory before it can perform the query. Eliminating Microsoft Jet database engine often results in an application that is faster than the original Microsoft Access application.

The alternative to using the Microsoft Jet database engine is to use ODBCDirect. ODBCDirect uses a Connection object that represents a pure connection to the destination Oracle database. When using ODBCDirect, the Microsoft Jet database engine is not loaded. When using ODBCDirect, all SQL statements are sent unaltered to the Oracle server for manipulation. These SQL statements are evaluated and interpreted on the Oracle server, reducing network traffic significantly. Only the subset of record information queried is sent back over the network to the Microsoft Access application.

Modifying the VBA code to use the ODBCDirect Connection object is not a straightforward procecdure. The property and method list of the Connection object is very different to those of the CurrentDB and Database objects. In addition, because the SQL statements are sent over the wire for manipulation by the Oracle server they must be in the correct Oracle syntax. Therefore, you should consider the following:

• Modify all SQL statements within the VBA code to comply with Oracle SQL syntax.
  
  
• Change SQL statements containing Microsoft Access specific expressions to Oracle syntax.
  
  
• Replace built-in Microsoft Access functions used within a SQL statement with the equivalent Oracle functions.

To replace the CurrentDB or Database object references with the Connection object references:

1. Create a global ODBCDirect Connection object reference as follows:
   
   

   Global connOracle as Connection
   
   

2. Create an ODBCDirect work space as follows:
   
   

   Dim DSN As String
   Dim UID As String
   Dim PWD AS String
   
   DSN = "ora817"
   UID = "system"
   PWD = "manager"
   
   wsODBC = DBEngine.CreateWorkspace(DSN, UID, PWD, dbUseODBC)
   

3. Create an ODBCDirect Connection object by connecting to the Oracle database as follows:
   
   

   Dim ODBCconnectStr AS String
   
   ' build up the connect string
   ODBCconnectStr = "ODBC;DSN=" & DSN & ";UID=" & UID & ";PWD=" & PWD
   
   ' open a connection to the Oracle database
   Set connOracle = wsODBC.OpenConnection(DSN, dbDriverCompleteRequired, False, 
   ODBCconnectStr)
   

4. When manipulating data in the Oracle database, simply pass the SQL command directly to the Oracle server for processing as follows:
   
   

   Dim sql As String
   set sql = "select * from emp where empno > 10"
   connOracle.execute sql
   
   

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 versus 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 that you must update, use a dynaset. Even if you are not going to update data, a dynaset is faster than a snapshot when the result set is large.

Comparing Use of Drop-Down Lists

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

This process works smoothly unless you make an entry that does not match a row already fetched. In this case, Microsoft Access begins 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. It also may cause problems for you within the user interface.

Microsoft Access does not share queries for drop-down lists. A snapshot query is not reusable across multiple list boxes. Instead, Microsoft Access treats each activation of a query independently.

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