Designing Lotus Notes Applications That Perform

By Lisa Woody

''Those of us encountering applications that work just fine in limited test environments but subsequently provide unacceptable performance under a full load, know that it is less painful to do the necessary design work up front, rather than redesign an application already in use. This article, based on experience with Lotus Notes Release 3, recommends ways to design applications for the best performance right from the start.''

From a user's perspective, Lotus Notes application performance is measured in response time at the client workstation. The most time-consuming elements are loading and viewing an application and saving a document or form.

In fact, several factors affect response times and some involve trade-offs. It is often necessary to sacrifice some program function in order to improve overall performance. Conversely, adding special functions may require giving up some performance.

It is the application developer's job to understand trade-offs and to balance function against performance. But sometimes, clever design techniques can buy performance without reducing function. In simple applications with small databases, many of these issues may not be significant; but because Lotus Notes applications tend to grow, it is wise to always design for performance. The following, then, are some suggestions for designing your next Notes application.

Views
A view in Lotus Notes is an organized way to display fields from documents, so that documents, and the information within them, can be easily located. A view can serve as a simple report; however, because users like to slice data many ways, it is tempting to design a large number of sophisticated views for a database. Before doing this, it is important to remember that behind every successful view stands an index, the structure that is built to enable a view to display.

A user measures a view's performance by how long it takes to open; that time is based upon how long it takes to build the index. (In severe cases, I have seen views take almost an hour to open!) The main factors in a view's performance are:
 * How often the view is out of date
 * How often the view is rebuilt
 * How large the view is
 * How complex the view is
 * How many views there are

When a document is added, changed, or deleted, its view is outdated and is added to a queue, indicating that it needs to be updated. At selected intervals, the Update task, which runs on the server, will update that view, based upon the index setting.

Index Settings
The index setting for the view is set in View Attributes and has three options: Automatic, Automatic-at most every xx hours, and Manual/Background. The default setting for a view is Automatic, meaning that an out-of-date view is updated whenever it is opened by a user. Therefore, in a volatile database, if a view is opened by a user before the indexer has rebuilt it in the background, the view may be rebuilt while the user waits.

Automatic-at most every xx hours forces the view to be rebuilt only after the specified interval xx has passed. This means that, in many cases, the view will open for the user immediately. A yellow icon with a black question mark in the corner of the view will notify the user that the view is out of date, and the user can choose to refresh the view if desired.

With the third index setting option, Manual/Background, the view will always open immediately because it is never rebuilt at opening. If the view is out of date, the user will be informed, via the yellow icon, and can choose to explicitly rebuild the view. Of course, server tasks will still rebuild the view in the background.

Carefully considering and selecting the index setting option can make a big difference in the response your users see, so consider changing the default for selected views.

Improving View Performance
Since each change to a document can potentially affect more than one view, it becomes clear that the number of views in a database also affects performance. If the server is busy updating multiple views, it cannot be responding to other user requests. That's why it is a good idea to limit the number of views in a database. In fact, the rule of thumb is to try to keep the number of views in an application to a minimum and to make each view as simple as possible.

Lotus Notes Release 4 is expected to improve the indexer's performance. In addition, Release 4 may alleviate the need for some views, because it offers the user some ability to sort and resize view columns on the fly.

In a case where a large number of specialized views is needed, consider making those views private. Private views are built and stored on the user's workstation and do not affect server performance.

The size of a view and its index is the major factor in how long the index update takes. The number of documents selected for the view and the number of columns in the view directly affect the index size. Documents categorized on multi-value fields may appear in multiple categories and may considerably increase the view's index size.

Views whose column definitions contain many or complex formulas are also candidates for improvement. Moving a calculation from a column to the form will often dramatically speed up the opening of a view. Although a calculation done once in a form may not be noticeable, that same calculation done for thousands of documents in a view may seem to take forever.

Sorted columns take longer to index than unsorted columns, and categorized columns take longer still. If you add a sorted or categorized column to a view (which previously had neither), indexing time will noticeably slow down. Additional sorted or categorized columns also contribute to a slowdown, but subsequent slowdowns will decrease in severity.

Keep selection formulas as simple as possible, and avoid selection formulas that use time/date functions as they will continually render the view out of date. Reducing the complexity of the formulas contained in a given view's design can significantly improve that view's performance. The more documents contained in a view, the more noticeable the improvement will be if you implement such a change. Time functions that depend upon the present clock will be slow, because the view indexes must be rebuilt each time the view is entered. An example is having the function @Now in the column formula.

You can determine how large your views really are by looking at the appropriate Database Sizes document, found in the Database Sizes view in the Notes server log (see Figure 1). In a large database, each index can take a considerable amount of space. In fact, the maximum size of a view in Lotus Notes Release 3 is 130 MB, a substantial percentage of the total database's 1 GB limit.



When you look at the server log, you may be surprised to see that some views are very large, while others are listed as 0 (zero). That's your clue about views that your users are not using. Depending upon the index options, the view may not be built until a user asks to display it, so a view with 0 size likely has zero users. Those views are excellent candidates for deletion.

Unread marks, which provide a visual indication of whether or not the user has read a document, may significantly affect the performance of any given view. Try to use Unread marks only in those views that require them. If you have to use them, choosing "Unread Documents only" instead of "Compute and Display at all levels of View" can noticeably improve performance.

