Optimize Microsoft Access Improve speed

Summary

This article describes several points to note when designing, writing, and implementing a Microsoft Access database application to ensure maximum speed.

Introduced

As a database programmer, your primary task is to enable your users to quickly select, manage, and access the information they need. The rest of the work is almost static, and as long as your database application executes faster, your program becomes more efficient. The purpose of this article is to highlight a variety of techniques and strategies that can be used to improve speed and make your Microsoft Access database application more efficient, which will help you accomplish your main mission as a database developer.

Hardware

The root of the problem of aiming speed

In order to improve the speed of your database program, you will naturally think of some of the most basic aspects of the impact speed, and we will start with dealing with these parts. When you try to improve the performance of your database program, the hardware platform that your program runs on will be the only determinant. Obviously, the first principle of implementing optimizations should be faster hardware = faster databases, and of course CPU and RAM are at the heart of this equation. I'm sure you'll notice what Microsoft Access declares, you need at least 8MB of RAM and a 80486 CPU to achieve a satisfying performance. In general, users are aware of this, but I've seen many users frustrated with the speed of their Microsoft Access execution, and when I asked them what machine they used to run the program, they told me it was a 80386, And is a MSACCESS.EXE running on a server. So, while trusting your users to improve their program performance through the following methods, it is always important to emphasize that the hardware is the key issue during Microsoft Access execution.

A valid disk access

Assuming you have a satisfactory hardware configuration, let's discuss how you can improve the speed at which Microsoft Access is running on this platform. As far as this problem is concerned, the second factor that affects the performance of your database program is disk access. During execution, access to the physical disk is always a bottleneck (compared to accessing the data stored in RAM), so you should minimize access to the disk. However, since your application always has to deal with some disk or some other physical device, your goal should be to ensure that all disk access is as effective as possible. The way to do this is to regularly defragment your disk data, organize the disk drives where your database resides, and all the disk drives you need to run your database program, and you should also remind your users to periodically clean up disk fragments to ensure that disk access remains valid. This minimizes the time spent reading and writing to physical disks, although these are unavoidable, while optimizing the performance of the entire system.

Maximize RAM/Minimize the frequency of disk access

The next topic is to minimize the frequency with which your database programs deal with physical disk drives. The key to doing this is to free as much RAM space as possible for your application. To achieve this, you need to do the following:

Increase the size of the maximum buffer

The first involves your setting of the maximum buffer size. The maximum buffer size refers to the amount of RAM that Microsoft Access retains as internal storage space. The larger the storage space, the more likely the user needs data to be found in RAM, while reducing the frequency of access to physical disks. The minimum buffer that Microsoft Access needs is 512KB, and if your hardware system has more than 4MB of RAM, you can allocate more for it to Microsoft Access (which also increases system performance). To do this, simply add the maximum buffer setting in the Msaccess.ini (in your Windows directory) file, and use your text editor, such as Notepad, to find the [Option] segment in the file and add the following sentence:

MaxBufferSize = xxxxx

XXXXX is a new setting for your application to allocate storage space (for example, setting maxbuffersize=2048 will allocate 2MB of buffer space for Microsoft Access). So, how much should you allocate to be appropriate? Apart from "as many as possible", the others are hard to say. You need to manually adjust this setting and try to get your hardware to provide the following: 1 The program that does not prevent the user from running at the same time, 2 does not affect the operating efficiency of its own underlying OS (you may want to pass the program when it starts). INI file to view the configuration of Microsoft Access itself, any modifications to the Msaccess.ini file will only work when Microsoft Access starts the next time, and for the currently running Microsoft Access programs have no effect whatsoever).

Avoid loading wizards

To free more memory for use by Microsoft Access, another step you can take is: if you don't use wizards, then don't load it. You can open the Msaccess.ini file, find the [Libraries] section, and precede the phrase "Wizards.mdb=ro" with a semicolon, thus avoiding automatic load wizards. Doing so will allow 315B of RAM to be available to Microsoft Access for your own use, not only to improve uptime performance, but also to reduce the average installation time of approximately 10 seconds per application.

Remove Wallpaper/Background bitmap

Unbinding a standard Windows wallpaper background frees up any 25K to 350K of RAM space. Releasing a complex bitmap frees up more memory space.

Create a permanent swap file

