You can edit Webhelp.htm in an HTML editor, such as Word with the Web Page Authoring component installed. You can replace or add to the default hyperlinks in Webhelp.htm with your own hyperlinks. For example, you might have an internal support page on your intranet that you would like users to visit instead of the support pages on the Microsoft Web site.

To edit the Help on the Web HTML document in Word

1. On the File menu, click Open.
2. In the List files of type box, click HTML Document.
3. In the Select a Document box, click Webhelp.htm in the folder Microsoft Office 98:Office:Help.
4. Edit Webhelp.htm as needed, and then close the document.

   The next time you click the Help on the Web command on the Help menu, the revised Webhelp.htm document opens in your browser.

There are a number of ways that you can customize Office applications to improve the overall performance of Office on client computers. By modifying specific options based on your particular use of Office, you can increase speed or reduce memory or disk usage. The remainder of this chapter discusses how to optimize each of the Office applications.

Optimizing Microsoft Access

Using the Performance Analyzer

When you want to optimize the performance of a Microsoft Access database, start with the Performance Analyzer. You can use the Performance Analyzer to analyze a database or selected objects in a database. The Performance Analyzer can make some changes for you automatically.

To run the Performance Analyzer

1. Open the Access database you want to optimize.
2. On the Tools menu, point to Analyze, and then click Performance.
3. In the Object Type box, click the type of database object you want to optimize.

   To view a list of all database objects at once, click All in the Object Type box.

4. In the Object Name box, select the database objects you want to optimize.
5. Repeat Steps 3 and 4 until you have selected all the objects you want to optimize, and then click OK.

In the dialog box that appears, the Analysis Results box displays three types of optimizations: Recommendations, Suggestions, and Ideas. When you click an item in the Analysis Results box, the Analysis Notes box displays information about the proposed optimization. Suggestion optimizations have potential tradeoffs to consider. To view a description of the tradeoffs, click a Suggestion optimization in the Analysis Results box, and then read the information in the Analysis Notes box.

Access can perform Recommendation and Suggestion optimizations for you. In the Analysis Results box, click each of the Recommendation or Suggestion optimizations you want performed, and then click Optimize.

You must perform Idea optimizations yourself. To perform an Idea optimization, click that optimization, and then follow the instructions in the Analysis Notes box.

Optimizing a System to Run Microsoft Access

In addition to running the Performance Analyzer, the following suggestions can help you optimize the performance of Access and the computer it is running on.

Increase RAM

Because database operations are faster when Access can perform them in random­access memory (RAM), the performance of Access depends largely on the amount of RAM available. To run as a standalone application, Access requires a minimum of 12 MB of RAM for Windows, or 16 MB of RAM for Windows NT Workstation.

The amount of memory Access needs to run at top speed depends on a variety of factors, such as how many other applications are running at the same time and what types of operations an Access database application performs. When other applications are competing for memory, you can usually improve performance significantly by running with more than the required amount of RAM.

Similarly, if an Access database application uses Automation (formerly OLE Automation) to work with another application's objects, you can improve the performance of an Access database application by running with more RAM. When running with 32 MB or more of RAM, you may be able to increase performance by adjusting the MaxBufferSize setting in the Windows registry. Make sure that you are not using any of your RAM for a RAM drive.

For more information about the MaxBufferSize setting, see "MaxBufferSize" later in this chapter.

Optimize Virtual Memory Use

With virtual memory, an application sees a large, continuous block of primary memory (RAM) that, in reality, is a much smaller block of primary memory supplemented by secondary memory (such as a hard disk). To temporarily free up space in RAM, blocks of data (called pages) are moved between RAM and a swap file located on the hard disk.

By default, the Windows 95 swap file is dynamic, so it can shrink or grow based on available disk space and the operations performed on the system. Also, the swap file can occupy a fragmented region of the hard disk with no substantial performance penalty. A dynamic swap file is usually the most efficient use of resources. The simplest way to ensure high virtual memory performance is to make sure that the disk containing the swap file has ample free space so that the swap file size can shrink and grow as needed.

In Windows 3.x, enhancing performance by changing virtual memory settings is quite common. Because the Windows 95 swap file is dynamic, the need to change virtual memory settings is less common. However, in some situations adjusting virtual memory settings can improve performance. If you've already tried deleting unnecessary files, and you still have a performance problem, try changing the Windows 95 default virtual memory settings.

If you have more than one drive available, you may get better performance if you specify that Windows locate the swap file on a drive other than the default in the following cases:

• If the default drive does not have much free disk space, and another local drive has space available.
• If another local drive is available that is faster than the current drive (unless that disk is already heavily used).

You also may get better performance if you specify the minimum disk space available for virtual memory to be at least 25 MB minus available RAM. For example, if a computer has 12 MB of RAM, you should specify at least 13 MB of virtual memory. You may want to specify more if you run several large applications at the same time.

For more information about changing Windows 95 virtual memory settings, see Windows 95 online Help.

Compact Databases and Defragment the Hard Disk

Each time users add or update data, a database grows. But when a user deletes data, the database does not automatically get smaller. Access makes empty data pages available for reuse after a database is closed, so the next time it is opened, new data pages are available to be filled with new records. If left unused, empty data pages remain in the database until it is compacted.

Compacting the database frees unused disk space, makes all data pages contiguous, and, if a primary key is defined for a table, saves its records in primary key order. For databases in which records are frequently added, deleted, and updated, you should compact frequently.

Note   If the database is a replica, you must compact it twice to reclaim all available space. Additionally, if the database is the Design Master in a replicated set, you must take certain precautions when compacting it to prevent it from losing its Design Master status. For more information about replication, see Chapter 29, "Workgroup Features in Microsoft Access."

In addition, you should occasionally use a utility such as the Windows Disk Defragmenter to defragment the hard disk after compacting databases. This increases efficiency by making database files contiguous on the hard disk.

Do Not Use Wallpaper on a Low­Memory Computer

If the Windows desktop has a wallpaper (full­screen background) bitmap and the computer does not have memory to spare, replace the wallpaper with a solid color or pattern bitmap, or no bitmap at all.

Using the System Policy Editor to Change Settings in the Options Dialog Box You can customize the user environment and achieve some performance optimizations in Access by setting options in the Options dialog box (Tools menu) on individual user's computers. Using the System Policy Editor, however, a network administrator can set these options remotely — for all users, for a particular network group, or for a single user. You can also set options for all client computers or for a single computer. Instead of making these changes in the Options dialog box on individual computers, run the System Policy Editor using the Access97.adm template. For more information about using the System Policy Editor, see "Using Windows System Policies to Customize Office" earlier in this chapter.

Adjusting Microsoft Jet Registry Settings to Improve Performance

