Optimize Microsoft Access to increase the speed

Hardware

The root of aiming speed

To increase the speed of your database program, you will naturally think of the basic parts that affect the speed. We will start from processing these parts. When you try to improve the performance of your database program, the hardware platform that your program runs will be the only deciding factor. Obviously, the first principle for optimization should be faster hardware = faster databases. Of course, CPU and RAM are the core of this equation. I believe you must note that Microsoft Access requires at least 8 MB RAM and a 80486 CPU to achieve satisfactory performance. Generally, users have noticed this point as appropriate, but I have seen many users frustrated by the speed at which Microsoft Access is executed, when I asked them which machine they used to run the program, they told me that it was a 80386 server and a MSACCESS on the server. EXE. Therefore, while we believe that your users can improve their program performance through the following methods, we still need to emphasize that hardware is the key issue during Microsoft Access execution.

Valid disk access

Assuming that a satisfactory hardware configuration is available, let's discuss how to improve the running speed of Microsoft Access on this platform. In this case, the second factor affecting the performance of your database program is disk access. During the execution process, access to the physical disk is always a speed bottleneck compared with access to data stored in RAM). Therefore, you should minimize access to the disk. However, because your applications always have to deal with some disks or other physical devices, your goal should be to ensure that all disk access is as effective as possible. To achieve this, you can sort out disk data fragments, the disk drive where your database is located, and all the disk drives involved when you execute your database program, in addition, you should remind your users to regularly clear disk fragments to ensure that disk access is always effective. This will minimize the time it takes to read and write the physical disk, although these reads and writes are inevitable, while optimizing the performance of the entire system.

Maximize RAM/minimize disk access frequency

The next topic is to minimize the frequency of dealing with physical disk drives in your database program. The key to this is to release as much RAM space as possible for your applications. To achieve this, you need to do the following:

Increase the maximum buffer size

First, you need to set the maximum buffer size. The maximum buffer size refers to the number of RAM retained by Microsoft Access as an internal bucket. The larger the storage space, the more likely the data you need can be found in RAM, while reducing the Access frequency to the physical disk. Microsoft Access requires a minimum buffer of KB. If your hardware system has more than 4 mb ram, then you can allocate more for Microsoft Access to improve system performance ). To do this, you only need. INI in your windows Directory) file to add the maximum buffer settings, use your text editor, such as Notepad, in this file find the [Option] segment, add the following sentence to it:

MaxBufferSize = xxxxx

In this example, xxxxx is a new setting for your application to allocate storage space (for example, setting MaxBufferSize = 2048 will allocate 2 MB of buffer space for Microsoft Access ). So, how much should you allocate? It's hard to say anything except "as much as possible. You need to manually adjust this setting, and try to make your hardware provide the following conditions: 1) It cannot prevent users from running programs at the same time; 2) it will not affect the running efficiency of its underlying operating system. check the configuration of Microsoft Access in the INI file. changes to the INI file will only work when Microsoft Access is started the next time, but will not affect the currently running Microsoft Access Program ).

Avoid loading Wizards

To release more memory for Microsoft Access, another step you can take is: if you do not use Wizards, do not load it. You can open the MSACCESS. ini file, find the [libraries] section, and add a semicolon before the "wizards. mdb = ro" sentence to avoid automatically loading Wizards. In this way, the RAM of 315B can be released to Microsoft Access for your own use. The advantage of this is not only to improve the running time performance, at the same time, the average installation time of each application is reduced by about 10 seconds.

Remove wallpaper/background bitmap

Release the standard windows wallpaper background to free up any 25 K to K space in RAM. Removing complex bitmaps will free up more memory space.

Create permanent swap file

Creating a permanent swap file will increase your performance. For this purpose, you can turn to the help documentation on this, which will guide you to create this file on the machine running the database application.

So far, we have solved the problems related to the hardware platform and the maximum disk access efficiency, and added Effective RAM storage space for your applications, next let's turn to optimizing the application itself.

Microsoft Access features

Compression

Always compress your program code. When developing and using a Microsoft Access database, you need to add and delete data and code regularly. The problem is that Microsoft Access cannot effectively release the allocated but deleted object space, which means that even if you delete an object, this object still occupies space in your database. Compressing the database will force Microsoft Access to delete these objects and reclaim the space occupied by them, so that your database is as small as possible but more effective. I used to reduce the average time for word-to-sentence queries by 30%-50%. This only means to compress database programs that become overly inflated due to frequent use and lack of compression. Therefore, in the process of running the program, do not ignore the importance of the compression process. Just as you need to regularly clear hard disk data fragments, you and your users should also regularly compress the database to ensure it is always the most effective.

Code only

You may want to build an application model through macro operations. Once you begin to optimize the final performance of your application, you must write all the macros into the code again. This is mainly because the Microsoft Access Code is much faster than the macro. Unfortunately, three macro operations cannot be rewritten into code. These three macros are Autokeys, Autoexec, and Addmenu operations, these three macros have no corresponding equivalence classes in Microsoft Access, so you can only use them. However, you can overcome the limitations of Autoexec by defining Autoexec to call your Access Basic Autoexec function.

Optimize data type declaration

When you declare the data type in your code, you should try to be as accurate as possible. If the type of a variable is not specifically declared and the default value is variable type) is very flexible and free, this type will also be the most wasteful type of memory. In this way, if you know that the check Balance variable does not require more than four decimal places, you can define it as a definite type rather than a variable type. You can also perform the same operation on the definition of your procedural functions. For example, you can declare the Function PostCredit () as an integer instead of Function PostCredit ()). Note the fact that if you estimate that a variable will be processed as a null value, you need to define it as a variable type rather than a definite strict data type, otherwise, you will get an error message. Similarly, pay attention to the difference between integer division and floating point division.

Frequent use of From/Report Variables

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

Mytemprariable = Forms! [Customer Invoice]! [Net price]

For this command, Microsoft Access first searches for a table named [Customer Invoice] in the Forms object. Once it finds this table, Microsoft Access then looks for the control named [NetPrice, and perform the correct operation. From this example, we can see that Microsoft Access finally determines the control we specified after two queries. If you want to query any control in the [Customer Invoice] table again in the same program function or subfunction, you can delete unnecessary statements that may appear next time, instead of using the following statements:

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 every time you need to find any object in the [Customer Invoice] table, all Form objects in the database must be searched once. To view and control [NetPrice], you only need to use the following statement:

Mytempvariable = F! [NetPrice]

You can also perform the same operations on the Report object, for example, set R = Reports! [MyReport]). When you only access a Form or Report once in a function, you may not get much advantage in terms of speed and performance, however, once you start to access the same Form or Report more than once, you will see a significant decrease in time. Therefore, by making Microsoft Access avoid additional queries, you will greatly increase the speed of your program.

Use windows functions where they can be applied

Whenever relevant, you can always use a windows function call to execute the same operation instead of the Access Basic code. In this way, you will save development time, because windows function calls are encoded and optimized, and they can be executed by machines written in C ), the Access Basic code should be compiled into the P code format and must be explained one line at the time of execution. The most common example 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. However, if you can simply use the GetPrivateProfileString and WritePrivateProfileString functions, they are fast and ready for coding optimization. Why do you need to worry about it? Reference: "Enhanced Microsoft Access: Using the Win16 API ")

The faster your Microsoft Access database application runs, the better you can complete your tasks, that is, to provide your users with the most effective ability to select, manage, and send their data. I sincerely hope that these tips will help you achieve this goal.

(