Liren Large Software Subsidy 6/06.iso/a/a565/3.ddi/PERFORM.TX$/PERFORM.bin

Text File | 1994-01-13 | 11.5 KB | 313 lines

  --------------------------------------------------------
  Performance Tuning Tips for Microsoft Access Version 1.1
                       May 1993
  --------------------------------------------------------

          (C) Copyright Microsoft Corporation, 1993

This document provides tips on tuning the performance
of Microsoft Access by adding or changing entries in
the MSACCESS.INI initialization file and by using
transactions.


------------------------
How To Use This Document
------------------------

To view this document on screen using Microsoft Windows
Notepad, maximize the Notepad window.

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


--------
Contents
--------

Tuning [ISAM] Entries in MSACCESS.INI
Using Transactions To Maximize Data Throughput

-------------------------------------
Tuning [ISAM] Entries in MSACCESS.INI
-------------------------------------

Microsoft Access automatically provides default internal
settings that usually provide the best performance 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.

You can tune your Microsoft Access performance by adding
or changing entries in the [ISAM] section of the
MSACCESS.INI initialization file. (Setup installs this
file automatically in your Windows directory.) These
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 Microsoft Access will
retry a lock operation.

WARNING:  Determining the best settings for your system
configuration or application can be a time-consuming and
difficult process, usually involving much trial and error.
Casual users shouldn't try to add or change these entries.

Microsoft Access automatically includes a PageTimeout
entry in the [ISAM] section of the MSACCESS.INI file.
This entry sets the amount of time Microsoft Access holds
a data page in memory. For additional performance tuning,
you can add MaxBufferSize, ReadAheadPages, LockRetry, and
CommitLockRetry entries to this section. All of these
entries are described in this document.

Microsoft Access reads these settings at startup time.
You can change them while Microsoft Access is running, but
the changes won't take effect until you restart Microsoft
Access.


PageTimeout (shared data only)
------------------------------
The PageTimeout entry sets the amount of time, in tenths
of a second, that Microsoft Access holds a data page in a
memory "page cache" if the database has been opened for
shared access. Microsoft Access reads data in 2-kilobyte
blocks of records, or "pages."

For example, when Microsoft Access reads a data page, it
places the data in the page cache. If Microsoft Access
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.

Maximum setting: 2147483647 (maximum long integer)
Minimum setting: 0
Default setting: Setup automatically sets the PageTimeout
                 entry in MSACCESS.INI to 300. If you
                 remove the PageTimeout entry, Microsoft
                 Access uses a default PageTimeout
                 setting of 5.

EXAMPLE:  This example sets PageTimeout to 2 seconds.

PageTimeout=20


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

When Microsoft Access receives a read request, it first
checks the data pages in the page cache. If the page
isn't in the cache, Microsoft Access reads the data page
from disk and then places it in the page cache. Microsoft
Access 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.

Maximum setting: 4096
Minimum setting: 18
Default setting: If there is no MaxBufferSize entry in
                 your MSACCESS.INI file, Microsoft Access
                 uses a default setting of 512.

NOTE:  Because Microsoft Access reads data in 2K pages,
it always uses an even MaxBufferSize setting. If you
type an odd number, Microsoft Access uses a
MaxBufferSize setting of 1 less than the number.

EXAMPLE:  This example sets MaxBufferSize to 4 megabytes.

MaxBufferSize=4096


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

If Microsoft Access 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. If Microsoft Access
then detects a sequential read, it can make the next N
reads directly from the read-ahead cache.

The read-ahead cache increases the speed of sequential
reads, especially for reading data stored on a network. It
increases record updates per second (throughput) on a
network by sending a few large packets rather than many
small packets over the network.

If possible, Microsoft Access places the read-ahead cache
in the first 640K of memory to take advantage of the
ability of Windows to read from and write to conventional
memory.

If the read-ahead cache can't be placed in conventional
memory, Microsoft Access places it in high memory. Placing
the cache in high memory is less efficient than placing it
in conventional memory because Windows must copy all reads
and writes to its own buffer before completing the memory
operation.

Maximum setting: 31
Minimum setting: 0
Default setting: If there is no ReadAheadPages entry in
                 your MSACCESS.INI file, Microsoft Access
                 uses a default setting of 8.

