INFO: PERFORM.TXT: Performance Tuning Tips for VB and Access

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, version 3.0

SUMMARY

This article contains the complete text of the PERFORM.TXT file distributed with the Standard and Professional Editions of Visual Basic version 3.0 for Windows.

MORE INFORMATION

                     PERFORM.TXT

  Release Notes for Microsoft (R) Visual Basic (R) Professional Edition

                     Version 3.00

           (C) Copyright Microsoft Corporation, 1993

How to Use This Document

To view PERFORM.TXT on screen in Windows Notepad, maximize the Notepad window.

To print PERFORM.TXT, open it in Windows Write, Microsoft Word, or another word processor. Then select the entire document and format the text in 10-point Courier before printing.

Contents

Part    Description
----    -----------
 1      Running Multiple Data Access Applications
 2      Manipulating Secured Microsoft Access Databases
 3      Tuning [ISAM] Entries in VB.INI or <APPNAME>.INI
 4      Using Transactions to Maximize Data Throughput
 5      Minimizing Keyset Overhead When Working with Large Recordsets
 6      Performance Tips for Visual Basic Data Access

Part 1: Running Multiple Data Access Applications

Visual Basic and Microsoft Access both use the same database engine to perform their database-related operations. When these applications are run at the same time on the same machine, it is important to coordinate use of the database engine, since it is only initialized by the first program accessing a data access operation. Re-initialization does not take place until all programs using the database engine are ended and another started.

Proper database initialization is especially important when accessing external databases like dBASE, FoxPro, Paradox, or Btrieve. All of these require special notations in the initialization file.

Note that data access applications can take the form of one or more instances of:

• Microsoft Access.
• A Microsoft Access application.
• Visual Basic at design time.
• Visual Basic at run time.
• A Visual Basic application.

To ensure that all applications using the database engine function as intended, you must ensure that all initialization parameters pertaining to external databases are identified in various .INI files under section headings (such as [Installable ISAM], [Paradox Isam], [Btrieve ISAM]) and are copied into each of the .INI files pertaining to each data access application that can potentially be running at the same time. The list below shows where each program looks for its initialization information:

Program                           .INI file

Microsoft Access                  MSACCESS.INI
Microsoft Access application      MSACCESS.INI
Visual Basic at design time       VB.INI
Visual Basic at run time          VB.INI
Visual Basic .EXE application     <APPNAME>.INI

If you want to coordinate operations between an instance of a Visual Basic application (or .EXE), you will want to force Visual Basic to indicate the same initialization file that a second instance of your program or an instance of Microsoft Access will use. This way, regardless of which application starts (and initializes) the database engine, both applications will be using the same initialization parameters.

Part 2: Manipulating Secured Microsoft Access Databases

For Visual Basic to manipulate secured Microsoft Access databases, you must provide Visual Basic with the location of the SYSTEM.MDA file associated with that Microsoft Access database, a valid user name, and password. This can be accomplished in three steps:

1. Use the SetDataAccessOptions statement to point to a valid .INI file.

2. Include a path to the SystemDB with a valid VB.INI or <appname>.INI entry to locate the file. For example:

         [Options]
         SystemDB=C:\ACCESS\SYSTEM.MDA
   

3. Set the user name and password (if other than "admin" with no password) with the SetDefaultWorkspace statement.

Part 3: Tuning [ISAM] Entries in VB.INI or <APPNAME>.INI

You can enhance the database access performance of Visual Basic by:

- Adding or changing entries in the VB.INI or <APPNAME>.INI

  initialization file.

Visual Basic automatically provides default internal settings for most common database operations. However, advanced users may want to tune these settings to provide maximum performance for a particular system configuration or application.

Setup automatically installs <APPNAME>.INI in your Windows directory. These [ISAM] entries determine the sizes of data page and read-ahead caches in memory, the amount of time data is held in a page cache, and the number of times Visual Basic will retry a lock operation.

WARNING

Determining the best settings for your system configuration or application can be time-consuming and difficult, usually involving much trial and error. In addition, settings that seem optimal for one situation may not be optimal for others. Casual users should not try to edit these entries.

Visual Basic automatically includes a PageTimeout entry in the [ISAM] section of the VB.INI or <APPNAME>.INI file. This entry sets the amount of time Visual Basic holds a data page in memory. For additional performance tuning, you can add MaxBufferSize, ReadAheadPages, LockRetry, and CommitLockRetry entries to this section.

Visual Basic reads these initialization settings at startup time. They can be changed while Visual Basic is running, but the changes won't take effect until you restart Visual Basic or your application. As with all .INI settings that affect the database engine, these settings are fixed once the engine is initialized just before the first data access operation.

PageTimeout Entry (Shared Data Only)

The PageTimeout entry sets the amount of time, in tenths of a second, that Visual Basic holds a data page in a memory "page cache" if the database has been opened for shared access. Visual Basic reads data in 2K blocks of records, or "pages."

For example, when Visual Basic reads a data page, it places the data in the page cache. If Visual Basic receives another read request for the same data page during the timeout period, it reads the data directly from the page cache rather than from disk.

Note

Access Basic ignores the PageTimeout setting unless your code allows background processing by periodically calling the DoEvents statement or function.

PageTimeOut Settings

Maximum: 2147483647 (max Long integer) Minimum: 0 Default: 300

If you remove the PageTimeout entry, Visual Basic uses a default PageTimeout setting of 5. For example:

   PageTimeout=20     ; This example sets PageTimeout to 2 seconds.

