| C H A P T E R 7 | Part 2 Deploying Microsoft Office | Microsoft Office Resource Kit |
| Customizing and Optimizing Microsoft Office |
|
|
|
For automatic downloading of policies over NetWare networks, Microsoft Client for NetWare Networks must be specified as the primary network logon client, and a preferred server must be defined.
Note By default, Windows 95 and Windows NT Workstation 4.0 automatically download the system policies you created and placed on the appropriate network server. If you want to change to manual downloading, see the Microsoft Windows 95 Resource Kit or the Microsoft Windows NT Server 4.0 Resource Kit and Microsoft Windows NT Workstation 4.0 Resource Kit. If the client computers are using NETX or VLM, then you must download system policies manually.
|
How Does Windows Use System Policies? When the user logs on, Windows checks the user's configuration information for the location of the policy file. Windows then downloads the policies and copies the information in the registry using the following process. First, if user profiles are enabled, Windows checks for a user policy section that matches the user name and applies the userspecific policy. If Windows does not find a user policy section, it applies the Default User policies. If support for group policies is installed, Windows downloads group policies, starting with the lowest priority group and ending with the highest priority group. Group policies are processed for all groups to which the user belongs. Group policies are not applied if there are user policies defined for the user. These settings are copied into the USER.DAT portion of the registry. Second, Windows applies computerspecific policies to the desktop environment. If a policy section for that computer name does not exist, Windows applies the Default Computer policies. These settings are copied into the SYSTEM.DAT portion of the registry.
By default, Windows automatically attempts to download computer
and user policies from the file Config.pol or Ntconfig.pol in the Netlogon folder
on a Windows NT server or the Public folder on a NetWare
server. This default location can be overridden in a policy file
setting. If no server is present, Windows uses the settings currently
on the client computer. For more information about how Windows
processes system policies, see the Microsoft
Windows 95 Resource Kit or the
Microsoft Windows NT Server 4.0 Resource Kit and
Microsoft Windows NT Workstation 4.0 Resource Kit.
|
|
For example, the following illustration shows the default Microsoft on the Web submenu in Excel.
For users who have Web access, these commands provide hyperlinks to a wealth of information about the application and other useful Web resources on the Microsoft Web site. If no registered browser is found, these commands are unavailable.
The following table describes the commands on the Microsoft on the Web submenu.
| This command on the Microsoft on the Web submenu | Connects to this page on the Microsoft Web site |
| Free Stuff | The Free Stuff page for the application, from which you can download file converters, addons, and other free utilities. |
| Product News | The home page for the application. |
| Frequently Asked Questions | The Frequently Asked Questions (FAQ) page for the application. This page is maintained by Microsoft Support Services. |
| Online Support | The online support page for the application. This page is maintained by Microsoft Support Services. |
| Microsoft Office Home Page | The Office home page, where you can find Officewide and applicationspecific information. |
| Send Feedback | An Officewide feedback page, where you can submit feature requests, bug reports, or product purchasing and licensing information. |
| Best of the Web | A page that contains hyperlinks to other useful reference sites, such as home, reference, and financial information. |
| Search the Web | A page that contains hyperlinks to Web search pages. |
| Web Tutorial | The Microsoft Web tutorial. |
| Microsoft Home Page | The Microsoft home page at http://www.microsoft.com/. |
Note In the Visual Basic environment of the Office applications, some of the Microsoft on the Web commands function differently. The Microsoft Office Home Page command is replaced by the For Developers Only command, which connects you to:
http://www.microsoft.com/msdn/
The Send Feedback command connects you to:
http://www.microsoft.com/officedev/misc/feedback.htm
Tip You can use the System Policy Editor and the Office policy template files to edit your own registry. In this case, the System Policy Editor displays only the Office-related registry keys and values that you are most likely to want to change. To edit your own registry settings, click Open Registry on the File menu in the System Policy Editor.
Disabling Commands on the Microsoft on the Web Submenu
The Microsoft on the Web submenu commands are enabled only if an Office application detects a registered Web browser. If these commands are available, but you want to disable them for some or all users in your workgroup, you can do so by using the System Policy Editor to modify Office system policies.
You can disable the applicationspecific commands (commands one through four on the submenu), or the Officewide commands (commands five through nine on the submenu). If you disable both groups of commands, the Microsoft Home Page command is also disabled.
To
disable the applicationspecific commands on the Microsoft
on the Web submenu
– or –
If you have previously created a system policy file, click Open on the File menu, and select the file Config.pol (for Windows 95 clients) or Ntconfig.pol (for Windows NT Workstation clients).
– or –
To set system policies for a specific user or group of users, on the Edit menu, click Add User or Add Group, and enter the name of the user or group. Then double-click the new icon.
User\application\Internet\Help_Microsoft on the Web
To distribute the new policy file, you must store it on a network server. For more information about using the System Policy Editor, see "The System Policy Editor" earlier in this chapter.
To
disable the Officewide commands on the Microsoft on the
Web submenu
– or –
If you have previously created a system policy file, click Open on the File menu, and select the file Config.pol (for Windows 95 clients) or Ntconfig.pol (for Windows NT Workstation clients).
– or –
To set system policies for a specific user or group of users, on the Edit menu, click Add User or Add Group, and enter the name of the user or group. Then double-click the new icon.
User\Office 97\Internet\Help_Microsoft on the Web
To distribute the new policy file, you must store it on a network server. For more information about using the System Policy Editor, see "The System Policy Editor" earlier in this chapter.
Adding Your Own Commands to the Microsoft on the Web Submenu
You can replace or add to the default commands on the Microsoft on the Web submenu with your own commands. 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
add your own commands to the Microsoft on the Web submenu
– or –
If you have previously created a system policy file, click Open on the File menu, and select the file Config.pol or Ntconfig.pol.
– or –
To set system policies for a specific user or group of users, on the Edit menu, click Add User or Add Group, and enter the name of the user or group. Then double-click the new icon.
User\Office 97\Internet\Help_Microsoft on the Web
&command name,URL
where the character after the ampersand (&) will appear underlined in the menu.
For example, to add the command Office 97 Resource Kit that connects to the Office Resource Kit home page on the Web, you would enter:
Office 97 &Resource Kit,http://www.microsoft.com/office/ork/
To distribute the new policy file, you must store it on a network server. For more information about using the System Policy Editor, see "The System Policy Editor" earlier in this chapter.
Customizing Web Connections in Office 98 for the Macintosh
 | In Office 98 for the Macintosh applications, the Help on the Web command on the Help menu opens an HTML document in your Web browser. This HTML document, named Webhelp.htm, is installed in the Microsoft Office 98:Office:Help folder. For users who have Web access, the Help on the Web command provides hyperlinks to a wealth of information about the application and other useful Web resources on the Microsoft Web site. |
