Microsoft Access Database RAM Cache Is Faster Data File Method

ID: Q107871

3.00 WINDOWS kbprg

The information in this article applies to:

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, version 3.0

SUMMARY

Before Microsoft Access was available, many Basic programs read disk files into string arrays. Then wrote them back to disk. You may be able to improve the speed and flexibility of file operations by instead using a Microsoft Access database with a RAM cache.

MORE INFORMATION

Visual Basic limits variable-length string arrays to 64K bytes. However, fixed-length string arrays are limited only by memory, and a Microsoft Access database can contain many megabytes of data.

The Microsoft Access database engine used by Visual Basic uses a robust, RAM-based caching scheme for speedy data access.

You can open a Microsoft Access database using a dynaset, which is a set of pointers to the original data. When you update the dynaset, you actually update the underlying physical table on disk. Dynasets are live; they are not just copies of the data. You can specify a large Microsoft Access data buffer, or cache, in RAM to obtain speeds faster than older Basic file input/output methods.

Please read the PERFORM.TXT file for performance tuning tips for data access in Visual Basic version 3.0 and Microsoft Access version 1.1 for Windows. The PERFORM.TXT file is in your Visual Basic directory. A related PERFORM.TXT file is installed with Microsoft Access.

A copy of the PERFORM.TXT file can also be found by searching for the following words in this knowledge base:

   PERFORM.TXT and Visual and Basic

Using a table object variable usually gives faster access to a database than using a data control bound to a database.

Using a Microsoft Access Data Buffer in RAM

You can specify the size of the Microsoft Access buffer, or cache, in RAM by adding an [ISAM] section to your initialization file. Specify the [ISAM] section in the VB.INI file for Visual Basic, or in the <appname>.INI file for your Visual Basic application. For example, use the following cache if your computer has at least eight megabytes of RAM installed:

   MAXBUFFERSIZE = 4096

This gives the Microsoft Access engine a four-megabyte dedicated cache.

You can also specify ReadAheadPages and other parameters. Please read the PERFORM.TXT file for more information.

Using the above Microsoft Access caching scheme can be faster than using Basic file input/output statements with a disk-caching product such as the SMARTDRV.SYS driver that ships with most MS-DOS versions 4.x and later.

In one test on a PC with a 486 chip and 50-megahertz clock speed, a Seek method used on a cached table took less than .6 milliseconds. FindFirst methods with simple criteria took only .7 to .9 milliseconds. Performance is about the same whether you use the primary key or any other index that is either defined as unique or resolves to a single record pointer.

For sample data manager source code, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q99643

TITLE     : Data Manager Source Code Available on the Internet

Additional reference words: 3.00 KBCategory: kbprg KBSubcategory: APrgDataOther

Keywords          :  
Version           : 3.00
Platform          : WINDOWS

Last Reviewed: February 20, 1996