MaxBufferSize Entry

The MaxBufferSize entry sets the amount of memory, in kilobytes, reserved for use as a page cache. Visual Basic reads data in 2K pages, placing the data in the page cache. Once the data is placed in the cache, Visual Basic can use it wherever it is needed -- in tables, queries, forms, or reports.

When Visual Basic receives a read request, it first checks the data pages in the page cache. If the page isn't in the cache, Visual Basic reads the data page from disk and then places it in the page cache. Visual Basic uses physical memory and if necessary virtual memory to create the cache. All pages stay in the cache until it is full and pages need to be flushed to make room for new reads.

MaxBufferSize Settings

Maximum: 4096 Minimum: 18 Default: If there is no MaxBufferSize entry in your VB.INI or <APPNAME>.INI

         file, Visual Basic uses a default setting of 512.

Note
----

     MaxBufferSize=4096  ; This example sets MaxBufferSize to 4 MB.

ReadAheadPages Entry

The ReadAheadPages entry sets the size, in data pages, of a "read-ahead" cache used by Visual Basic for sequential page reads. A sequential page read occurs when Visual Basic detects that data in a current read request is on a data page adjacent on physical disk to the data page of the previous request. Visual Basic uses the "read-ahead" cache only when it detects that a sequential read is taking place.

- If Visual Basic detects a sequential page read, it reads the requested

  page plus the next (N-1) pages in that direction, where N is the
  ReadAheadPages setting, placing the data pages in the read-ahead cache.

  reads directly from the read-ahead cache.

ReadAheadPages Settings

      file, Visual Basic uses a default setting of 8. For example:

           ReadAheadPages = 16

Note
----

LockRetry Entry

LockRetry Settings

         file, Visual Basic uses a default setting of 20. For example:

         LockRetry = 6

CommitLockRetry Entry

CommitLockRetry Settings

         <APPNAME>.INI file, Visual Basic uses a default setting of 20.
         For example:

         CommitLockRetry = 6   ; Assuming a LockRetry setting of 6,
                               ; this example causes Visual Basic to
                               ; retry a page locked by a transaction 36
                               ; times.

Part 4: Using Transactions to Maximize Data Throughput

In a multi-user environment, you can further tune the performance of Visual Basic by using transactions for operations that update data. A transaction is a series of operations that must execute as a whole or not at all. You mark the beginning of a transaction with the BeginTrans statement. You use the Rollback or CommitTrans statement to end a transaction.

You can usually increase the record updates per second (throughput) of an application by placing operations that update data within an Access Basic transaction.

Tip

Part 5: Minimizing Keyset Overhead When Working with Large Recordsets

When a query selects a large number of records from the database, Visual Basic only fetches the first row of that Dynaset or Snapshot and places the key to re-fetch that row in memory. Once a record is fetched or visited, it becomes a member of the recordset. As you "visit" additional rows of the recordset, the keys are stored in workstation memory (in a temporary table), and in the case of Snapshots, so is the data. If you move back to previously fetched rows, Visual Basic re-fetches the rows using the old key fetched from the temporary key table.

• If the database record is no longer there, you get a trappable error.
• If the record has changed, the new information is fetched from the database.

Moving to the end of the Dynaset or Snapshot does at least two things: First, it forces Visual Basic to visit all of the records in your recordset. Hence, all keys will be saved on the workstation. If this is a few hundred rows, this may not take long or take up more space than the workstation can handle.

However, for larger recordsets, a MoveLast operation may be far more than the workstation can save. When working with Snapshots, not only are the keys fetched, but the data for all records is also brought into local memory. Generally, you should avoid operations that fetch more rows than your user or workstation can deal with. Operations that must touch each record in a recordset may best be performed with an action query that consumes less system resources. In any case, your performance will not be severely degraded, either as you move forward until you have to swap, or hardly at all if you move backwards in the recordset -- even to the first record.

Note

Part 6: Performance tips for Visual Basic Data Access

The following tips are suggested for operations involving more than just a few records to increase the overall performance of your system.

1. When working with large recordsets (Dynasets or Snapshots), do not use the MoveLast method unless absolutely necessary.

   Moving to the end of a recordset requires Visual Basic to load all keys for the recordset into memory. In the case of Snapshots, not only are keys loaded into memory, but the data is also brought into workstation memory. If temporary memory space is exhausted, Visual Basic may be forced to swap this temporary cache to disk. In this case, Visual Basic will use space as addressed by the \TEMP environment variable. Once this space is exhausted, your application will trigger a trappable error.

2. When you want to access external tables fast, attach the table to your database instead of using the IN clause in a SQL statement or addressing the table directly.

   When Visual Basic needs to access your external table, all linkage information is resolved when the database is opened and does not have to be re-established and initialized each time the data is accessed (for example, with non-attached tables.

3. For reasonably small recordsets, especially where you do not intend to write to the recordset, use Snapshots instead of Dynasets.

   If possible, set the READONLY flag on the data control or DB_READONLY option when opening databases. This will permit Visual Basic to bypass significant logic to handle multi-user read-write access to your tables.

4. In cases where you are working with external ODBC databases, you will achieve maximum possible speed if you use SQL Passthrough instead of attaching or direct access that involves the Visual Basic database engine.

Keywords          : APrgDataAcc vbwin GnrlVb kbprg kbfasttip
Technology        : kbvba
Version           : WINDOWS:3.0
Issue type        : kbinfo