|
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 LowMemory Computer
If the Windows desktop has a wallpaper (fullscreen 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 give the best 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:
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.
These changes affect only Access 97.
These settings affect only a particular database application or session of Access. You use the /profile commandline 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 readahead caching, writebehind 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.
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 highwater 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 highwater mark.
By default, Microsoft Jet allocates memory for its internal buffer on an asneeded 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 highwater 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 provides the best 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.
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.
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 peertopeer 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 serverbased networking system that manages lock retries itself, such as Windows NT Server or Novell NetWare, there is no need to change this setting.
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.
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 the best performance results by following these guidelines for table design.
Design Tables Without Redundant Data
A welldesigned 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
The first two panels of the wizard contain introductory information about normalization.
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 MultipleField Indexes When Joining Multiple Fields
If you use multiplefield 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 multiplefield 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 multiplefield 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 multiplefield 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 singlefield index on FirstName to optimize the query.
The simplest approach is to add multiplefield indexes to fields used in multiplefield joins and also add a singlefield index to any field on which you use criteria to restrict the values in the field. You can add both a multiplefield index and a singlefield 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:
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.
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.
This allows Access to transfer less data over the network.
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.
Optimizing SQL Database Performance
When you connect to an external SQL database table, you achieve the best 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:
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.
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.
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.
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:
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, Addins 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:
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.
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.
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 recordlocking 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):
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.
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.
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.
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.
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.
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.
For example, suppose this is Query1:
SELECT Format$(Field1)As TempField FROM Table1
Query1 is nested in the following query, Query2:
SELECT * FROM Query1 Where TempField = 100
Nesting Query1 in Query2 causes optimization problems due to the expression Format$(Field1) in Query1. For best performance, use calculated fields only in the toplevel 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.
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.
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 dataaccess 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.
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.
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 multiplefield 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")
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.
| 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 |
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.
| 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 |
Additional Notes on Rushmore Optimization
For example, if you have a multiplefield index on LastName, FirstName, you can optimize a query on LastName or on LastName and FirstName, but you cannot optimize a query on FirstName.
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 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:
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:
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.
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:
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.
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:
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.
| Numeric data type | Speed |
| Integer, Long | Fastest |
| Single, Double | Nexttofastest |
| Currency | Nexttoslowest |
| Variant | Slowest |
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:
While the Erase statement completely eliminates the array,
the ReDim statement used with the Preserve keyword
makes the array smaller without losing its contents.
Erase intArrayReDim Preserve intArray(10, conNewUpperBound)
Erasing a fixedsize 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 zerolength 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 resave 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 frontend database in a frontend/backend database application. A frontend/backend database application consists of two database files. The backend database contains only the application's tables. The frontend database contains all other database objects (queries, forms, reports, macros, and modules) and links to the tables in the backend database. Typically, the backend database is located on a network server, and copies of the frontend 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."
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.
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.
To optimize Excel for speed, use the following guidelines:
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.
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.
If users want to switch quickly between viewing graphics and placeholders, you can assign the following two Visual Basic procedures to custom buttons.
Sub ViewObjects()
ActiveWorkbook.DisplayDrawingObjects = xlAll
End SubSub ViewObjectPlaceholders()
ActiveWorkbook.DisplayDrawingObjects = xlPlaceholders
End Sub
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.
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
 | Virtual Memory |
| | Tip In Windows 95 and Windows NT Workstation 4.0, you can use a system policy to define the default value for the Export pictures option on the Advanced tab in the Options dialog box (Tools menu) for all PowerPoint users in your workgroup. In the System Policy Editor, set the following policy: |
| | Tip In Windows 95 and Windows NT Workstation 4.0, you can use a system policy to define the default value for the Link sounds with file size greater than option on the General tab in the Options dialog box (Tools menu) for all PowerPoint users in your workgroup. In the System Policy Editor, set the following policy: |
Users may not need the highest resolution video driver and the up to 16 million colors their video drivers support. Additional color support in a video driver can dramatically decrease the speed of screen updates when your users scroll or update graphics.
For writing reports and working with spreadsheets, users may need only 16 to 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.
To speed up printing, try the following:
Animated screen savers use computer processor time that you can allocate to a print job. For more information, see your screen saver documentation.
This option allocates processor time to Word during
a print job so users can continue working while Word is printing,
but this means less processor time is available for printing.
To turn off background printing, clear the Background Printing
check box on the Print tab in the Options dialog box
(Tools menu).
|
