 |
 |
 | |
 | |
 | |
 |
|
|
| |
| |
| |
|
Last updated: 16 Jun 2001
Having the latest version of Approach on a suitably fast system with plenty of RAM (many suggest 64 meg), fast disk access, a large permanent swap file (ie. if you are using MS-Windows 3.1*. If you are using MS-Windows 95+ either make sure you have a lot of spare disk space (say 100meg or more) or that your virtual memory is set to 2.5 times the size of your RAM. Also on MS-Windows 95+: setting your file system to "server" instead of "desktop" can help disk access times), and fast disk caching will of course increase the performance of any large database. Defraging your hard disk regularly will help the performance of all of your applications, including Approach database. Also, make sure you regularly compress the databases.
You may also what to carefully consider your database design. A badly designed database will perform poorly in any application. For instance, the more joins you have, and the more joined databases shown on any one form, the slower your database will tend to be. One way to speed up a bit database is to split complex .APRs into 2 or 3 smaller less complex ones where each .apr opens fewer tables. Also make sure you don't have a lot of unused forms, reports, macros and scripts cluttering up your database.
Databases work slower with 'full record locking' than with 'optimistic record locking'.
Minimize your use of color and graphics as these drastically effect the time it takes to update the screen.
In order to maintain the best ratio of speed and stability you may want to consider deleting the .adx files (indexes) on a regular basis (say every week or two). There are some possible complications with this so carefully read "Deleting and recreating index files" first. Saving your database using the FILE/SAVE AS menu command using the copy data option achieves much the same thing, although it means that everything get a new file name.
Delete excessive windows .tmp files. If this is a regular problem you may want to include the following statement in your autoexec.bat file: "del c:\windows\temp\apr*.tmp"
If you are using Approach on a LAN or WAN, also see 'Running Approach on a network' in this FAQ.
Wherever possible, use "numeric" data type as compared to "text". Suppose you have a part number that is literally all numbers, containing no alphabet characters, and the number string is 10 digits long. This field could be set up as a "text" field of 10 characters in length, or a "numeric" field of 12.3 (12 digits to the left of the decimal point and 3 to the right of the decimal point) or 10.0 (10 digits to the left of the decimal point and none to the right of the decimal point) characters in length. All of these options will index and sort fine, but by setting up the field as a "numeric" field of 10.0 characters, the index .adx file will be smaller and Sorts and Finds will be faster.
Whenever possible, join database files on numeric fields. If numeric field joins are not possible, join database files on text fields with short lengths. Why? Two reasons. The total number of joins in an application is limited by the total of the lengths of all of the joined fields. The maximum limit for the total of the lengths of all of the joined fields is 450 characters. [Where did this number 450 come from? Answer: personal communication from an Approach developer.] Joining fields with shorter lengths will allow more joined database files to be included in the application. The second reason is that fields that are joined are always indexed. The .adx index files get large and inefficient when the joined, indexed, text field is long (over 10-20 characters). This will slow down searches and screen updates. [I am not sure that these details are correct.]
Joined fields in different database files must match in terms of data type (such as, numeric or text) and must match in terms of length (maximum number of characters) for optimal and reliable performance. If the length of the joined fields is different, then the joins themselves can become unreliable and can become cross-linked so that the Unjoin function on the join map does not work.
Big gains in the speed of Finds can obtained by using SQL queries. (see article 'Using SQL in Approach'). Conversely, doing finds, joins, or sorts on calculated fields may substancially reduce performance.
I have no idea why, but some people have reported significant gainsin speed by setting the default sort on the database files so the last record added appears first. In other words reverse the order completely.
Conclusions: Design Approach applications for speed by: 1) joining as few databases as possible in an application, 2) using 'optimistic record locking', 3) compressing .dbf files regularly, 4) deleting apr*.tmp files, 5) joining on numeric fields, 6) index on fields with short fieldnames, 7) joining on fields with the same data type and length, 8) consider using SQL queries on big Finds, and avoid doing finds, joins or sorts on calculated fields.
Stuart Tucker <Stutucker@aol.com> substantially contributed to the articles 'Optimizing the speed of a database' and 'Deleting and recreating index files (.adx)'.