Microsoft Access Performance Tips to Speed up Your Access Databases
by Dan Haught, Executive Vice President, and
Luke Chung, President of FMS
|
This paper is featured on:
|
Over 100 Tips for Faster Microsoft Access Databases
This paper outlines
specific performance tips you can use to improve the speed of your
Microsoft Access
applications. These tips apply to all versions of Access including Access
2010, 2007, 2003, 2002, 2000, and 97.
Some of the tips may contradict
each other but they are offered because bottlenecks differ in each
situation. Some tips may make things run faster on your
system, while others degrade performance. You should evaluate each tip as it applies to your
specific application running on your specific hardware.
Good luck!
Normalize Your Tables
Normalize your data so the same data is stored only in one place.
Remember that time is also a dimension and you may need to store historic
data as well. Read our paper on Data
Normalization Fundamentals for more detail.
Every Table Should Have a Primary
Key
Every table in your database should have
a primary key. This allows your database application to quickly locate specific records.
Additionally, you cannot create secondary indexes on a table's fields unless that table
has
a Primary Key.
Primary
Key Should be One Field and Numeric
The primary key should only be one field and ideally numeric and
meaningless. Primary keys define the uniqueness of each record which can be
accomplished efficiently with a single number. The easiest is using an
AutoNumber field in Access or Identity column in SQL Server. The primary key
is also used in each secondary index for that table, so the smaller the
better. Multi-field primary keys and non-numeric text fields are less
desirable.
That said, some tables should simply use text fields as primary keys
because they don't change much and the tables are relatively small. For
instance, a list of countries or states (no need to create a separate number
for each country or state).
Having a meaningless primary key means the index is stable even when data
changes. Otherwise, changes in the primary key have a ripple effect through
each secondary index and any other tables bound by referential integrity.
Tables Should Participate in Relationships
Related tables with one-to-one or one-to-many relationships should
implement referential integrity with cascading deletes and/or updates to
ensure orphan records are not created. With cascading deletes, the removal
of the parent record, automatically deletes the child records in the
corresponding table. Access (the Jet Engine) automatically takes care of
this for you without the need to write any code, which is great for
maintaining data integrity.
With a relationship established, a hidden index is created for the
foreign key, so links between those tables are faster in queries. The link
is also automatically drawn for you when you put the two tables on your
query designer.
Eliminate Unnecessary Subdatasheets
By default, Access creates subdatasheet views between related tables.
This is nice if you want it, but is a huge performance hit every time you
open the table. Set this property to None if you don't want it. Here's more
information on the
Subdatasheet Name Property.
Choose the Optimal Data Types
Choose the best data types for your
fields. By choosing the optimal data type, you can decrease both the disk space used to
store data, and the time it takes Access to retrieve, manipulate, and write data. The
general guideline is to choose the smallest data type possible to store a particular type
of data.
Add Secondary Indexes As Necessary
If your tables are large and you search on a field or use it in a join,
create a secondary index on the field(s). Secondary Indexes offer performance gains on an order of
magnitude.
Don't Over-Index
Just as it is important to add indexes to
fields that need it, it is important to avoid indexing fields that don't need it. Every
index adds to the time it takes the database engine to update, delete and add records.
Don't Index Fields with Lots of
Identical Data
Don't apply indexes to fields that
contain much the same data. For example, indexing a Yes/No field is almost always a
performance degrading operation. Similarly, if you have a number field that only contains
two or three values, an index wouldn't be a good idea. To check the number of unique
entries in an index, use the Access DistinctCount property. Compare this value to the
number of records in the table and you can quickly see if the index is doing you any good.
Keep Indexes As Small As Possible
When creating a multi-field index, index
only as many fields as are absolutely necessary.
Keep an Open Handle to Every Linked Database
You can significantly increase the performance of
opening tables, forms, and reports that are based on linked tables by having
Microsoft Access keep the linked table's database open. To do this, open a Database variable in Visual
Basic code using the OpenDatabase method. Keep this variable open as long as your
application is running. This forces Access to keep that database open, making access to
linked tables much faster.
For more information, read our tip on
Increasing the
Performance of Linked Databases
Minimize Record Navigation
Avoid record navigation wherever possible
on linked tables. Only use the PageUp and PageDown movements, and the Move last movements
when absolutely necessary.
Use DataEntry Mode
If you are only going to be adding
records, use the DataEntry command on the Records menu. This data access method is more
efficient for adding records because existing records are not read from the database.
Create a Data Entry Form
If you often have the need to add new
records to a linked table, consider creating an "Add Records" form and set that
form's DataEntry property to Yes. This prevents Access from attempting to retrieve all the
existing records in a linked table when you need is to enter new records.
Limit the Data Returned by Queries
Limit the number of fields and records
returned by using filters or queries. This reduces the amount of data that needs to be
read from the linked table, thereby according faster performance.
Don't Use Domain Aggregate
Functions
If a query is based on one or more linked
tables, avoid using functions (built-in or user-defined), or domain aggregate functions in
the query criteria. When you use domain aggregate functions (such as DLookup), Access must
fetch all records in the function's data source to execute the query.
Release Locks ASAP
To improve multi-user concurrency, assume
that other users will be trying to edit data in the same linked tables that you are using.
In other words, keep records locked only as long as is necessary.
Link ODBC Tables
If you are going to be accessing a SQL
database table, link the table permanently. This makes opening that table much faster in
subsequent attempts. This is because linked tables cache a lot of information about the
source table in your database, making the retrieval of the same structural information
unnecessary after the linked table is created.
Minimize Server Traffic
Minimize the amount of data returned from
the server. Do this by structuring your queries to return only the fields and records
needed. Reduce the number of fields returned, and put constraints on the query by using
the WHERE clause.
Use Snapshots When Appropriate
Don't open Dynaset type recordset object
on SQL database tables unless you need to add or edit records, or need to see the changes
made by other users. Instead, consider using Snapshot recordsets which can be faster to
scroll through. Of course, Snapshot recordsets can take longer to open since they require
a full read of the source data.
Use Dynasets for Large Record Sets
If you need to retrieve a large number of
records, use a Dynaset instead of a Snapshot. Snapshot type recordsets must load all
records from the data source before becoming available, whereas Dynasets are available as
soon as the first 20 or so records are retrieved. Also, when using a Snapshot against
large ODBC data sources, you run the risk of running out of disk space on your local
computer. This is because all data is downloaded into RAM until the amount of RAM is
exhausted. Then, the database engine creates a temporary database to store the contents of
the snapshot. In a nutshell, when you open a snapshot, you need at least as much disk
space as the largest table you are opening.
Take Advantage of the Cache
Use cache memory wherever possible when
using external SQL data sources. Microsoft Access forms and reports have automatic caching
mechanisms. When using recordsets in your Visual Basic code, use the CacheStart, CacheEnd
and FillCache methods to maximize cache effectiveness.
Don't Force Local Query Processing
Don't use query constructs that cause
processing to be done by Access on the local computer. The following query operations
force the Jet database engine to perform local data processing:
- Join operations between table that are
linked to different data source (I.e. a join between a SQL table and a linked Access
table)
- Join operations based on query that uses
the DISTINCT keyword, or a query that contains a GROUP BY clause.
- Outer joins that contain syntax that is
not directly supported by the remote database server.
- The LIKE operator used with Text or Memo
fields
- Multi-level grouping and totaling
operations
- GROUP BY clauses that are based on a query
with the DISTINCT keyword, or the GROUP BY clause.
- Crosstab queries that have more than one
aggregate, or that have field, row, or column headings that contain aggregates, or that
have an ORDER by clause
- User-defined functions, or functions that
are not supported by the remote server
- Complex combinations of INNER JOIN, LEFT
JOIN, or RIGHT JOIN operations in nested queries.
Use FailOnError For Bulk Updates
If you are using bulk update queries,
optimize performance on the remote server by setting the FailOnError property of the
Querydef object, or query to Yes.
Use ODBCDirect
ODBCDirect gives you almost direct access to server data through ODBC. In
many cases, it is a faster and more flexible way to hit server data than
that traditional Jet/Linked table technique.
Split Your Database into Two
Databases
You can improve performance and
application maintainability by splitting your Access database into two databases. The
"application" part of your database holds all objects except tables, and is
linked to the "data" part that contains the actual tables. For
more information, read our technical paper
Splitting
Microsoft Access Databases to Improve Performance and Simplify
Maintainability
Use a Current Workgroup
Information File
If you are using a workgroup information
file (SYSTEM.MDA) created with a previous version of MS Access, convert it to the current
version of Access for optimum performance.
Use the Access Performance
Analyzer
Microsoft Access has a useful performance tool
built right into the product. From the Tools menu, select Analyze, Performance. The
Performance Analyzer allows you to select all objects or specific objects, and then runs
an analysis looking for potential problems. The Performance Analyzer does not find all of
the items that Total Access Analyzer
does, but it does offer some tips.
Run the Access Table Analyzer
The Access Table Analyzer makes it easy
to properly normalize the data in your tables by breaking tables with repeated or
improperly structured data into two or more tables. This tool is available from the Access
Tools, Analyze menu
Reduce the Size of Graphics in your Access 2007 Databases
If you embed graphics on your forms or reports, Access 2007 can store
them much more efficiently. Access 2007 can convert graphics into much
smaller PNG formats to significantly reduce the size of your databases. This
does not affect graphics already on your forms and reports but helps if you
add new graphics or replace existing ones. To activate this feature, change
an Access setting. From the Access Office button, choose Access Options,
Current Database. At the bottom of the Application Options section, set the
Picture Property Storage Format to: Preserve source image format (smaller
file size)
Compact Your Database Often To
Reclaim Space
Compacting your database reclaims unused
space and makes almost all operations faster. You should do this on a regular basis. Also,
be sure to compact anytime you import or delete objects in your database, or compile and
save VBA code.
Learn more about Total Visual Agent for a system
administrative tool to schedule compact and backup your Access databases on a regular
schedule.
Make It Look Faster
If you have exhausted all other
performance optimization techniques, consider making your application "look"
faster. Do this by displaying status messages and progress meters as your application
loads forms, runs queries, and performs any other operation that may take a bit of time.
While this doesn't make your application run faster, it appears to run faster.
Compact Often to Update Statistics
Compact the database often. When you
compact the database, you reorganize records so that they are stored in adjacent spaces,
making retrieval faster. Additionally, compacting a database updates its data statistics,
which can be used by a query to run faster. You can force a query to recompile (which in
turn causes it to use the latest statistics) by opening it in design view, saving it, and
then running it.
You may want to defragment your disk
using a program such as the Disk Defragmenter that is part of Windows before compacting
your database. This leaves contiguous free disk space immediately after the database file.
In theory, this make future additions to the database occur faster. You may want to
experiment with this on your system.
Index Query Criteria Fields and
Join Fields
Index any fields in the query that are
used to set criteria. Index the fields on both sides of a join. Alternatively, you can
create a relationship between joined fields, in which case an index is automatically
created.
Search Access Help for: Index
Use Identical or Compatible Datatype In Join Fields
Fields that are joined in query should
have the same data type, or compatible data types. For example, the Long Integer DataType
is compatible with the AutoNumber data type.
Limit Fields Returned by a Query
Where possible, limit the number of
fields returned by a query. This results in faster performance and reduced resource usage.
Avoid Calculated Fields and IIF
Functions
Avoid calculated fields, or fields that
use expressions in subqueries. Pay special care to avoid the use of immediate If (IIF)
functions in sub-queries.
Don't Use Non-Indexed Fields for
Criteria
Avoid using non-indexed fields or
calculated fields for criteria restrictions.
Index Sort Fields
Index any fields you use for sorting. Be
careful not to over-index.
Use Temporary Tables to Eliminate Running the Same Queries Over and Over
If you are processing data that's used multiple times (for instance on
multiple reports), it may be faster to store intermediate results in
temporary tables rather than running a series of Select queries many times.
Create a temporary table to store your results. Empty the table and fill it
with your data using and Append query. You can then use that table for
multiple reports and forms.
Avoid Domain Aggregate Functions
on Foreign Tables
Do not use domain aggregate functions
(DLookup for example) in queries to access data from a table that is not in the query.
Link to the table and set criteria accordingly, or create a separate
aggregate (totals) query.
Use Fixed Column Headings in Crosstab Queries
Wherever possible, use fixed column
headings in your crosstab queries with the PIVOT syntax.
Use BETWEEN Rather than >= and <=
Between lets the search engine look for values in one evaluation rather
than two.
Use Count (*) To Count Records
If you use the Count function to
calculate the number of records returned by a query, use the syntax Count(*) instead of
Count([fieldname]). The Count(*) form is faster because it doesn't have to
check for Null values in the specified field and won't skip records that are
null.
Compile Each Query Before
Delivering Your Application
When you compact your database, its data
statistics are updated. When you then run a query, these updated statistics are compiled
in the query's execution plan. This sequence of events results in the fastest possible
query. Before you deliver your application, compact the database, and then force each
query to be recompiled. You can force a query to recompile (which in turn causes it to use
the latest statistics) by opening it in design view, saving it, and then running it.
Take Advantage of Rushmore Query
Optimization
Microsoft Jet uses Rushmore query
optimization whenever possible. Rushmore is applied to queries run on native Access data,
and on linked FoxPro and dBASE tables. Other linked data types do not support Rushmore
optimization. To ensure that Rushmore optimizations are used, create indexes on all fields
that are used to restrict a query's output. If you have queries that don't contain indexes
on fields used in the query's restriction clause, Rushmore is not used.
Link on Primary Key Indexes Whenever Possible
To make queries run faster, you should have indexes on all fields in the query that
join, restrict, or sort the data. Whenever possible, link on Primary Key
fields instead of other fields. Indexes are most critical on tables with
large numbers of records, so you may not see a difference on small tables.
You also don't need to add secondary indexes on fields that are part of
referential integrity.
Experiment With One-to-Many Restrictions
If you have a one to many join in query
with a restriction, try moving the restriction to the other side of the join. For example,
if the restriction is on the many side, move it to the one side. Compare performance
results for both versions, and choose the fastest one.
De-Normalize If Necessary
Although you should strive to normalize
your data for best performance and future flexibility, you may want to consider denormalizing some of your data
if you frequently run queries with joins would benefit from such data
restructuring.
Experiment With Sub Queries
Instead Of Joins
If you have a query with a join that is
not performing adequately, consider replacing the join with a sub query. In some cases,
the sub query may cause the overall query operation to run faster.
Limit the Number of Fields
Returned By Each Query
Where possible, queries should use a
Where clause to constrain, or limit, the number of records returned. This results in
faster performance and reduced resource usage.
Save the SQL of the Form RecordSource as a Query
We've seen situations where a saved query loads significantly faster than
the same SQL string stored as the RecordSource of a form. Somehow, saved
queries are optimized more than the SQL string behind the report.
Close Unused Forms
Close forms that aren't being used. Every
form that is open consumes memory that could be used by other parts of your applications.
Open Forms Hidden
Consider opening your application's most
commonly used forms when your application starts. Set their Visible properties to False,
and then make the Visible as needed. This frontloads some performance hits to the
application load event, making forms load faster when needed.
Use the DataEntry Property of a
Form
If a form's record source (the table or
tables accessed by the form's RecordSource property) contain a large number of records,
and the form is primarily used to add new records, set the DataEntry property of the form
to Yes. This precludes Access from having to retrieve existing records when the form
loads.
Don't Sort A Form's Recordset
Avoid sorting records in a form's
underlying record source unless a particular presentation order is absolutely necessary
for the form. This makes the form load faster.
Base Forms on Queries-Minimize
Fields Returned
Base forms and subforms on queries rather
than tables. By doing this, you can use the query to restrict the number of fields
returned, making the form load faster.
Use Lightweight Forms
Consider replacing Visual Basic code in a
form's module with calls to standard modules, or with hyperlink objects. Then set the
form's HasModule property to False. This turns the form into a Lightweight
form, making it load faster. Search Access online help for "Lightweight
Forms" for more information. In Access 2007, you can use embedded macros for
simple operations.
Index Fields Used to Link SubForms
to a Form
Index all fields in the subform that are
linked to the main form. Also index all fields in the subform that are used for criteria.
Set Editing Properties on SubForms
Set the subform's AllowEdits,
AllowAdditions, and AllowDeletions properties to No if the records in the subform aren't
going to be edited. Or set the RecordsetType property of the subform to Snapshot.
Reduce the Number of Fields in ListBox and ComboBox Row Sources
In the RowSource property of listbox and
combobox controls, include only the fields that are necessary.
Set AutoExpand on ComboBoxes to No
Set the AutoExpand property of comboboxes
to No if you don't need the "fill in as you type" feature.
First Field of an AutoExpand
ComboBox Should Be Text
In a combobox that has the AutoExpand
property set to Yes, the first displayed field should be a Text data type instead of a
Number data type. In order to find matches, Access needs to convert numeric values to
text. If the data type is Text, this conversion can be skipped.
Optimize Bound ComboBoxes
If the bound field in a lookup combobox
is not the displayed field, don't use expressions for the bound field or the displayed
field, don't use restrictions (the WHERE clause) in the row source, and use single-table
row sources wherever possible.
Move Linked Data Local for ComboBox and ListBox Controls
If the data that fills a list box or
combo box does not change often, and that data comes from a linked table, consider moving
that data's table into the local database. This can be a huge performance boost, especially if
the linked table is located on a network drive.
Group Controls On Multiple Pages
Consider grouping controls on multiple
pages. When the form loads, prepare only the controls on the form's first page. Defer
operations on other page's controls, such as setting the record source until the user
moves to that page. This makes the form load faster.
Only Load Subforms on Tab Pages when the Page is Selected
If a tab control contains several pages (tabs) with subforms on them, the
form will load quicker if the subforms on the tabs that aren't visible
aren't loaded right away. Since those tab pages aren't viewed yet, you can
defer loading the data until the user clicks on the tab. You'll take a
performance hit the first time the user clicks on it, but if they never
select the tab, you would never load the data. For more information, read
Microsoft
Access Performance Tip: Using Late Binding for Subforms
on Tab Controls
Close Forms That Contain Unbound
OLE Objects
Close forms that contain unbound OLE
Objects when they are not in use. When you activate an unbound OLE objects, the memory
used in that operation is not released until the form is closed.
Convert Subforms to Listbox or
Combobox Controls
Where possible, convert subforms to
listbox or combobox controls. It is far quicker to load a control than it is to load an
additional form as a subform.
Move Form Module Code to a
Standard Module
You can reduce a form's load time by
moving its code from the form module to a standard module. When the form loads, the form's
module doesn't need to be loaded. Of course, the standard module needs to be loaded at
some point, but once a standard module is loaded, it stays in memory until you close the
database.
Avoid Unnecessary Property
Assignments
Set only the properties that absolutely
need to be set. Properties assignments can be relatively expensive in terms of
performance. Review your form's startup code to ensure that you are not setting any form
or control properties that don't need to be set.
Use the Requery Method Instead of
the Requery Action
Use the Requery method instead of the
Requery action. The method is significantly faster than the action.
Give Visual Feedback
Give the user some visual feedback during
long operations. Consider using status meters to display a task's progress. At a minimum,
use the Hourglass cursor along with a status message.
Keep Forms Lightweight With
Hyperlinks
Hyperlinks in label controls make it easy
to open another Access object. So instead of placing command buttons on your forms to do
common operations, investigate the possibility of using a label control with the Hyperlink
properties. This approach eliminates the need for a command button, and its associated
event code.
Split Forms Into Multiple Pages
Consider using multi-page forms,
separated by the page-break character. This allows you to present only the controls
needed, and can reduce form-load time. For example, if your form has 10 combobox controls
that take a long time to fill, split the form into multiple pages using the PageBreak
control. Then, pick the 5 combobox controls the user is most likely to use and place them
on the first page. Place the remaining controls on the second page. Load time for the form
should be substantially reduced, especially if the queries filling those combo box
controls are complex.
Minimize the Number of Controls
Minimize the number of controls on your
form. Loading controls is the biggest performance hit when loading a form.
Avoid Overlapping Controls
Avoid overlapping controls. It takes
Access more time to render and draw controls that overlap each other than it does
non-overlapping controls.
Use Graphics Sparingly
Use bitmap and other graphic objects
sparingly as they can take more time to load and display than other controls.
Use the Image Control
Use the Image control instead of unbound
object frames to display bitmaps. The Image control is a faster and more efficient control
type for graphic images.
Save the SQL of the Report RecordSource as a Query
We've seen situations where a saved query loads significantly faster than
the same SQL string stored as the RecordSource of a report. Somehow, saved
queries are optimized more than the SQL string behind the report.
Don't Sort Report Queries
Don't base reports on queries that use an
ORDER BY clause. Access reports use their Sorting and Grouping settings to sort and group
records: the sort order of the underlying record set is ignored.
Avoid Expressions and Functions
In Sorting and Grouping
Try to avoid reports that sort or group
on expressions or functions.
Index Fields Used In Sorting and
Grouping
Index any fields that are used for
sorting or grouping.
Base Reports on Queries-Minimize
Fields Returned
Base reports and subreports on queries
instead of tables. By using a query, you can restrict the number of fields returned to the
absolute minimum number, making data retrieval faster.
Index Fields Used to Link Subreports
Index all the fields used to link a
subreport to a report.
Index Subreport Fields Used for
Criteria
Index all subreport fields used for
criteria. This will cause the subreport to link its records faster. Of course, remember
that over-indexing can cause performance bottlenecks when editing, adding and deleting
data.
Avoid Domain Aggregate Functions
in a Report's RecordSource
Do not use domain aggregate functions
(such as DLookup) in a report's recordsource property. This can have a significant
performance impact on how long it takes the report to open and display pages.
Use the NoData Event
Use the report's NoData event to identify empty reports. You can then
display a message and close the report. This is easier than running a
separate process to see if data exists for the report.
Avoid Unnecessary Property
Assignments
Set only the properties that absolutely
need to be set. Properties assignments can be relatively expensive in terms of
performance. Review your form's startup code to ensure that you are not setting any form
or control properties that don't need to be set.
Eliminate Unnecessary Reports
If a sub report is based on the same
query as its parent report, or the query is similar, consider removing the sub report and
placing its data in the main report. While this is not always feasible, such changes can
speed up the overall report.
Limit the Number of Controls on
Reports
Minimize the number of controls on your
report. Loading controls is the biggest performance hit when loading a report.
Avoid Overlapping Controls
Avoid overlapping controls. It takes
Access more time to render and draw controls that overlap each other than it does
non-overlapping controls.
Minimize Bitmap Usage
Use bitmap and other graphic objects
sparingly as they can take more time to load and display than other controls.
Use the Image Control
Use the Image control instead of unbound
object frames to display bitmaps. The Image control is a faster and more efficient control
type for graphic images.
Convert Macros to Visual Basic
Code
Convert macros to Visual Basic code. In
almost all cases, Visual Basic code runs faster than macros. This obviously
doesn't apply if you are using macros for a SharePoint deployment of your
Access forms.
Make an MDE File
If possible, make an MDE file out of your
database. An MDE file cannot become decompiled, so your Visual Basic code always runs at
top speed. Additionally, since no source code is stored in the MDE file, the database
loads faster and uses less memory.
Achieve the Compiled State
Module code is saved in two states in
your Access database: the source state, and the compiled state. The source state consists
of the contents of your actual modules, with full text including white space, procedure
and variable names, and comments. The compiled state is the executable version of your
code. All comments and white space have been removed, and a stream of executable
instructions has been produced-the code is ready to be run. The difference between these
two states can cause your application to run slower than molasses in January if you don't
understand them.
When you run a procedure, VBA checks to
see if the module containing the procedure is compiled. If it is, VBA simply runs the
code. If it is not compiled, VBA compiles it by loading the code into memory, performing a
syntax check, and compiling it into an executable format. If all these operations succeed,
it can then run the code. You probably surmised that this process of compiling is not a
free lunch-it does take some time. And herein lies the crux of the matter: compiling code
takes time, and compiling lots of code takes lots of time.
So if you want your database to run as
fast as possible, your task is obviously to reduce the amount of time Access spends
compiling your code to a bare minimum. In fact, in an ideal application, all your code
should be compiled and saved in the compiled state. So how do you go about this? Your
Access database (or project in VBA parlance) is said to be in a compiled state when all
modules, including form and report modules, are saved in both states in the database. This
means that the original source code is stored, as is the compiled version. In such a
state, Access runs much faster, because it can completely bypass the compilation process.
Getting your database into the compiled
state is actually rather easy:
- Open any module
- From the Debug Menu, select Compile and
Save All Modules
Your database is now in the compiled
state. This includes form and report modules (called class modules using Access
terminology) and standard modules. All VBA code that is called by your application is
immediately ready for execution. There is no need for compilation. This is all fine and
well, but is just as easy for your database to become decompiled. When you make certain
changes to your database, it automatically becomes decompiled, which means that the
compiled state that you created using the previous steps no longer exists.
How to Avoid Decompilation
So how do you avoid decompilation, or
loss of the compiled state? Any of the following actions can decompile your database:
- Modify any module code
- Make changes to code-bearing objects, such
as form, reports and controls, or create such code-bearing objects
So the bottom line is: to avoid
decompilation, don't do the above. It's not as bad as it seems. After all, your database
does not need to be in a compiled state while you are doing development work on it-it only
really requires the performance benefits of the compiled state when it is actually running
on your user's workstations. Therefore, if you follow these guidelines, you can enjoy peak
performance from your module code:
- During development, don't use Compile All
Modules. It is a waste of time, because the first time you make any changes to the module,
it will decompile, or reverse the effect of Compile All Modules. Rather, use the Compile
Loaded Modules option instead. This action only compiles the modules that are called by
the modules you have open. This is a much quicker operation, and results in the same
syntax checking that Compile All Modules does.
- When you are ready to deliver your
database for testing or live use, put it into the compiled state using the steps outlined
above.
Decompile to Eliminate Old VBA Compiled States
The Compact feature only compacts the data, but not the code portion of
an Access database. To flush the database of old code, shrink the size of
your database, and avoid the infamous Bad DLL Calling Convention error, use
the /decompile command occasionally. Read this reference for more
information on the
Microsoft Access decompile feature.
ActiveX Controls Should Have
References
If you are using an ActiveX control, your
database should have a Reference to the ActiveX (OCX) file. This allows you to use early
binding to bind variables to the control's objects, making it load and run faster. In most
cases, this is handled for you: when you insert an ActiveX control into a form or report,
Access automatically creates a Reference for that control.
Use Option Explicit
Always explicitly declare variables.
Ensure this happens in every module in your application by using the Option Explicit
phrase at the top of each module.
Choose the Most Efficient
Variable Type
Use the most efficient variable type
possible when declaring variables. For example, don't use a Long Integer when an Integer
will do. Avoid Variant types because the can be inefficient and slow.
Use Early Binding
Avoid using the Object data type. Instead
use the specific data type for the object you are working with. This allows Visual Basic
to employ "early binding" which can be substantially faster in many cases.
Assign Things to Object Variables
If you are going to refer to a property,
control, object or data access object (DAO) more than once, assign it to an object
variable.
Use the Me Keyword
Use the Me keyword instead of the
Form!FormName form to refer to the form of a form's module.
Avoid the Immediate If Function if Parts Run Other Code
Use the IIf (immediate if) statement
sparingly. IIf() does not employ "short-circuit" evaluation. This means that
both sides of the expression are always evaluated, which may not be what you
want since intuitively it looks like only the criteria satisfying side would
run.
Use Dynamic Arrays
Instead of fixed arrays, use dynamic
arrays with the Erase and ReDim statements to make better use of memory.
Take Advantage of Demand Loading
Organize and structure you modules to
take advantage of Visual Basic's demand loading architecture. When a procedure is loaded
from a module, the entire module is loaded into memory. By placing related procedures in
the same module, you can reduce the number of loads the Visual Basic has to make.
Eliminate Dead Code
Eliminate unused procedures and unused
variables. These elements use memory unnecessarily, and slow program load and execution.
Our Total Access Analyzer
program finds unused procedures, variables, and constants, and variables
assigned but not used.
Use Constants Instead of
Variables
If you are using data that is not going to change, put it in a constant
instead of a variable. This allows Visual Basic to compile the value into the constant
when the module is compiled, making the execution of that code faster.
Avoid Infinite Recursion
Avoid Infinite Recursion. Don't have code
that can call itself without having some type of short-circuit mechanism. This can lead to
"Out of Stack Space" errors.
Declare String Data Intelligently
Visual Basic allocates stack and heap
memory differently according to the type of strings you create. By understanding how this
works, you can write more efficient string code. String variables in procedures that are
non-static use space on the computer's stack. Use the following information to write code
that minimizes stack memory usage.
- Local fixed-length strings less than or
equal to 64 characters use 2 bytes for each character in the string. They don't use heap
memory.
- Local fixed-length strings longer than 64
characters use 4 bytes of stack memory for a pointer to the variable in heap memory and 2
bytes of heap memory for each character in the string.Local variable-length strings use 4
bytes of stack memory for a pointer to the variable in heap memory, and a variable amount
of heap memory according to the length of the string.
- If your code used a large number of
fixed-length strings of 64 characters or less, you can reduce stack usage by changing the
strings to local variable-length strings or making them static fixed-length strings.
Minimize OLE References
Every time you reference a Visual Basic
object, method or property, you are initiating one or more calls the OLE's Idispatch
interface. Each one of these calls takes time. Minimizing the number of such calls is one
of the best ways to make you code run faster. You can minimize OLE references by doing the
following:
- Use object variables instead of directly
referring to objects
- Use the With statement and the For Each
construct to minimize object references
- Move references to properties and methods
outside of loops
- When you refer to a member of collection,
do so with the object's index number. Referring to a collections member with a name or
expression introduces extra work, and therefore, more time.
Turn Off Screen Painting
Turn off screen painting during
repetitive operations that update the screen. Consider using the Application.Echo property
to turn off screen painting. Depending on the type of video card in your computer, this can
have moderate to dramatic effects of performance.
Don't Write Code When a Query Would Be Better
We've seen pages of VBA code written using various recordset operations
when one or a few queries would do the trick. Queries are not only faster
and optimizable, they're easier to understand and maintain. If you're not
familiar with how to use Select, Update, Delete, and Append queries, learn
them to best use Access or SQL Server.
Close Your Database a Couple of Times a Day
VBA dynamically loads code into memory as
needed on a module level. If a function is called or a variable is used, the entire module
containing that function or variable is loaded into memory. As you are developing your
application, you keep loading code into memory. Visual Basic for Applications does not
support dynamic unloading of these modules. Because of this, RAM will begin to fill up. To
boost development performance (I.e. to decrease the amount of time you spend as a
developer working on your application), you may want to close the database periodically to
unload the modules. Note that you do not have to close Access itself, just the database
itself. However, if you have library database code loaded, you should exit Access also.
It is especially important to close your
database after a Compile All Modules command. The Compile All Modules command pulls all of
your code into memory. Closing and reopening the application will unload the code and
enable you to develop faster because of the additional free memory.
If you are developing your application in
a single-user environment, you can improve your development performance by opening the
application exclusively. This allows Visual Basic for Applications to save and compile
faster by eliminating multiple-user save situations.
ActiveX Controls Should Have
References
If you are using an ActiveX control, your
database should have a Reference to the ActiveX (OCX) file. This allows you to use early
binding to bind variables to the control's objects, making it load and run faster. In most
cases, this is handled for you: when you insert an ActiveX control into a form or report,
Access automatically creates a Reference for that control.
Don't Use Expressions To
Determine Loop Boundaries
If use loop constructs in your VBA code,
such as For...Next, Do...While, etc. don't force VBA to evaluate the boundaries of the
loop each time. For example, instead of saying:
For intCounter = 0 To Forms.Count - 1
...
Next intCounter
use this:
intCount = Forms.Count - 1
For intCounter = 0 To intCount
...
Next intCounter
In the second example, VBA only has to
determine the value of Forms.Count once. In the first example, the value needs to be
determined for each iteration of the loop.
Use Seek Instead of Find
Use Seek instead of Find... whenever
possible. It uses indexes more efficiently and is the fastest data access method.
Search Access Help for: Seek method
Use Bookmarks For Record
Navigation
Whenever possible, use bookmarks to move
among records instead of using the FindNext method. The Jet engine can navigate to
bookmark values quicker than doing the sequential reads required by FindNext.
Use Indexed Fields for FindRecord
and FindNext
If you can't use the Seek method, and
must use the FindRecord or FindNext methods, use them on indexed fields. These methods are
much more efficient when used on a field that is indexed.
Search Access Help for: Find methods
Don't Use Transactions Unless Necessary
Microsoft Access lets you wrap table update code in transactions so you
can rollback incomplete attempts to save data. Any code operation that adds, modifies, or deletes data
can be enclosed in a
transaction using the BeginTrans...CommitTrans pair.
If you do not need to rollback your updates, you can avoid using
transactions and the overhead of maintaining a rollback log.
Use SHOWPLAN To See Query
Execution Plans
Microsoft Jet implements a cost-based
query optimizer in its query engine. During the compilation process of the query, Jet
determines the most effective way to execute the query. You can
view this plan using the ShowPlan registry setting.
To use this setting, use the Registry Editor that comes with your
operating system and add the following key to the
registry:\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\3.0\Engines\Debug
Under this key, add a string data type
entry named JETSHOWPLAN in all capital letters. To turn ShowPlan on, set the value of this
new entry to "ON". To turn the feature off, set the value to "OFF".
When the feature is on, a text file called SHOWPLAN.OUT is created (or appended to if it
already exists) in the current directory. This file contains the query plans.
Tune Database Performance with Jet
Registry Settings
Microsoft Jet lets you tune many new parameters to tweak
the engine for the best possible performance. However, this was somewhat difficult because
you could only modify these settings from their defaults by creating keys in the registry,
setting them to new values, and restarting Access and/or Jet. Version 3.5 of the Jet
engine makes this process a whole lot easier. These registry keys are located in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet
3.5.
Also, you can use the DAO SetOption
method to set these parameters at runtime. Using SetOption causes Jet to apply the changes
immediately-the changes are not stored permanently in the registry, and you do not have to
restart Access. If you want to get the maximum performance out of your data operations,
experiment with these settings.
For more information, search Access online help for "Customizing Windows
Registry Settings for Data Access Objects."
Threads
Experiment with the Threads setting in the registry. With this setting
you can increase or decrease the number of operating system threads
available to the Jet database engine.
MaxBufferSize
Use the MaxBufferSize registry setting to tune the data buffer used by
the Jet database engine.
UserCommitSync
Use the UserCommitSync registry setting to tune performance of explicit
transactions.
ImplicitCommitSync
Use the ImplicitCommitSync registry setting to tune the performance of
implicit transactions.
FlushTransactionTimeout
Use the FlushTransactionTimeout registry setting to tune the performance
of asynchronous write operations.
ExclusiveAsyncDelay
Use the ExclusiveAsyncDelay registry setting to tune the performance of
asynchronous mode writes to exclusively opened databases.
SharedAsyncDelay
Use the SharedAsyncDelay registry setting to tune the performance of
asynchronous mode writes to databases opened for shared access.
PageTimeout
Use the PageTimeout registry setting to change the delay for checking
other user's changes to the database.
LockDelay
Use the LockDelay registry setting to change how long Microsoft Jet waits
between retries on locked pages in a shared database.
MaxLocksPerFile
Use the MaxLocksPerFile registry setting
to tune performance by setting the maximum number of locks can be placed on a database
(MDB) file. For more information, search Access online help for "Customizing Windows
Registry Settings for Data Access Objects", or visit this Microsoft
Knowledgebase article
815281.
RecycleLVs
Use the RecycleLVs registry setting to determine how to memo, OLE and
hyperlink data pages are recycled.
Use ISAMStats To See Engine Detail
Microsoft Jet contains an undocumented function called ISAMStats that shows various internal values. The
syntax of the function is:
- ISAMStats ((StatNum As Long [, Reset As Boolean]) As Long
where StatNum is one of the following
values:
0 - number of disk reads
1 - number of disk writes
2 - number of reads from cache
3 - number of reads from read-ahead cache
4 - number of locks placed
5 - number of release lock calls
For example, to see the number of disk
reads, try the following code:
Dim lngReads As Long
lngReads = DBEngine.ISAMStats (0, False)
Debug.Print lngReads
To reset the number of disk reads back to
0, do this:
lngReads = DDBEngine.ISAMStats (0, True)
Remove Code From Your Startup Form
If you use a Startup form instead on an
Autoexec macro, place the Visual Basic code needed for that form in the form's module
instead of a standard module. Since Access has to load your Startup form, it will
automatically load that form's module, which is generally faster than loading a standard
module. This technique gives your application the appearance that it is loading faster.
Minimize Startup Form Code
Minimize the amount of code in your
Startup form. You may want to defer certain operations, such as opening data access
objects in code, or checking objects to a later time. This technique gives your
application the appearance that it is loading faster.
Don't Use ActiveX Controls on
Startup Form
Don't use ActiveX controls on your
Startup Form. These controls can take long to load than other controls and will
subsequently slow down the load time of your application.
Automate the Deployment of the Latest Version of Your Database
Centrally manage and simplify the automatic deployment of your Access
application to each desktop. When you update your application, you want to
distribute it quickly and easily. It's also important to make sure each user
is using the right version of Access. Our
Total Access Startup
program makes it easy to manage your database version and Access version by
letting you distribute a simple shortcut to launch your program.
Split Your Database
Split your database into an application
and data database. Place only the data database on the server, keeping the application
objects such as forms, reports and queries locally, where they can load and execute
faster.
Keep Static Data Local
Keep static data, such as lookup tables,
on the local machine. Update the local tables as necessary from the server. For example, a
lookup table containing the two-letter abbreviations for American states is not likely to
change anytime soon. When such a table is used in a data entry application, it is a performance bottleneck to retrieve that data from the server every time it is needed.
Instead, copy that table to your application's local database.
Set Options to Avoid Lock
Conflicts
Avoid lock conflicts by setting the
Refresh Interval, Update Retry Interval, Number of Update Retries, and ODBC Refresh
Interval Settings in the Tools, Options menu.
Tune Jet with the Registry
Investigate the registry settings
available for Access and Microsoft Jet for tuning data engine parameters. Search Access
online help for "Performance" for more information on these settings
Increase RAM
Increase the amount of RAM on your computer. Operating systems, Office,
and other programs eat up a lot of memory. With the cost of memory so low,
you should have at least 1 GB of RAM. With sufficient RAM, Access can
perform its queries entirely in RAM without the need to write temporary data
to disk.
Avoid NTFS Compression
If you are using NTFS compression under
Windows NT, move your Access application to a non-compressed drive. NTFS adds a
significant amount of overhead during disk operations, and Access applications are very
disk-intensive.
Make Sure Network Speed is Maximized
Most networks and network cards support 10/100 Mbps standards. Make sure
your machine is using the 100 and not the 10 if you're accessing data across
your network.
Keep Enough Local Disk Space Free
Access requires a fair amount of disk
space to operate, especially with large databases. Operations such as running large action
queries, adding lots of data, importing data, and compiling and saving module code can use
a lot of additional space on a temporary basis. Additionally, transactions, and compacting
the database can use a lot of disk space. A good rule of thumb is to have roughly 5-10
times the size of your largest database available in free local storage.
Keep Databases Local
Whenever possible, keep your databases on
a local drive instead of on a network drive. In almost all cases, local disk access is
faster than network access.
Install Access Locally
Do not install Microsoft Access, or its
related Microsoft Office components on a network. Most applications, including Access and
Office perform better when run from local drives.
Open Databases Exclusively
If you are opening a database, and no
other users need to use the database, open the database in exclusive mode. To do this,
check the Exclusive checkbox in the Open Database dialog.
Close Unneeded Applications
Free up memory by closing applications
that you aren't using. If you are using the System Agent from the Microsoft Windows Plus
Pack, consider turning it off to make more memory available for your access application.
Optimize Your Disk
Keep your disk in optimal shape by
deleting unneeded files and emptying your Recycle Bin. Use a disk defragmenter such as the
one found in the Windows 95 Accessories, System tools menu.
Close Access Occasionally to
Reclaim Leaked Memory
Almost all Windows applications
"leak" memory. This is due to data structures, variables, and other memory
consumers that are not correctly released by the application. By closing Access, you allow
Windows to reclaim this leaked memory.
Install Windows Locally
The Windows operating system should be
installed on a local fixed drive on your computer. Because of the number of components
involved in running Windows, a network installation of Windows causes poor performance,
especially for database applications.
Microsoft Access has
provided an incredibly powerful tool for the rapid development of database systems. With
this power comes complexity—and complexity can lead to less than desirable
performance. Use the tips in this paper to get your Access applications into top shape.
Additional Resources
Here are some additional resources that may help you create faster Access
databases and/or create them in less time:
Automated Database Analysis for Best Practices
For automated analysis of your databases,
check out our Total Access Analyzer program which is designed to examine
every object in your database in detail, provide documentation to help you understand what's
in it, how the objects interact, and offer suggestions for
applying Microsoft Access
Best Practices. It finds hundreds of types of errors, suggestions, and
performance tips specific to your database objects.
|
Microsoft Access Source Code Library
Why write all the code yourself? Get our Total Visual SourceBook code
library with 100,000+ royalty-free lines of code you can insert into your
Access, Office and VB6 projects. Written exclusively for this professional
code library, there's code you won't find anywhere else. More info on
Microsoft Access code.
|
Simplify Writing, Taking Over, and Delivering VBA Module Code
If you want to write better VBA code and do it in less time, learn about
our Total Visual CodeTools program which works with Access, Office, and
VB6. Total Visual CodeTools includes code builders, tools to standardize
existing code (indentations, variable names, adds error handling, etc.), and
tools for you to deliver more robust solutions. Learn more about our
Microsoft Access coding
tools.
|
Microsoft Access Consulting Services
FMS also offers custom software development services. If you're in over
your head or just don't have time, contact our team to see how we can help
you maintain, enhance, speed up and/or migrate your Microsoft Access applications.
Here's more on our
Microsoft
Access Developer Services.
|
Copyright © 1998-2011,
FMS Inc. All rights reserved. This information may not be republished, reprinted or
retransmitted in any form without the express written permission of FMS Inc. The
information provided in this document is provided "as is" without warranty of
any kind.
|
Email Newsletter
Developer Team Blog
Facebook
Twitter