In addition to optimizing system memory use, you may want to adjust Microsoft Jet settings in the Windows registry. These settings control how the Microsoft Jet database engine uses memory and performs other aspects of its operations. Microsoft Jet automatically provides default settings that usually provide an excellent performance for most common database operations. However, depending on what kind of operations a database application performs and how much memory is available at any given time, you may be able to improve performance by adjusting Windows registry settings.

Note   Keep in mind that the optimum value for a setting in the registry can change from computer to computer, and can change depending on how much memory is available, the kind of operation the user is performing, and the level of activity in the database at any given time. Experiment with these settings to find out what works best.

If you decide to change any of the Microsoft Jet registry settings in the Windows registry, you have three options:

• Change the default values for Microsoft Jet settings in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5 key.

  These defaults are written when Access 97 is installed. Changes made to settings in this key may affect other applications that use the Microsoft Jet version 3.5 database engine, such as Visual Basic version 5.0 or Excel 97. Make changes to values in this key only if you want to affect all the applications that use Microsoft Jet 3.5 on a user's computer, or if only Access 97 is used to perform operations on Microsoft Jet 3.5 databases on a user's computer.

• Create a Jet 3.5 subkey below the KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Jet\3.5\Engines key, and then add values for any default setting you want to override.

  These changes affect only Access 97.

• Create a custom set of registry keys, called a user profile, that contains settings to override default registry settings.

  These settings affect only a particular database application or session of Access. You use the /profile command­line option when starting Access to specify the user profile you want to use.

For more information about user profiles or changing Access registry settings, see Access online Help.

Using the SetOption Method to Change Settings in the Windows Registry You can also change most of the Microsoft Jet registry settings by using the SetOption method of the DBEngine object in Data Access Objects (DAO) code. Using the SetOption method, you can change these settings while a database application is running. For example, you may want to change the SharedAsyncDelay and PageTimeout settings to low values to increase performance when an application performs operations on one record at a time or is using controls bound to data; but you may want to increase these values when performing bulk operations, such as update and delete queries or transactions on large numbers of records. Changing Microsoft Jet settings by using the SetOption method does not affect the values stored in the Windows registry. Changes made with the SetOption method are in effect only for the current instance of the DBEngine object. For more information about the SetOption method, see Access online Help.

The following sections discuss the Windows registry settings you can adjust to improve performance of Access.

Threads

A thread is a software process that runs independently on a multitasking operating system such as Windows 95 or Windows NT Workstation. For example, using multiple threads allows you to run a communications software program to download a file at the same time you use your word processor to edit a document.

Microsoft Jet uses threads internally to enhance performance and provide background services such as read­ahead caching, write­behind caching (transaction commit), cache maintenance, and the detection of changes made to shared databases. By default, Microsoft Jet uses up to a maximum of three threads. You may want to try increasing the Threads setting if a large number of actions are performed in a database application, or if it contains a large number of linked tables.

MaxBufferSize

Microsoft Jet reads and writes data in 2 KB pages, placing the data in a temporary holding area called the buffer as required by its operations. By default, when performing operations that add, delete, or update records and that are not part of an explicit transaction, Microsoft Jet automatically performs internal transactions that group changes to records and temporarily saves them in the buffer.

After a specified time, or when the size specified by the MaxBufferSize setting is exceeded, Microsoft Jet writes the data to the database. This minimizes the time spent reading and writing data to the database. Additionally, Microsoft Jet can minimize the time spent reading data for tables, queries, forms, or reports by reading available data from its buffer.

The MaxBufferSize setting specifies a high­water mark for the size of the buffer that Microsoft Jet uses to work with records in memory, measured in kilobytes. Microsoft Jet can temporarily exceed the MaxBufferSize. As soon as it does, however, it starts a background thread to write data to the database to bring the buffer's size down to the specified high­water mark.

By default, Microsoft Jet allocates memory for its internal buffer on an as­needed basis up to the MaxBufferSize. The default value written in the Windows registry for the MaxBufferSize when Access is installed is 0, which indicates that Microsoft Jet calculates the setting based on the following formula:

((Total RAM in KB – 12,288)/4 + 512 KB)

For example, on a system with 16 MB of RAM (16,384 KB), Microsoft Jet uses a setting of 1,536 in MaxBufferSize.

You can override the default calculated setting by specifying a different value in MaxBufferSize. This sets a new high­water mark. The minimum value that Microsoft Jet uses by default is 512 KB, but you can specify a value as low as 128 KB. However, setting the value in MaxBufferSize to less than 512 KB is not recommended for Access applications because it can seriously degrade performance.

For computers with 16 MB or less of installed RAM, there is generally no need to override the calculated setting. For computers with 32 MB of RAM, however, you may see some performance improvement when specifying a larger MaxBufferSize, as long as no other applications are running at the same time as Access. For example, tests have shown performance improvements in applications that perform large transactions when a MaxBufferSize as large as 8 MB is specified.

Specifying a MaxBufferSize larger than 8 MB has not been found to increase performance. Setting a value too high can degrade performance due to the added CPU overhead needed to manage the cache, and due to the fact that the operating system may start swapping the Microsoft Jet cache to disk as virtual memory.

Important   When you specify a MaxBufferSize setting larger than the default and you are not using the FlushTransactionTimeout setting, increase the SharedAsyncDelay setting to increase the time that data is held in the buffer. If you do not increase the value of SharedAsyncDelay, Microsoft Jet writes the contents of its buffer to the database before it has had time to utilize the additional memory you specified. For more information, see "SharedAsyncDelay" later in this section.

UserCommitSync

The UserCommitSync setting determines whether changes made as part of an explicit transaction (a change made to data using the BeginTrans, CommitTrans, and Rollback methods) are written to the database in synchronous mode or asynchronous mode. In synchronous mode, Microsoft Jet does not return control to the application code until the changes made by the CommitTrans method are written to the database. In asynchronous mode, Microsoft Jet stores the changes in its memory buffer, returns control to the application code immediately, and then writes the changes to the database in a background thread.

Microsoft Jet begins writing the changes either after a specified period of time (determined by the FlushTransactionTimeout setting, or by the SharedAsyncDelay or ExclusiveAsyncDelay settings described later in this section), or when the MaxBufferSize is exceeded. The default UserCommitSync setting is Yes, which specifies synchronous mode. It is not advisable to change this setting, because in asynchronous mode there is no guarantee that information has been written to disk before a database application's code proceeds to the next command.

Note   There is no longer a need to use explicit transactions to improve performance of Access. In Access 95 and Access 97, a database application should use explicit transactions only in situations where there may be a need to roll back changes. Microsoft Jet can now automatically perform internal transactions to improve performance whenever it adds, deletes, or changes records.

For more information about transactions, see Chapter 9, "Working with Records and Fields," in Building Applications with Microsoft Access 97.

ImplicitCommitSync

By default, when performing operations that add, delete, or update records outside of explicit transactions, Microsoft Jet automatically performs internal transactions that temporarily save data in its memory buffer, and then later write the data to the hard disk. The ImplicitCommitSync setting determines whether changes made using these automatic internal transactions are written to the database in synchronous mode or asynchronous mode.

