Duffbert's Random Musings :: How to Optimize Database Performance Using Database Properties

Category Software Development

The following is a very nice KnowledgeBase entry that covers the different database properties and how they can enhance or degrade the performance of your application....

How to Optimize Database Performance Using Database Properties

Document Number: 1085205

Problem
How can you optimize the performance of your large application(s) ?

Content
You can specify the following options to provide higher performance and functionality for specific uses. Note that these options are for advanced Notes users. If the options in the Advanced tab are not visible to you, it requires an administrator to change them.

Don't maintain unread marks

Maintaining unread marks in a database slows performance. For some databases, such as the Domino Directory or the Domino log file, unread marks are not useful. If a database does not require tracking read and unread documents, consider disabling unread marks to improve performance.

Document table bitmap optimization

Notes refers to tables of document information to determine which documents appear in an updated view. Selecting the "Document table bitmap optimization" property associates tables with the forms used by documents in each table. During a view update, Notes searches only tables whose views contain forms used by documents in that view. While there is a slight performance cost to maintaining this association, this setting speeds updates of small views in large databases significantly.

To enable optimization using the table-form association, select "Document table bitmap optimization." When you change this setting, compact the database to enable it. Make sure your system has sufficient disk space as this compact makes a temporary copy of the database. You can also use the load compact command with the -F or -f switch to enable or disable bitmap optimization.

Don't overwrite free space

To prevent unauthorized users from accessing data, Notes overwrites deleted data in databases, which can reduce database performance. In some situations, this security features is not necessary, such as when:

The database is physically secure -- for example, on a password-protected server in a locked room
Space in the database is quickly reallocated -- for example, in system databases such as MAIL.BOX
Security is not an issue -- for example, in an employee discussion database

Maintain Last Accessed property

Domino databases store the date when a document was last modified or read. By default, the database records only changes to documents -- not reads. If you select the database option "Maintain Last Accessed property," the database records reads of a document as well as changes to it. If you set the database to delete documents based on intervals without activity, such as 10 days without being read or modified, select "Maintain Last Accessed property" and be aware this may negatively impact database performance. Otherwise, leave the option deselected for best performance.

Disable transaction logging

When disabled, turns off logging of all transactions for all Domino API functions. It also turns off full database integrity and a replacement of Database Fixup on system restart with high-speed transaction roll forward/rollback from transaction logs along with support for backup and recovery APIs.

Allow soft deletions

The "Allow soft deletions" property lets deleted documents remain in the database and not be permanently removed for a set number of hours. The hours are set by the database manager in the Advanced tab of the Database Properties box. After that time, the document is permanently deleted from the database.

Don't support specialized response hierarchy

Documents store information about their parent or response document, which is used only by the @functions @AllChildren and @AllDescendants. In databases that don't use these @functions in views, select the database property "Don't support specialized response hierarchy" to improve database performance.

Don't allow headline monitoring

Users can set up their headlines to search databases automatically for items of interest. If many users do this, database performance can be slow. To prevent a database from being monitored, select "Don't allow headline monitoring."

Allow more fields in database

For a Release 5 database, you can select the advanced database property "Allow more fields in database" which allows the database's UNK table to contain up to 64,000 fields.

Note: Not all areas of R5 have been coded to support the Large UNK Tables. If you turn on the "Allow more fields in database" property, once your UNK Table gets large enough and starts using the Large UNK Tables, then areas of the product that cannot handle large UNKs will not work. Currently this includes the following:

- Full Text Indexing query by field
- Query by Form

For a Release 4 database or a Release 5 database without this option selected, all the field names in a database when concatenated cannot exceed 64 kilobytes, which results in a database limit of approximately 3000 fields.

Limiting the number of entries in the $UpdatedBy fields

A document stores the name of the user or server that made each change to it in the $UpdatedBy field. This edit history requires disk space and slows both view updates and replication. If you do not need to maintain a complete edit history, specify the number of changes that the $UpdatedBy field tracks with the database setting "Limit entries in $Updated fields." Once the $UpdatedBy field reaches this limit, the next edit causes the oldest entry to be removed from the $UpdatedBy list. Limiting the number of entries in the $UpdatedBy field in documents improves database performance.

Limiting the number of entries in the $Revisions fields

A document stores the date and time of each change saved to it in the $Revisions field. Domino servers use this field to resolve replication or save conflicts. The $Revisions field stores up to 500 entries by default. If you do not need to track changes this closely, specify the number of changes that $Revisions field tracks with the database setting "Limit entries in $Revisions field." Once the $Revisions field reaches this limit, the next edit causes the oldest entry to be removed from the $Revisions list. Limiting the number of entries in the $Revisions field in documents improves database performance.
Consider limiting the entries in $Revisions fields in databases that:

Contain many documents
Replicate often or have no replicas
Contain documents that are rarely edited

Undelete Expire Time (in hours)

Provides the ability to limit the time that users can restore deleted notes by accessing them in a special view type. By setting the "soft delete" database option and creating this view mistakenly deleted documents can be retrieved simply and quickly.