EXAMPLE:  ReadAheadPages=16

NOTE: Microsoft Access creates a separate read-ahead cache
for each database open on your computer. Each library
database has its own read-ahead cache.


LockRetry
---------
The LockRetry entry sets the number of times Microsoft
Access retries a page-locking operation before it reports
an error. For example, if a user tries to lock a data page
that is already locked by another user, the attempt will
fail. Microsoft Access will try to lock the page N more
times, where N is the LockRetry setting.

If the attempt to lock the page still fails on the Nth
retry, Microsoft Access reports an error.

Maximum setting: 2147483647 (maximum long integer)
Minimum setting: 0
Default setting: If there is no LockRetry entry in your
                 MSACCESS.INI file, Microsoft Access uses
                 a default setting of 20.

EXAMPLE:  LockRetry=6


CommitLockRetry
---------------
The CommitLockRetry entry is used in conjunction with the
LockRetry entry to set the number of retries that
Microsoft Access attempts when a user tries to lock a
record on a data page that is already locked by a
transaction. (For more information about transactions, see
"Using Transactions To Maximize Data Throughput" later in
this text file.)

If a user tries to lock a data page that is already locked
by a transaction, Microsoft Access will try to lock the
page N more times, where N is the product of the LockRetry
setting and the CommitLockRetry setting. For example, if
the LockRetry setting is 5 and the CommitLockRetry setting
is 6, Microsoft Access will try to lock the page 30 more
times.

Maximum setting: 2147483647 (maximum long integer)
Minimum setting: 0
Default setting: If there is no CommitLockRetry entry in
                 your MSACCESS.INI file, Microsoft Access
                 uses a default setting of 20.

EXAMPLE:  Assuming a LockRetry setting of 6, this example
causes Microsoft Access to retry locking a page locked by
a transaction 36 more times.

CommitLockRetry=6


----------------------------------------------
Using Transactions To Maximize Data Throughput
----------------------------------------------

In a multiuser environment, you can further tune the
performance of Microsoft Access by using Access Basic
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.
(For more information about transactions, search Help for
"transactions" or see "Using Transactions" in Chapter 8,
"Manipulating Data," in Introduction to Programming.)

You can usually increase the record updates per second
(throughput) of an application by placing operations
that update data within an Access Basic transaction. The
following example from Chapter 8 in "Introduction to
Programming" (pages 118-119) illustrates how you can use
transactions in a function that transfers funds between
two bank accounts.

Function TransferFunds(FromAccount, ToAccount, CustomerID, Amount)
    ' Transfers Amount from CustomerID's FromAccount to ToAccount.
    ' Returns 0 if successful, nonzero error value if unsuccessful.
  Dim db As Database, Source As Dynaset, Destination As Dynaset
  On Error Resume Next
  Set db = CurrentDB()
  Set Source = db.CreateDynaset(FromAccount)
  Set Destination = db.CreateDynaset(ToAccount)
  If Err Then     ' Error with a field or table.
    On Error GoTo 0
    TransferFunds = Err
    Exit Function
  End If
  Source.FindFirst "[Cust ID] = " & CustomerID
  Destination.FindFirst "[Cust ID] = " & CustomerID
  If Not (Source.NoMatch Or Destination.NoMatch) Then
    If Source!Balance >= Amount Then
      BeginTrans
      Source.Edit
      Source![Balance] = Source![Balance] - Amount
      Source.Update
      Destination.Edit
      Destination![Balance] = Destination![Balance] + Amount
      Destination.Update
      If Err Then
        TransferFunds = -1  ' One or both updates didn't succeed.
        Rollback            ' Roll back changes.
      Else
        TransferFunds = 0   ' No error.
        CommitTrans         ' Commit changes.
      End If
    Else
      TransferFunds = -2    ' Insufficient funds.
    End If
  Else
    TransferFunds = -3      ' Account doesn't exist.
  End If
  Source.Close
  Destination.Close
End Function

TIP:  Because Microsoft Access locks data pages used in a
transaction until the transaction ends, using transactions
will prevent access to those data pages while the transaction
is pending. If you use transactions, try to find a balance
between data throughput and data access.