The default ImplicitCommitSync setting is No, which specifies that changes are written to the database in asynchronous mode; this helps provide an excellent performance. If you want internal transactions to be written to the database in synchronous mode, change the ImplicitCommitSync setting to Yes. This generates behavior similar to Microsoft Jet versions 2.x and earlier, when not using explicit transactions in Access Basic code; however, it can also impair performance considerably.

FlushTransactionTimeout

The FlushTransactionTimeout setting determines the method Microsoft Jet uses to perform asynchronous writes to a database file. This setting is the number of milliseconds after which Microsoft Jet starts writing database changes to disk from its cache. Changes are written to disk after the specified amount of time has expired and if no new pages have been added to the cache during that interval.

The only exception is if the size of the cache exceeds the MaxBufferSize setting, at which point the cache starts asynchronous writes regardless of whether the time has expired. The default setting is 500 milliseconds. The only reason to increase the value of the FlushTransactionTimeout setting is if the database is being updated over a slow wide area network (WAN) or local area network (LAN) connection. Increasing this value for databases being updated over fast WAN and typical LAN connections does not improve performance.

The FlushTransactionTimeout setting overrides both the ExclusiveAsyncDelay and
SharedAsyncDelay settings, and is the preferred method of determining how Microsoft Jet performs asynchronous writes to a database file. To enable the ExclusiveAsyncDelay and SharedAsyncDelay settings, you must set the FlushTransactionTimeout entry to a value of 0.

ExclusiveAsyncDelay

The ExclusiveAsyncDelay setting is the maximum time that can pass before asynchronous mode changes start to be written to a database that is opened exclusively. The default setting is 2,000 milliseconds. Decrease this setting if you want to be sure that changes are written to a database more frequently, but note that this decreases overall performance.

Because the default setting is already quite long, increasing this setting does not improve performance in most cases. However, if a system has 32 MB of RAM or more and you have specified a large MaxBufferSize, you may see some performance improvement if you increase this setting. By default, this setting is disabled by the FlushTransactionTimeout setting.

SharedAsyncDelay

The SharedAsyncDelay setting is the maximum time that can pass before asynchronous mode changes start to be written to a database that is opened in shared mode. The default setting is 50 milliseconds. Note that this produces a small delay before changes are made available to other users. Increasing this value enhances performance in shared databases because there are fewer disk writes. However, it may reduce the overall concurrency because pages are locked while they are in the buffer waiting to be written to the database.

If a database application performs operations that affect many records, increase this setting to give Microsoft Jet additional time to temporarily save additions, deletions, and updates in its buffer before it writes them to the database. This applies whether a database application uses action queries, DAO code, or SQL statements to add, delete, or update records. By default, this setting is disabled by the FlushTransactionTimeout setting.

PageTimeout

The PageTimeout setting determines how long Microsoft Jet waits before checking to see whether other users have made changes to the database. If changes have been made, Microsoft Jet refreshes the data in its memory buffer.

Note   This action is equivalent to pressing F9 while viewing a table or query in Access, or using the Refresh method in DAO code.

The default PageTimeout setting is 5,000 milliseconds. Decreasing this setting increases the amount of time spent reading from the disk, thus impairing performance; but it can also ensure the data available to the user is more current. The Refresh Interval setting on the Advanced tab in the Options dialog box (Tools menu) in Access overrides the setting in PageTimeout.

Tip   You can override the PageTimeout setting and refresh the cache by using the dbRefreshCacheargument of the Idle method in DAO code. This allows users to see other users' changes immediately. For more information about the Idle method, see Access online Help.

LockDelay

If Access tries to place a lock on a page in a shared database, and a message that locking has failed is returned, the LockDelay setting determines how long it waits before it retries. If the time it takes to return the message exceeds the LockDelay setting, there is no delay. The default setting is 100 milliseconds.

On systems that do not manage lock retries themselves, such as Windows 95 peer­to­peer networking, the LockDelay setting prevents Microsoft Jet from performing repeated retries over a short period of time. If you are using such a system, the default setting is usually sufficient to reduce the number of lock requests sent across the network, which frees up network bandwidth for other purposes.

If a database application or its users frequently lock a large number of records, you may want to try increasing this setting to further reduce the number of lock requests being sent across the network. If you are using a server­based networking system that manages lock retries itself, such as Windows NT Server or Novell NetWare, there is no need to change this setting.

MaxLocksPerFile

The MaxLocksPerFile setting determines the maximum number of locks that Microsoft Jet places against a file. The default setting is 9,500 locks. If the number of locks required to perform a transaction exceeds the MaxLocksPerFile setting, the transaction commits (writes) the data that has locks associated with it, frees the locks, and then continues processing the transaction.

If the maximum number of locks a server can handle is less than the setting in MaxLocksPerFile, the server returns an error message or appears to hang when performing a large transaction. If this occurs, decrease the value of MaxLocksPerFile. A NetWare server can be configured to perform a maximum of 10,000 locks per connection. A NetWare server connection can include more than one database, so it is possible to exceed the maximum number of available locks if you are using more than one database at a time.

RecycleLVs

Long value (LV) pages store data in fields with Memo, OLE Object, and Hyperlink data types, as well as the data that defines forms, reports, and modules. When a database is open in shared mode, the size of the database increases when data, forms, reports, and modules are deleted or changed in such a way that the current LV page must be discarded and replaced with a new LV page. Sometimes a new LV page is needed to prevent errors for other users who may still be using the object or data in its original form.

The RecycleLVs setting determines when discarded LV pages become available for reuse. The default setting is 0, which specifies that discarded LV pages continue to occupy space in the database and only become available for reuse after the last user closes the database. You can change the default setting to 1 so that discarded LV pages become available for reuse after Microsoft Jet determines that there is only one user in the database in shared mode and that new LV data has been added to the database.

Note that setting RecycleLVs to 1 slows performance slightly. Therefore, this is primarily useful during development of a database application, when forms, reports, and modules are being created, because it minimizes the need to compact the database.

When a database is open in exclusive mode, the RecycleLVs setting has no effect: discarded LV pages become available for reuse immediately. In both exclusive mode and shared mode, discarded LV pages are reused only when new LV data needs to be written to the database. To remove discarded LV pages before that time, you must compact the database.

Optimizing Table Performance

You can achieve excellent performance results by following these guidelines for table design.

Design Tables Without Redundant Data

A well­designed database is a prerequisite for fast data retrieval and updates. If existing tables in your database contain redundant data, you can split the data into related tables so that you can store data more efficiently. This process is called normalization.

You can use the Table Analyzer Wizard to normalize your tables manually, or you can have the wizard do this for you automatically. After identifying the data that can be moved to smaller tables, the Table Analyzer Wizard identifies a unique value within each new table to use as a primary key, or if no such value exists, creates an incrementing AutoNumber field to use as the primary key. It creates foreign key fields and uses them with the primary keys to create a relationship between the new tables. Finally, the wizard searches through your data, identifies any values that appear to be inconsistent, and prompts you to choose the correct value.

