Skip Navigation LinksHome > SSW Standards > Developer Access > SSW How to Improve Access Performance

How to Improve Access Performance

Compact your database often to reclaim space

Compact the database

Compacting your database reclaims unused space and makes almost all operations faster. You will do this on a regular basis. When you compact the database, you reorganize records so that they are stored in adjacent spaces, making retrieval faster. Additionally, compacting a database updates its data statistics, which can be used by a query to run faster. Defragmenting first is just academic.... You may want to defragment your disk using a program such as the Disk Defragmenter that ships with Windows 95 before compacting your database. This leaves contiguous free disk space immediately after the database file. In theory, this make future additions to the database occur faster.

Increase RAM

Increase the amount of RAM on your computer. The minimum acceptable amount of RAM for Access 97 is 16 MB. 32 MB is better but 64 MB is SSW's recommendation. Windows NT requires more RAM than Windows 95 to achieve the same level of performance, so if you have the choice always get 64 MB of RAM.

Keep Enough Local Hard Disk Space Free

Access requires a fair amount of disk space to operate, especially with large databases. Operations such as running large action queries, adding lots of data, importing data, and compiling and saving module code can use a lot of additional space on a temporary basis. Additionally, transactions and compacting the database can use a lot of disk space. A good rule of thumb is to have roughly 5-10 times the size of your largest database available in free local storage. SSW recommends that you should always have at least 100 MB free.

Run on a Computer with a Pentium processor

Access requires a Pentium processor for adequate performance. A DX4/100 MHZ processor is the minimum but a Pentium200 MMX is recommended as the base Intel Processor.

Avoid Wallpaper

Avoid wallpaper, unless you are using a small, tiled bitmap.

Close Access Occasionally to Reclaim Leaked Memory

Almost all Windows applications "leak" memory. This is due to data structures, variables and other memory consumers that are not correctly released by applications. By closing Access, you allow Windows to reclaim this leaked memory.

Close Unneeded Applications

Free up memory by closing applications that you aren't using. If you are using the System Agent from the Microsoft Windows Plus Pack, consider turning it off to make more memory available for your Access application.

Disable Office Startup Programs

Office installs one or more startup programs in your Windows\Start\Menu\Programs\Startup folder. Typically, the two programs installed are the Office Fast Start and Office Find Fast indexing utilities. Consider removing these items from the Startup folder, as they use up memory. Additionally, the Find Fast indexer runs continuously, and can dramatically reduce Access application performance.

Don't Use Disk Compression Software

Do not use disk compression software on the drive where you have installed Microsoft Access, or where your database file(s) are located. Disk compression exacts a heavy toll on performance, and this toll can be acute in database applications.

Don't Use RAM Disks

Don't waste RAM by using a RAM disk. RAM disks are a vestige of 16-bit Windows and DOS systems, and have no place on modern computers.

Install Access Locally

Do not install Microsoft Access, or its related Microsoft Office components on a network drive. Most applications, including Access and Office perform better when run from local drives.

Install Windows Locally

The Windows operating system should be installed on a local fixed drive on your computer. Because of the number of components involved in running Windows, a network installation of Windows causes poor performance, especially for database applications.

Keep Databases Local

Whenever possible, keep your databases local instead of on a network drive. In almost all cases, local disk access is faster than network access.

Adjust Virtual Memory Settings

Although Windows 95's default virtual memory settings are adequate for most systems, you may want to consider lowering the settings if you don't have a lot of space on the disk being used for virtual memory, or you may wish to use another local drive that is faster than the one being used by default for virtual memory.

Open Databases Exclusively

If you are opening a database, and no other users need to use the database, open the database in exclusive mode. To do this, check the Exclusive checkbox in the Open Database dialog, as shown below.

Exclusive mode

 

Optimise your Disk

Keep your hard disk in optimal shape by deleting unneeded files and emptying your Recycle Bin. Use a disk defragmenter, such as the one found the Windows 95 Accessories, Systems tools menu.

Developers Only: Saving space with
single precision number fields

One of the most prevalent design problems we see with databases is the unnecessary use of the Double number field. In some cases changing all the double precision number fields in a table from double to single real can trim the space used by about 40%. This makes a lot of difference to performance in reading the file. You can store numbers from -3.402823E38 to -1.401298E-45. Basically if the number is going to have over 6 significant figures in it, then think very carefully about this change as those precise number will get trimmed. An export of the table to a text file will allow you recover if you do trim a number incorrectly. Numbers such as 25.773 and -43000.1 dont need double precision. Dont forget to compact your database after this event. Hint Courtesy of: Garry Robinson, URL: http://www.vb123.com/.