Less P. Wright, Jr.
SQL-Win Database Solutions
Created: September 23, 1993
This article presents a compilation of various items to keep in mind while designing, coding, and implementing your Microsoft Access® database application to ensure maximum speed.
As a database developer, your primary mission is to enable your clients to efficiently collect, manage, and access the information they need. All other things being constant, the faster your database application can perform its activities, the more efficient a database application can be said to be. The purpose of this article is to highlight various tips and tricks to help you make your Microsoft Access® database applications as "efficient" as possible by optimizing it for speed, and thus hopefully assisting you in your primary mission as a database developer.
Laying the foundation for speed. We'll start from the ground up in terms of approaching the various sectors you'll want to look at for optimizing the speed of your database. The single most determining factor in your quest to improve your database's performance will be the hardware platform that you are implementing the database on. Obviously, the first rule to realize is that faster hardware = faster databases, with the CPU and RAM being the heart of the whole equation. Be sure that your clients are aware that Microsoft Access strongly recommends at least 8 MB RAM, and an 80486 for satisfactory performance. Normally you can take this for granted, but I have seen clients distressed about the speed of their Microsoft Access applications, and when asked about what hardware they were running on, I was informed that they were running 80386s with the actual MSACCESS.EXE running on the server. So just be sure that your clients are aware that while following the methods below will certainly improve performance, in the end their hardware will be the single most determining factor in terms of Microsoft Access performance.
Efficient disk access. Given a satisfactory hardware configuration, let’s move on to how you can tweak the speed of Microsoft Access on that platform. What may be the second most limiting factor for your database is disk access. Physical disk access will always be a bottleneck in terms of performance (compared with accessing the same data stored in RAM), so you will want to minimize disk access as much as possible. However, given that your application will almost always have some disk or physical media interaction, your goal should be to ensure that all disk access is as efficient as possible. The way to do that is to defragment the drive(s) on which your database resides and any other drives that it will be interacting with when you initially implement your database, and have the client periodically defragment the drive(s) thereafter to ensure that disk access is kept at top efficiency. This will minimize the time spent during the inevitable write/reads to the physical disk, and thus optimize the performance for this area.
Maximize RAM / minimize disk access frequency. The next topic is to minimize the frequency with which your database must interact with the physical drive. The key to doing this is to free up as much RAM memory as possible for use by your application. There are several steps you can take to do this.
MaxBufferSize = xxxxx
where xxxxx is your new setting for memory allocation by your application. (For example, MaxBufferSize = 2048 will allocate 2 MB for the Microsoft Access buffer.) How much should you allocate? Besides the pat "as much as possible" response, it is difficult to say. You will need to hand-tune this setting and try to ascertain the most your hardware platform can afford (1) without interfering with other applications that your users are likely to be using simultaneously, and/or (2) without disrupting the efficiency of the underlying operating system itself. (You will want to note that Microsoft Access configures itself according to the .INI file at startup; hence any modifications made to the MSACCESS.INI file will only be implemented upon the next startup of Microsoft Access, and will have no effect upon a currently running instance of Microsoft Access.)
Now that we've ensured a solid hardware platform, maximized disk access efficiency, and increased the RAM memory available for your application to use, let’s turn to optimizing the application itself:
Compact, compact. Be sure to compact your application frequently. When you are developing and using a Microsoft Access database, you are frequently adding and deleting data, code, and so on. The problem is that Microsoft Access does not effectively remove the space that was previously allocated for deleted objects, meaning that even though you deleted it, it is still occupying space in your database. Compacting the database will force Microsoft Access to remove the space that the deleted objects formerly occupied, resulting in a smaller database size and a much more efficient database. Don't overlook the importance of compacting in terms of performance—I've literally cut average query times by 30–50% merely by compacting database applications that had become bloated over time due to frequent use and lack of compacting. Just as you periodically defragment hard drives, you or your client should periodically compact the database to ensure that it is operating at peak efficiency.
Code only, please. While you may wish to prototype an application via macro automation, once you set out to optimize performance of your final application, be sure to rewrite all macros into code. The bottom line is that Access Basic code runs faster than macros. Unfortunately, there are three macro actions you will be prevented from rewriting into code: these are the Autokeys, Autoexec, and Addmenu actions, which have no Access Basic equivalent—hence you will be forced to use them. However, you can overcome the Autoexec limitation by having the Autoexec macro action be a one-line Runcode macro that calls your Access Basic Autoexec function.
Optimize datatype declarations. You should try to be as specific as possible when declaring datatypes within your code. While the variant datatype (the default unless otherwise specified) is extremely flexible, it also the most memory-expensive of any of the datatypes. Thus, if you know that the CheckBalance variable will not need precision beyond four decimal places and will never contain a null value, declare it as currency and not as a variant. You'll want to do the same for your procedure declarations (for example, Function PostCredits() as integer, instead of Function PostCredits()). Please make note of the fact that you will need to use the variant datatype (rather than a more efficient but stricter datatype) if you expect that the variable will ever be asked to handle a null value (otherwise you will receive an error). Along similar lines, be aware of the differences between integer division and floating-point division.
Always use Form/Report variables. Within your procedures, you want to ensure that you resolve as much of any given reference as possible in advance. What does that mean? Take the following example: If you wanted to refer to the text box called [Net Price] in code, you would use the following:
Mytempvariable = Forms![Customer Invoice]![Net Price]
For this command, Microsoft Access will first search the Forms object for the form named [Customer Invoice]. Once it locates the [Customer Invoice] form, Microsoft Access looks for the control named [Net Price] and takes the appropriate action. With the above statement, Microsoft Access went through two references to ascertain the final control we were specifying. If you plan on referencing any control on the [Customer Invoice] form again within that same procedure (function or subroutine), you can eliminate the redundant referencing that would occur the next time by using the following:
Dim F as form
Set F = Forms![Customer Invoice]
Now, your form variable F automatically refers to the form [Customer Invoice], allowing Microsoft Access to avoid searching through all of the forms in the database’s Forms object each time you
reference any object on the [Customer Invoice] form. To reference the control [Net Price], you would now simply use the following:
Mytempvariable = F![Net Price]
You can also do the same with the Reports object (for example, Set R = Reports![My Report]). While you won't get any speed gains if you access a form or report only once in a function, you will see significant time savings as soon as you start to access controls on the same form or report more than once. Thus, by saving Microsoft Access from having to do redundant referencing, you will greatly enhance the speed at which your procedures will execute.
Use Windows functions where applicable. Whenever relevant, use a Windows function call instead of performing the same action in Access Basic code. You will save development time because the function calls are already coded and optimized and because they are written in C (resulting in machine language executables)—whereas Access Basic code is compiled into a p-code format and interpreted line by line when executing. The most common example is custom .INI settings. You could use the Access Basic file functions to obtain a free file handle, open the file, read/write the file, and then close it, but why bother when you can simply use the GetPrivateProfileString and WritePrivateProfileString functions, which are faster, already coded, and waiting for you? (See “Enhanced Microsoft Access: Using the Win16 API”.)
The faster your Microsoft Access database application can accomplish its responsibilities, the better you will have achieved your mission of providing your clients with the ability to effectively collect, manage, and retrieve their data. I hope the above tips will help you achieve this goal.