To use the Table Analyzer Wizard

1. On the Tools menu, point to Analyze, and then click Table.

   The first two panels of the wizard contain introductory information about normalization.

2. On the third panel of the wizard, double­click the table that you want to normalize.
3. Follow the instructions in the remaining panels of the wizard.

   In the last panel, you can create a query to view all the information from the split tables in a single datasheet.

For more information about determining which tables, fields, and relationships belong in your database, see Access online Help.

Choose Appropriate Data Types for Fields

You can save space in a database and improve join operations by choosing appropriate data types for fields. When defining a field, choose the smallest data type that is appropriate for the data in the field. Also, give fields you use in joins the same or compatible data types.

For more information about data types or the size of data types for fields, see Access online Help.

Create Indexes for Fields Used in Joins and When Setting Criteria

An index helps Access find and sort records faster. You can make dramatic improvements in the speed of queries by indexing fields on both sides of joins, or by creating a relationship between those fields and then indexing any field used to set criteria for the query. Finding records through the Find dialog box (Edit menu) is also much faster when searching an indexed field. Intelligent indexing of large tables can significantly improve performance when your query uses Rushmore™ query optimization.

For more information about Rushmore query optimization, see Access online Help.

Note   Microsoft Jet can use a descending index to optimize a query, but the criteria on the indexed field must use an equal sign (=) as the comparison operator. If the index is descending and the comparison operator is something other than an equal sign, the index will not be used. Ascending indexes (the default) can always be used to optimize a query.

Indexes take up disk space and slow down the adding, deleting, and updating of records. In most situations, however, the faster data retrieval outweighs these disadvantages. If a database application updates data frequently or if you have disk space constraints, you may want to limit indexes; otherwise, use them generously. Make sure to create a primary key or unique index if the data in a field or combination of fields uniquely identifies the records in a table.

Use Multiple­Field Indexes When Joining Multiple Fields

If you use multiple­field indexes, Access can optimize queries that join multiple fields from one table to multiple fields in another table (such as LastName, FirstName in one table to LastName, FirstName in another table) or that search for values in multiple fields.

If you use criteria on a field in a multiple­field index, the criteria must apply to the first field or fields in the index in order for Microsoft Jet to use the index to optimize the query. For example, if you have a multiple­field index on the two fields LastName and FirstName used in a join to the LastName and FirstName fields in another table, Microsoft Jet can use the multiple­field index to optimize the query when you have criteria on LastName only, or on LastName and FirstName, but not if the criteria is on FirstName only. If the criteria is on FirstName only, you must add a single­field index on FirstName to optimize the query.

The simplest approach is to add multiple­field indexes to fields used in multiple­field joins and also add a single­field index to any field on which you use criteria to restrict the values in the field. You can add both a multiple­field index and a single­field index to the same field.

Optimizing the Performance of Linked Tables

Although you can use linked tables as though they were regular Access tables, keep in mind that they are not actually in your Access database. Each time you view data in a linked table, Access has to retrieve records from another file. This can take time, especially if the linked table is on a network or in an SQL database.

If you are using a linked table on a network or in an SQL database, follow these guidelines for the best results:

• Force the linked database to remain open.

  This greatly enhances performance when opening the main database and opening tables and forms. Create an empty table in the linked database and create a link to the table in the main database. Then use the OpenRecordset method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and creating and deleting the associated LDB file.

• View only the data you need.

  Do not page up and down unnecessarily in the datasheet. Avoid going to the last record in a large table. If you want to add new records to a large table, use the Data Entry command on
  the Records menu to avoid loading existing records into memory. If you often add records to a linked table, create a form for adding records that has the DataEntry property set to Yes. When you open the form to enter new data, Access does not display any existing records and does not have to retrieve all the records in the linked table.

• Use filters or queries to limit the number of records that you view in a form or datasheet.

  This allows Access to transfer less data over the network.

• In queries that involve linked tables, avoid using functions in query criteria.

  In particular, avoid using domain aggregate functions, such as DSum, anywhere in your queries. When you use a domain aggregate function, Access retrieves all of the data in the linked table to execute the query.

• When an Access database is shared on a network, avoid locking records longer than necessary.

Optimizing SQL Database Performance

When you connect to an external SQL database table, you can maintain excellent performance by using linked tables whenever possible. Linked tables are considerably faster, more powerful, and more efficient than directly opened tables. You can only open tables directly using Visual Basic code, so this is not pertinent if you are accessing SQL Server™ data using the Access user interface.

Following are additional guidelines for improving performance:

• Retrieve and view only the data you need.

  Use restricted queries to limit the number of records that you retrieve, and select only the columns you need, so Access can transfer less data over the network. If you are not updating the data, do not use updatable result sets. You can prevent data from being updated when using a form by setting the form's RecordSet property to Snapshot.

• If you need to retrieve a large number of records, use a dynaset.

  Dynasets are faster and more efficient than snapshots. For example, when you move to the end of a snapshot, the entire result set must be downloaded to the local computer. With a dynaset, however, only the last screen is downloaded. Also do not page up and down unnecessarily in the data, and avoid going to the last record in a large table — the Data Entry command on the Records menu is the fastest way to add new records to a table.

• Use cache memory.

  If the data most recently retrieved from the server is requested again while the application is running, it is faster to retrieve many rows from a cache than to fetch many individual rows. Access forms and datasheets automatically use a cache. If you are retrieving data using a Recordset object created in Visual Basic for Applications, you can use the CacheStart and CacheSize properties to specify the range you want within the Recordset object. Use the FillCache method to quickly fill all or part of this range with data from the SQL database server.