Creating a permanent swap file (as opposed to a temporary file) will increase your extra performance. In this context, you can turn to a help document on this, which will guide you through creating the file on the machine running the database application.

So far, we've solved the problem of hardware platforms, maximum disk access efficiency, and added effective RAM storage space to your applications, and then let's move on to optimizing the application itself.

Features of Microsoft access itself

Compression, compression

Be sure to compress your program code regularly. When you are developing and using a Microsoft Access database, you need to constantly add and remove data, code, and so on. The problem now is that Microsoft Access does not effectively release allocated but deleted object space, which means that even if you delete an object, the object still occupies space in your database. Compacting the database forces Microsoft Access to actually delete these objects and reclaim the space it occupies, making your database as small as possible but more efficient. I have reduced the average time of Word query by 30%--50%, and this is done simply by compressing the database programs that have become overly inflated due to frequent use and lack of compression, so don't ignore the importance of the compression process in the process of running the program. Just as you want to periodically erase hard disk data fragments, you and your users should also compress the database regularly to ensure that it is always running the most efficiently.

Use code only

You might want to build an application model with macro actions, and once you start to optimize the final performance of your program, be sure to rewrite all the macros into code. This is mainly because Microsoft Access code runs much faster than macros. Unfortunately there are three macro actions you can't change them into code, these three macros are AutoKeys, AutoExec, and AddMenu operations, and the three macros don't have equivalent classes in Microsoft Access, so you can only be forced to use them. However, you can overcome AutoExec limitations and just define AutoExec to invoke your Access Basic AutoExec function.

Optimizing Data Type declarations

When you declare a data type in your code, you should try to be as precise as possible. Because if the type of a variable (if there is no special declaration, the default is a mutable type) is very flexible and free, then this type will also be the most wasteful type of memory. This way, if you know that the check balance this variable does not require more than 4 decimal digits, then define it as a type rather than a mutable type. The same can be done with the definition of your process function (for example, to declare a function postcredit () as a cosmetic, not a function postcredit ()). Note the fact that if you estimate that a variable will be processed into a null value, then you need to define it as a mutable type rather than a certain strict data type, or you will get an error message. Also, pay attention to the difference between shaping Division and floating-point division.

Often use From/report variables

In your program, you need to ensure that you can solve as many inquiries as possible in advance. What does that mean? For example, if you want to refer to a text box named [Netprice] in your code, you can use a statement like this:

mytemprariable=forms! [Customer invoice]! [Net Price]

For this directive, Microsoft Access first searches the forms object for a table named [Customer Invoice], and once it finds the table, Microsoft Access then looks for the control named [Netprice] and does the right thing. As you can see from this example, Microsoft access finally determines the control we specify after two queries. If you want to requery any control in the [Customer Invoice] table in the same program (function or child function), you can delete the extra statements that might appear the next time, and use only the following statement:

Dim F as Form

Set F = forms! [Customer Invoice]

Now, your form variable will automatically point to Form[customer Invoice], allowing Microsoft Access to avoid searching the database's form object every time you need to find any of the objects in the [Customer Invoice] table. To refer to control [Netprice], you simply need to use the following statement:

mytempvariable=f! [Netprice]

You can also do the same thing with the subject (for example, set r=reports! [Myreport]). When you are only accessing a form or a list once in a function, you may not get much benefit in terms of speed performance, but once you start the access operation in the same form or in the list more than once, you will see a noticeable decrease in time. Then, by keeping Microsoft Access from making unnecessary lookup, you will greatly improve the speed of your program.

Use Windows functions where you can apply

Whenever relevant, you can always use a Windows function call instead of the Access Basic code to perform the same operation. This will save you time for development because Windows function calls are already coded and optimized, and because they are written in C (machine executable), Access Basic code is compiled into P-code and needs to be interpreted one line at a time. One of the most common examples is the Custom.ini setting. You can use the Access Basic file function to get a free file pointer, open the file, read/write the file, and then close it. But if you can simply use the getprivateprofilestring and writeprivateprofilestring functions to implement them quickly and with coded optimizations handy, why bother? (Reference: "Enhanced Microsoft access:using the Win16 API")


The faster you run your Microsoft Access database application, the better you can accomplish your task, which is to provide your users with the most efficient ability to choose, manage, and send their data. I sincerely hope that these tips will help you achieve this goal.