Abstract: This article introduces some notes that you need to pay attention to when designing, writing, and implementing Microsoftaccess database applications to ensure maximum speed. As a database programmer, your main task is to allow your users to quickly select, manage, and access the information they need. Other jobs are almost unchanged, as long as you
Abstract: This article introduces some notes that you need to pay attention to when designing, writing, and implementing Microsoft access database applications to ensure maximum speed. As a database programmer, your main task is to allow your users to quickly select, manage, and access the information they need. Other jobs are almost unchanged, as long as you
Summary
This article describes how to design, compile, and implement Microsoft access database applications to ensure that
SpeedNotes.
Introduction
As a database programmer, your main task is to allow your users to quickly select, manage, and access the information they need. Other jobs are almost unchanged, as long as your database application executes
SpeedThe faster the program, the higher the program efficiency. The purpose of this article is to highlight various techniques and strategies.
OptimizationProgram
Improve
SpeedTo make your Microsoft Access database application more efficient. This can also help you complete your mission as a database developer.
Hardware
Aim
SpeedRoot problem
To
ImproveYour database program
SpeedNaturally, you will think of the impact
SpeedWe will start from processing the basic parts. When you try
ImproveWhen your database program is performing, the hardware platform that your program runs will be the only deciding factor. Obviously, implementation
OptimizationThe first principle 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. In general, the user notices that this is justified, but I have seen many users execute Microsoft Access
SpeedFrustrated, when I asked them which machine they were using to run the program, they told me it was a 80386 instance and a MSACCESS. EXE instance running on a server. Therefore, I believe that your users can use the following methods
ImproveWhile its program performance is at the same time, it must be emphasized 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
ImproveMicrosoft Access running on this platform
Speed. In this case, the second factor affecting the performance of your database program is disk access. During execution, access to a physical disk is always one
Speed(Compared with accessing data stored in RAM), you should minimize disk access. 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, at the same time
OptimizationThe 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 (which also increases the system performance ). To do this, you only need. add the settings for the maximum buffer in the INI file (in your windows Directory), and use your text editor, such as Notepad, to find the [Option] section in this file, 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 (You may want to pass it when the program starts. 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.
ImprovePerformance of running time, and 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 file swap (relative to a temporary 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 application. Next let's turn
OptimizationThe 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, do not ignore the importance of the compression process when running the program. 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.
OptimizationThe final performance of your program must re-write all macros into code. 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.
OptimizationData 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 (if not specifically declared, the default 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 this operation on the definition of your procedural functions (for example, 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 sub-function), you can delete unnecessary statements that may appear next time, use the following statement only:
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 operation on the Report object (for example, set R = Reports! [MyReport]). When you only access one Form or Report once in a function, you may
SpeedPerformance is not very good, but 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
ImproveYour program
Speed.
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 passed through
OptimizationIt is also because they are written in C Language (which can be executed by machines), and the Access Basic code should be compiled into P code form, which also needs to 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 both fast and encoded.
OptimizationWhy do you need to worry about the availability? (Refer to "Enhanced Microsoft Access: Using the Win16 API ")
Your Microsoft Access database application is running
SpeedThe faster 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.