• Avoid using queries that cause processing to be done locally.

  When accessing external data, the Jet database engine processes data locally only when the operation cannot be performed by the external database. Query operations (as defined by the SQL commands used to implement them) performed locally include:

  • JOIN operations between tables from different remote data sources. (Note that if the join involves a local table or query with few records and a remote table with many more records, and the remote table's join field is indexed, Access returns only the records that match the local table or query, thus greatly improving query performance.)
  • JOIN operations based on a query with the DISTINCT predicate or a GROUP BY clause.
  • Outer joins containing syntax not supported by the Open Database Connectivity (ODBC) driver or server.
  • DISTINCT predicates containing operations that cannot be processed remotely.
  • The LIKE operator used with Text or Memo fields (this may not be supported by some servers).
  • Multiple­level GROUP BY arguments and totals, such as those used in reports with multiple grouping levels.
  • GROUP BY arguments based on a query with a DISTINCT predicate or a GROUP BY clause.
  • Cross­tab queries that have more than one aggregate; that have field, row, or column headings that contain aggregates; or that have a user­defined ORDER BY clause.
  • TOPn or TOPn PERCENT predicates.
  • User­defined functions, or operators or functions that are not supported by the ODBC driver or server.
  • Complex combinations of INNER JOIN, LEFT JOIN, or RIGHT JOIN operations in nested queries.
• For update and delete queries that affect data stored on a server, set the FailOnError property to Yes.

  This setting tells Access to send the query to the server, where all the appropriate records are processed at once instead of one record at a time.

Optimizing Multiuser Performance

This section describes suggestions that can help you optimize the performance of databases that are used in a multiuser environment.

Database performance is faster when only data is sent across the network. Put only the tables on a network server, and keep other database objects on users' computers. You can separate the tables from the other database objects by using the Database Splitter. To split a database, make a backup copy of the database, and then use the Database Splitter command (Tools menu, Add­ins submenu).

Choose the appropriate record locking method. To set the record locking method, use the options on the Advanced tab in the Options dialog box (Tools menu). The options under Default Record Locking include the following:

• No Locks

  Access does not lock the records being edited. When a user tries to save changes to a record that another user has also changed, Access gives the option of overwriting the other user's changes, copying the user's version of the record to the Clipboard, or discarding the changes. This strategy ensures that records can always be edited, but it can create editing conflicts between users.

• Edited Records

  Access locks the record being edited, so no other user can change it. It might also lock other records that are stored nearby on the hard disk. If another user tries to edit a record that is locked, Access displays an indicator in the other user's datasheet. This strategy ensures that users can always finish making changes that they have started. It is a good choice if you do not often have editing conflicts.

• All Records

  Access locks all records in the form or datasheet (and underlying tables) for the entire time it is being edited, so no one else can edit or lock the records. This strategy is very restrictive, so choose it only when you know only one person needs to edit records at any one time.

Note   When you edit data in a linked SQL database table using ODBC, Access does not lock records; instead, the rules of that SQL database govern locking. In this instance, regardless of the record­locking setting you choose for your database, Access always acts as though the No Locks setting has been selected.

You can avoid locking conflicts by adjusting the following settings on the Advanced tab in the Options dialog box (Tools menu):

• Update Retry Interval

  This sets the number of milliseconds after which Access automatically tries to save a changed record that is locked by another user. Valid values are 0 through 1,000 milliseconds. The default setting is 250.

• Number of Update Retries

  This sets the number of times Access tries to save a changed record that is locked by another user. Valid values are 0 through 10. The default setting is 2.

• ODBC Refresh Interval

  This sets the interval after which Access automatically refreshes records that are opened using ODBC. Valid values are 1 through 32,766 seconds. The default setting is 1500.

• Refresh Interval

  This sets the number of seconds after which Access automatically updates records in Datasheet or Form view. Valid values are 1 through 32,766 seconds. The default setting is 60.

Note   When Access refreshes the current datasheet or form, it does not reorder records, add new records, or remove deleted records. To view these changes, you must requery the underlying records for the datasheet or form.

If a multiuser database uses linked tables, see "Optimizing the Performance of Linked Tables" earlier in this chapter for additional suggestions. If a multiuser database connects to tables in an external SQL database, see "Optimizing SQL Database Performance" earlier in this chapter for additional suggestions.

Optimizing Query Performance

There are several things you can do to make queries run faster. In addition to using the Performance Analyzer to analyze specific queries, consider the following ideas.

• Index fields on both sides of joins, or create a relationship between those fields and index any field used to set criteria for the query.

  Note   The Microsoft Jet database engine automatically optimizes queries that join an Access table on your hard disk and an ODBC server table (typically an SQL server table) if the Access table is small and the joined fields are indexed. In these cases, Access improves performance by requesting only the necessary records from the server. Make sure that tables you join from different sources are indexed on the join fields.

• When defining a field in a table, choose the smallest data type or FieldSize that is appropriate for the data in the field. Also, give fields you use in joins the same or compatible data types.
• When creating a query, do not add fields that you do not need, and clear the Show check box for selection criteria fields whose data you do not want Access to display.
• Avoid calculated fields in nested queries. If you add a query that contains a calculated field to another query, the expression in the calculated field slows performance in the top­level query.

  For example, suppose this is Query1:

  SELECT Format$(Field1)As TempField FROM Table1
  
  
  

  Query1 is nested in the following query, Query2:

  SELECT * FROM Query1&nbspWhere TempField = 100
  
  
  

  Nesting Query1 in Query2 causes optimization problems due to the expression Format$(Field1) in Query1. For most effective performance, use calculated fields only in the top-level query of nested queries. If that is not practical, use a calculated control on the form or report based on the query to show the result of the expression, instead of nesting the query.

• When grouping records by the values in a joined field, specify Group By for the field that is in the same table as the field you are totaling (calculating an aggregate on).

  For example, if your query totals the Quantity field in an Order Details table and groups by OrderID, specify Group By for the OrderID field in the Order Details table, not the OrderID field in the Orders table. For greater speed, use Group By on as few fields as possible. As an alternative, use the First function where appropriate.

  If a totals query includes a join, consider grouping the records in one query and then adding that query to a separate query that performs the join. This improves performance in some queries.

• Avoid restrictive query criteria on calculated and nonindexed columns whenever possible. For more information, see "Optimizing Criteria Expressions in Queries Using Rushmore Technology" later in this chapter.
• If you use criteria to restrict the values in a field used in a join, test whether the query runs faster with the criteria placed on the one side or the many side of the join. In some queries, you get faster performance by adding the criteria to the former.
• Use field sorting judiciously, especially with nonindexed fields.
• If your data does not change often, use make­table queries to create tables from your query results. Use the resulting tables rather than queries as the basis for your forms, reports, or other queries.
• Avoid using domain aggregate functions, such as the DLookup function, in a query that reads table data. Instead, add the table to the query or create a subquery.
• If you are creating a cross­tab query, use fixed column headings whenever possible. For more information about sorting or limiting column headings in cross­tab queries, see Access online Help.
• Use the Between...And, In, and equal (=) operators on indexed columns to optimize queries. For more information, see the following section, "Optimizing Criteria Expressions in Queries Using Rushmore Technology."

If a query includes tables in an external SQL database, see "Optimizing SQL Database Performance" earlier in this chapter for additional suggestions.

Optimizing Criteria Expressions in Queries Using Rushmore Technology

Rushmore is a data­access technology that permits sets of records to be queried efficiently. With Rushmore, when you use certain types of expressions in query criteria, your query runs much faster.

Simple Expressions

Access can optimize simple expressions in the Criteria row of the query design grid or in a WHERE clause in an SQL SELECT statement. A simple optimizable expression can form an entire expression or can appear as part of an expression.

A simple optimizable expression takes one of the following forms:

Indexed field Comparison operator Expression
Expression Comparison operator Indexed field

In these expressions, Indexed field is a field on which an index is constructed.

Comparison operator is one of the following operators: <, >, =, <=, >=, <>, Between, Like, or In. Expression can be any valid expression, including constants, functions, and fields from other tables.

Note   For best results, the comparison value in an expression using the Like operator must begin with a character, not a wildcard character. For example, you can optimize Like "m*" but not Like "*m*".

Examples of Simple Optimizable Expressions

If you have created indexes for the LastName, Age, and HireDate fields in an Employees table, the following are simple optimizable expressions:

[LastName]="Smith"
[Age]>=21
#12/30/90#<[HireDate]
Employees.[LastName]=Customers.[LastName]
[LastName] IN ("Smith", "Johnson", "Jones")
[Age] BETWEEN 18 AND 65

If you have a multiple­field index on the LastName, FirstName fields, the following expression is optimizable:

(([LastName])="Smith") AND (([FirstName])="Pat")

The SQL SELECT statement for the following COUNT(*) query is Rushmore optimizable if there are indexes on the LastName and FirstName fields. This query counts the number of employees who are not named Pat Smith:

SELECT Count(*)FROM Employees
WHERE (Employees.[LastName] <> "Smith")
AND (Employees.[FirstName] <> "Pat")

Complex Expressions

Access can also optimize complex expressions made by combining simple expressions with the AND or OR operators.

A complex optimizable expression takes one of the following forms:

Simple Expression  AND  Simple Expression

– or –

Simple Expression  OR  Simple Expression

The following rules determine query optimization when combining simple expressions in query criteria.

You can use parentheses to group combinations of simple expressions. The preceding rules also apply to combinations of expressions grouped with parentheses.

After you have combined simple optimizable expressions into complex expressions, these complex expressions can, in turn, be combined to form even more complex expressions that are optimizable according to the preceding rules.

Examples of Complex Optimizable Expressions

The following table lists examples of combined simple expressions and the extent to which the result is optimizable.

Note   These examples assume that you have created indexes for the LastName and HireDate fields, but not for the MiddleInitial or FirstName fields.

Additional Notes on Rushmore Optimization

• The COUNT(*) function is highly optimized for queries that use Rushmore.
• If the index is descending and the comparison operator is other than equal (=), the query cannot be optimized.
• Rushmore queries work with Access tables, as well as with Microsoft FoxPro and dBASE tables (DBF files). You cannot use Rushmore with ODBC data sources, however, because Access sends these queries to the ODBC data source instead of processing them locally.
• You can optimize multiple­field indexes if you query the indexed fields in the order they appear in the Indexes window, beginning with the first indexed field and continuing with adjacent fields (up to and including 10 fields).

  For example, if you have a multiple­field index on LastName, FirstName, you can optimize a query on LastName or on LastName and FirstName, but you cannot optimize a query on FirstName.

Optimizing Filter Performance

If the lists in fields in the Filter By Form window take too long to display or they do not display values from the underlying table, you can set filtering options to change these behaviors. By setting filter options, you can prevent the lists from displaying the underlying table's field values, display field values on the list for certain types of indexed or nonindexed fields only, or change the record limit that determines whether the list displays a field's values.

You can perform this optimization for all tables, queries, and forms in the database, or for specific bound text box controls in forms.

Tip   If you use the same nonindexed field repeatedly to filter records, consider indexing it before changing the following settings. This improves filtering and other search operations on the field.

To optimize Filter By Form performance for all tables, queries, and forms, set the options under Filter By Form Defaults on the Edit/Find tab in the Options dialog box (Tools menu) according to the performance you want to achieve:

• If the list of values takes too long to display in nonindexed fields only, limit the lists to indexed fields. You can do this by clearing the check boxes for Local Non­indexed Fields and ODBC Fields.
• If the lists take too long to display in indexed fields because there are too many records in the indexes, also clear the Local Indexed Fields check box.
• If lists are not displaying the values from indexed or nonindexed fields, make sure that the appropriate check boxes are selected under Show list of values in; or increase the number in the box Don't display lists where more than this number of records read so that it is greater than or equal to the maximum number of records in any nonindexed field in the underlying table.

  If the field is indexed, Access reads only the unique values, not all the values in a field. If the field is not indexed, Access reads all the values in the field. If the number of records it reads is more than the maximum it is allowed to display, Access does not display the values for that field on the list.

To optimize a specific bound text box control on a form, display the form in Design view, display the property sheet for the control, and then set the FilterLookup property according to the performance you want to achieve:

• If the lists take too long to display in nonindexed fields only, limit the lists to indexed fields. Set the FilterLookup property to Database Defaults; then, on the Edit/Find tab in the Options dialog box (Tools menu), select the Local Indexed Fields check box and clear the Local Non­Indexed Fields and ODBC Fields check boxes.
• To prevent a list from displaying, regardless of the database defaults, set the FilterLookup property to Never.
• To force a list to display, regardless of the database defaults, set the FilterLookup property to Always.
• If lists are not displaying the values from indexed or nonindexed fields, make sure that the FilterLookup property for each field is not set to Never. If it is not, verify that the options under Show list of values in are selected. If they are, increase the number in the Don't display lists where more than this number of records read box so that it is greater than or equal to the maximum number of records in any nonindexed field in the underlying table.

Optimizing Find and Replace Performance

For the fastest searches when using the Find or Replace commands (Edit menu), search for whole field values or the first character within a single indexed field. If you search the same nonindexed field repeatedly, index the field.

Optimizing Form and Subform Performance

There are several things you can do to make your forms run faster:

• Avoid overlapping controls.
• Use bitmaps and other graphic objects sparingly.
• Convert unbound object frames that display graphics to image controls.
• Use black­and­white rather than color bitmaps.
• Close forms that are not being used.
• If the underlying record source includes many records and you want to use the form primarily to enter new records, set the DataEntry property of the form to Yes so that the form opens to a blank record.

  If you open a form with all records showing, Access has to read in each record before it can display the blank record at the end of the recordset. If the form is already open, you can click the Data Entry command on the Records menu to switch to Data Entry mode.

• Do not sort records in an underlying query unless record order is important, especially with multiple­table queries.
• Base subforms on queries rather than tables, and include only fields from the record source that are absolutely necessary. Extra fields can decrease subform performance.
• Index all the fields in the subform that are linked to the main form.
• Index any subform fields used for criteria.
• Set the subform AllowEdits, AllowAdditions, and AllowDeletions properties to No if the records in the subform will not be edited. Or set the RecordsetType property to Snapshot.
• Eliminate code from forms that do not need it by setting the HasModule form property to No.

  The HasModule property specifies whether the form has a form module. A form without a form module loads more quickly and occupies less disk space. If a form or controls on the form do not use event procedures, the form does not require a form module. For example, if your application uses a switchboard form exclusively to navigate to other forms, instead of using command buttons with event procedures, you can use command buttons with macros, or hyperlinks.

  You can still use code with a form that has no form module by calling Function procedures from a standard module using an expression. (You cannot use Sub procedures, because they cannot be called using an expression.) To do this, define a Function procedure in a standard module and then call that function from an event property of the form or a control. For example, to use a command button to call a function to open a form, add an expression like this to the OnClick property of the command button: =OpenDataEntry().

  Note   If a form currently contains event procedures, and you decide to eliminate all event procedure code from that form, you must set the HasModule property to No to completely remove the form module.

In addition to these tips, you can use the Performance Analyzer to analyze specific forms in your database. For information about using the Performance Analyzer, see "Using the Performance Analyzer" earlier in this chapter.

Optimizing List Box and Combo Box Performance

There are several things you can do to make list boxes and combo boxes run faster:

• Base the list box or combo box on a saved query instead of an SQL statement.

  If you use a wizard to create the list box or combo box, Access automatically sets the RowSource property of the control to an SQL statement. To change the RowSource property to a saved query, click the Build button next to the RowSource property. In the SQL Builder window, click Save on the File menu and enter a name for the query. When you close the SQL Builder window, click Yes when Access asks if you want to update the property.

• In the query specified in the RowSource property, include only fields that are absolutely necessary. Extra fields can decrease performance.
• Index both the first field displayed in the list box or combo box and the bound field (if the fields are different).
• Index any fields used for criteria.
• In combo boxes, set the AutoExpand property to No if you do not need Access to automatically fill in the text box portion of the combo box with a value that matches the characters that the user types.
• If the AutoExpand property for a combo box is set to Yes, the first displayed field should have a Text data type instead of a Number data type. In order to find a match in the list, Access converts a numeric value to text. If the data type is Text, Access does not have to do this conversion.
• If the bound field in a lookup combo box is not the displayed field:
  • Do not use expressions for the bound field or the displayed field.
  • Do not use restrictions in the row source.
  • Use single­table (or query) row sources rather than multiple­table row sources, if possible.
• Do not create list boxes or combo boxes based on data from linked tables if the data will not change. It is better to import the data into your database, in this case.

For information about optimizing the Filter By Form performance of a list box or combo box, see "Optimizing Filter Performance" earlier in this chapter.

Optimizing Report and Printing Performance

Here are some suggestions for speeding up report and printing performance:

• Avoid overlapping controls.
• Use bitmaps and other graphic objects sparingly.
• Convert unbound object frames that display graphics to image controls.
• Use black­and­white rather than color bitmaps.
• Avoid sorting and grouping on expressions.
• Index fields you sort or group on.
• Base subreports on queries rather than tables, and include only fields from the record source that are absolutely necessary. Extra fields can decrease subreport performance.
• Index all the fields in the subreport that are linked to the main report.
• Index any subreport fields used for criteria.
• Avoid using domain aggregate functions. Include the field in the report's underlying query or use a subreport.
• Make sure the report's underlying query is optimized.
• Use the HasData property or NoData event to determine whether a report is bound to an empty recordset.

Optimizing Visual Basic Performance

In general, you can do more to improve the speed of your code by choosing more efficient algorithms than by implementing particular coding tricks. However, certain techniques can help make your code run faster.

Compile the code in a database application before distributing it to users

In the Module window, click Debug, and then click Compile And Save All Modules.

Always explicitly declare variables

You can require that variables be explicitly declared before they are used in a procedure by selecting the Require Variable Declaration check box under Coding Options on the Module tab in the Options dialog box (Tools menu).

Use the most specific type possible when you declare variables

For example, declare a variable that is used to represent a form as type Form rather than as type Object or Variant. This is especially important if you are working with Automation objects or separate instances of Visual Basic. When working with Automation objects, instead of using a Variant data type or the generic Object data type, declare objects as they are listed in the Modules/Classes box in the Object Browser. This ensures that Visual Basic recognizes the specific type of object you are referencing, allowing the reference to be resolved when you compile.

Use variables to refer to properties, controls, and data access objects

If you refer more than once to the value of a property or control on a form, or to a data access object or its property, create object variables and refer to the variables rather than using full identifiers. This approach is especially effective for speeding up a looping operation on a series of properties, controls, or objects.

Use the Me keyword for form references within an event procedure

When you make form references within an event procedure, use the Me object variable to refer to the form. This restricts the search for the form to the local name space.

Use the IIf function judiciously

Avoid using the IIf function if either of the return expressions takes a long time to evaluate. Access always evaluates both expressions. It is often more efficient to replace the IIf function with an If...Then...Else statement block.

Use string functions when appropriate

Some functions have two versions, one that returns a Variant data type (for example, the Str function) and one that returns a String data type (for example, the Str$ function.) When working with variables declared with the String data type or when writing data directly to random-access files, use functions with names that end with $ if they are available. This makes your operations run faster, because Access does not need to perform type conversions. For more information about string functions, see Access online Help.

Use the Integer or Long data type for math when the size and type of numbers permit

The Variant data type, although more flexible, uses more memory and processor time as it translates between data types. The following table ranks the numeric data types by calculation speed.

Use dynamic arrays and the Erase or ReDim statement to reclaim memory

Consider using dynamic arrays instead of fixed arrays. When you no longer need the data in a dynamic array, use either the Erase statement or the ReDim statement with the Preserve keyword to discard unneeded data and reclaim the memory used by the array. For example, you can reclaim the space used by a dynamic array with the following code:

Erase intArray

While the Erasestatement completely eliminates the array, the ReDimstatement used with the Preservekeyword makes the array smaller without losing its contents.

ReDim Preserve intArray(10, conNewUpperBound)

Erasing a fixed­size array does not reclaim the memory for the array; it simply clears out the values of each element of the array. If each element was a string, or a Variant data type containing a string or an array, then erasing the array would reclaim the memory from those strings or Variant data types, but not the memory for the array itself.

Replace procedure calls with inline code

Although using procedures makes your code more modular, performing each procedure call always involves some additional work and time. If you have a loop that calls a procedure many times, you can eliminate this overhead by removing the procedure call and placing the body of the procedure directly inline within the loop. If you place the same code inline in several loops, however, the duplicate code increases the size of your application. It also increases the chance that you will not remember to update each section of duplicate code when you make changes.

Use constants whenever possible to make your application run faster

Constants also make your code more readable and easier to maintain. If your code has strings or numbers that do not change, declare them as constants. Constants are resolved once when your program is compiled, with the appropriate value written into the code. With variables, however, each time the application runs and finds a variable, it needs to get the current value of the variable. Whenever possible, use the intrinsic constants listed in the Object Browser rather than creating your own.

Use bookmarks instead of the FindNext method to return to a particular record

Using the Bookmark property, you can write a procedure to find a target record, store its bookmark value in a variable, move to other records, and return to the original record by referring to the bookmark. For more information about the Bookmark property, see Access online Help.

Use the FindRecord and FindNext methods on indexed fields

When locating records that satisfy a specified criteria, the FindRecord and FindNext methods are much more efficient than the Seek method when used on a field that is indexed.

Consider reducing the number of procedures and modules

While your application runs, each called procedure is placed in its own public block of memory. Access incurs some overhead in creating and managing these blocks. You can save some of this overhead by combining short procedures into larger procedures.

Organize the modules in an application

Visual Basic loads modules on demand — that is, it loads a module into memory only when your code calls one of the procedures in that module. If you never call a procedure in a particular module, Visual Basic never loads that module. Placing related procedures in the same module causes Visual Basic to load modules only as needed.

Eliminate dead code and unused variables

As you develop and modify your applications, you may leave behind dead code — entire procedures that are not called from anywhere in your code. You may also have declared variables that are no longer used. Review your code to find and remove unused procedures and variables; for example, Debug.Print statements.

To search for references to a particular variable, use the Find command on the Edit menu. Or, if you have Option Explicit statements in each of your modules, you can quickly discover whether a variable is used in your application by removing its declaration and running the application. If the variable is used, Visual Basic generates an error. If you do not see an error, the variable was not used.

If your application has places in which the contents of a string variable or a Variant data type containing a string is not needed, assign a zero­length string ("") to that variable. If you no longer need an object variable, set that variable to Nothing to reclaim the memory used by the object reference.

You can also use compiler directives and conditional compilation to ignore portions of code based on constant values that you specify. For more information about debugging Visual Basic code, see Chapter 7, "Debugging Visual Basic Code," in Building Applications with Microsoft Access 97.

Saving a Database as an MDE File

If the design of forms, reports, and modules is stable, consider saving a database as an MDE file. Saving a database containing code as an MDE file removes the source code from the file and provides some additional optimization of memory use. However, the design of forms, reports, and all code modules cannot be changed in an MDE file. To modify the design of a database saved as an MDE file, you must open the original copy of the database, make the modification to that database, and then re­save it as an MDE database.

For this reason, saving a database as an MDE file is most appropriate for a database where the design of forms, reports, and modules will not be changed, or for the front­end database in a front­end/back­end database application. A front­end/back­end database application consists of two database files. The back­end database contains only the application's tables. The front­end database contains all other database objects (queries, forms, reports, macros, and modules) and links to the tables in the back­end database. Typically, the back­end database is located on a network server, and copies of the front­end database are installed on individual users' computers.

For more information about MDE files, see "Security Features in Microsoft Access" in Chapter 29, "Workgroup Features in Microsoft Access."

Optimizing Microsoft Excel

There are several techniques you can use to optimize Excel for size and speed. In general, size optimizations decrease both the amount of memory required and the amount of disk space required. Speed optimizations usually occur when you modify worksheets so that the recalculation engine in Excel works more efficiently.

Optimizing for Size

If a worksheet contains links to large ranges on external documents, it may require a large amount of disk space and take a long time to open. To prevent this, clear the Save external link values check box on the Calculation tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu). You do not lose the links to the external data, but clearing this option prevents Excel from saving the value with the linked worksheet.

