Microsoft Access Database RAM Cache Is Faster Data File Method

Last reviewed: February 18, 1996
Article ID: Q107871
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


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 18, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.