Finally, user education can come into play. If a Notes application is very large, users should know that performance slows down when they open multiple views concurrently. Although this is not a design issue, you should teach your users that F9 causes a refresh and should not be used unless needed, to avoid putting extra load on the server.

Forms
Forms that take to long to open or close will also be a thorn in your users' sides. In some cases, you can make enough information available in the view so that the form is opened only in exceptional cases. Although this is not always practical, it sometimes works well. Lotus' design of the Personal Name and Address book uses this principle, displaying phone numbers in the view so that the user doesn't have to open the document to obtain the needed information.

When designing forms, you should remember that the total number of fields in a form affects both form and view performance. The rule of thumb is the fewer fields, the better the performance. Forms containing a large number of fields may take a long time to open and save. If the form has many fields that contain summary data (i.e., non-rich text fields), it will also affect views, because summary data is loaded as part of building the index.

Earlier, I recommended that you move calculations from view columns to fields in the form. Unfortunately, a large number of calculations in a form will affect the form's performance, so it is helpful to design your application such that these calculations are done only when necessary. Computed fields are recalculated every time a document is saved, whether or not they need to be calculated.

"Computed for Display" fields allow data to be displayed that is not physically stored. Try to take advantage of these fields for data that does not need to be displayed in a view. There are probably many instances where database fields are needlessly calculated and stored, based on values in other fields that remain constant. You can designate all of these types of fields as "Computed for Display," which reduces total document size and improves performance.

Many developers use buttons to provide a friendly user interface, but the cost may be more than you realize. A button designed to save a document may double the time to save, since both the button and the save cause a semi-recalculation. In a large form, this can try a user's patience.

Batching calculations can save time. Design the form so that the user fills it out and saves it; the save is quick since the calculations are not done in field formulas. Instead, you can run an hourly background macro to set the calculated fields.

Except for use on the simplest form, you should avoid the "Refresh fields" option, set in Form Attributes, because it recalculates every time the cursor leaves a field.

@Db Functions
@Db functions (@DbColumn, @DbLookup, and @DbCommand) are among the most expensive functions when it comes to performance; therefore, you should use them judiciously. Minimize the number of @Db functions in any form, and try to code them to run only when needed.

Never use two lookups where one will do. For example, you can use the formula in Figure 2 to test for @DbLookup errors by setting a temporary variable to the value returned by the lookup, then testing that variable to see if an error was encountered. If there was no error, the value is available in the temporary variable for your further use.

temp := @DbLookup(""; "Server":"Database"; "View1"; key; 2); FIELD Value := @If(@IsError(temp; "Error doing lookup"; temp)

'''Figure 2. Testing for DbLookup Errors'''

If you need to look up more than one field related to the same keyword, use a single @DbLookup to return a list of concatenated values, rather than doing multiple lookups that each return a single piece of data. The resulting list can then be parsed, and the fields can be set, as shown in Figure 3. Figure 3A shows the actual view, Figure 3B is the column formula, and Figure 3C is the button formula.



'''Figure 3A. View of Button Doing Multiple Lookups'''



'''Figure 3B. Multiple Lookup Column Formula'''



'''Figure 3C. Multiple Lookup Button Formula'''

Design your application so that the lookups are done only when necessary. Computed fields are calculated every time a document is saved. If a field's value is determined via a lookup, but its value is not expected to change often, consider putting the @Db function into a button rather than into a field formula. In this way, the user can control when to run the formula.

Keyword fields with values populated by a lookup cause that lookup to be done every time a document is opened, even for a read. For documents that are frequently read but seldom edited, and that contain keyword fields populated by @DbColumn commands, try a special formula. By using an @If statement with a clause such as @IsNewDoc or @IsDocBeingLoaded, you can control when the lookup takes place.

Code lookups for efficiency; if the data in the view does not change often, use the Cache option (the default in the lookup formula). This enables a view used for lookups to be cached in memory where it can be reused, thus saving time. Of course, where it is critical for the looked up data to be current, the NoCache option can be used, at the expense of performance.

If you are looking up a rich-text field, you must use the field name for the lookup value. Otherwise, it is faster to use the column number, because the data can be pulled from the view index. When the field name is used, the entire document must be read.

Finally, create either a small external database designed for lookups, or specialized views that perform efficiently. Much of the time spent in a lookup can be in opening the view, and as discussed above, views in smaller databases perform much better. Limiting the number of documents in a lookup view and keeping those views up to date can increase performance of these functions.

Overall
Applications may function better by splitting a large database into smaller logical units. For example, lookups for personnel information may be better accommodated by a stand- alone personnel database, rather than storing all of the information in the main application. (See the article "Designing a Scalable Lotus Notes Workflow Application" in this issue for another creative solution for splitting large databases.)

Hardware Considerations
As in all applications, hardware plays a part in Notes application performance. Since views are built on the server, server factors such as memory and processor speed affect view performance. Workstation configuration, on the other hand, is more likely to affect form performance. Network bandwidth between client and server can also impact performance.

It is always wise to test your application on machines configured like those of your users. It's easy to get away with sloppy design techniques on a 100 MHz Pentium system when the database is local, but that same application may prove unacceptable on a more moderately configured machine communicating across the network to a remote server.

Science and Art
Designing a Notes application is both a science and an art. The science is in knowing the techniques for efficient programming. The art is in making the trade-offs between function and performance. Careful planning and design at the beginning can help you create applications your users will appreciate. Happy designing!