Optimizing for Speed

To optimize Excel for speed, use the following guidelines:

• Do not select the Precision as displayed check box on the Calculation tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu) unless you really need it.

  When Precision as displayed is off, Excel stores the full precision of a number in memory, and displays only the number of digits specified by the formatting. When Precision as displayed is on, however, Excel performs a math operation on every cell, which rounds the number. The operation forces the precision of the number stored in memory to be equal to the number of decimal places in the cell number format, slowing calculation speed.

• If you use complicated IF functions on a worksheet, replace them with VLOOKUP or HLOOKUP functions.
• Avoid using the functions AREAS, CELL, COLUMNS, INDEX, INDIRECT, INFO, NOW, OFFSET, RAND, ROWS, and TODAY.

  Formulas that contain these functions, or dependents of those formulas, must be recalculated every time there is a calculation, because their results may change even if their precedent cells have not changed. If you must use these functions, try to avoid having other calculations depend on their results.

• Avoid using user­defined functions and names that are complex expressions, which are recalculated more slowly than the equivalent formula in a cell.
• If your worksheets contain a large number of pictures, you can speed up scrolling by selecting the Show placeholders option for objects on the View tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu).

  If users want to switch quickly between viewing graphics and placeholders, you can assign the following two Visual Basic procedures to custom buttons.

  Sub ViewObjectPlaceholders()
  	ActiveWorkbook.DisplayDrawingObjects =&nbspxlPlaceholders
  End Sub
  
  
  
  Sub&nbspViewObjects() ActiveWorkbook.DisplayDrawingObjects =&nbspxlAll
  End Sub
  

• If you have large worksheets that take a long time to recalculate, consider selecting the Manual option on the Calculation tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu).

  Whenever you make a change to the worksheet that necessitates recalculation, Excel displays the word Calculate in the status bar. You can continue to change the worksheet, and when you are finished, press F9 to recalculate manually.

• Set your users' monitors to use only 16 or 256 colors.

  For writing reports and working with spreadsheets, users may need only 16 or 256 colors, so they can switch to a video driver that supports a lower resolution and fewer colors. They can always switch back if there is no improvement in performance or if their work requires additional video capabilities.

Optimizing Microsoft PowerPoint

There are several adjustments you can make to help PowerPoint run faster and use less disk space. These adjustments have to do primarily with using less memory or using memory more efficiently. This section explains how to make these adjustments.

If you use this operator	To combine this expression	With this expression	The resulting query has these characteristics
AND	Optimizable	Optimizable	Fully optimizable
OR	Optimizable	Optimizable	Fully optimizable
AND	Optimizable	Not optimizable	Partially optimizable
OR	Optimizable	Not optimizable	Not optimizable
AND	Not optimizable	Not optimizable	Not optimizable
OR	Not optimizable	Not optimizable	Not optimizable
NOT	N/A	Optimizable	Not optimizable
NOT	N/A	Not optimizable	Not optimizable
When you combine these expressions	With these expression types and operator	The resulting query has these characteristics
[LastName]="Smith" AND [Hire Date]<#12/30/90#	Optimizable AND Optimizable	Fully Optimizable
[LastName]="Smith" OR [Hire Date]<#12/30/90#	Optimizable OR Optimizable	Fully Optimizable
[LastName]="Smith" AND [MiddleInitial]="C"	Optimizable AND Not Optimizable	Partially Optimizable
[LastName]="Smith" OR [MiddleInitial]="C"	Optimizable OR Not Optimizable	Not Optimizable
[FirstName]="Terry" AND [MiddleInitial]="C"	Not Optimizable AND Not Optimizable	Not Optimizable
[FirstName]="Terry" OR [MiddleInitial]="C"	Not Optimizable OR Not Optimizable	Not Optimizable
Numeric data type	Speed
Integer, Long	Fastest
Single, Double	Next­to­fastest
Currency	Next­to­slowest
Variant	Slowest
	Virtual Memory