Coding faster lookups in IBM Lotus Notes and Domino

Read about 11 tips for coding faster lookups in IBM Lotus Notes and Domino. The author looks at the @DbLookup @Formula in Lotus Notes and Domino and describes some new tips for developers to use when coding new applications or troubleshooting performance problems in existing applications.

Share:

Raphael Savir, Principal Developer, LS Development Corporation

Raphael Savir, principal developer at LS Development Corporation (http://www.lsdevelopment.com) has been developing Notes/Domino applications and analyzing application performance problems since the early 1990s. He has spoken at Lotusphere and other conferences on several occasions on these topics.



13 March 2007

Also available in Chinese Japanese

In this article, we look at perhaps the single most popular @Formula in IBM Lotus Notes and Domino, @DbLookup. Current developers of Lotus Notes/Domino applications probably cannot envision creating an application without the use of this formula, and more than 15 years of performance testing and customer troubleshooting have demonstrated that this formula is often used dozens of times in a single form and usually in multiple forms throughout an application.

This same experience has shown us, though, that performance problems are frequently associated with these @DbLookup formulas. We’ve seen sophisticated enterprise applications brought to a virtual standstill by unacceptably slow performance related to these formulas.

In this article, we describe 11 tips that are guaranteed to speed up virtually any application. These tips range from simple one-liners to more radical changes in the way you approach your @DbLookup formulas, but all are time tested.

We assume some knowledge of Lotus Notes/Domino development, so the basic arguments of the @DbLookup formula, for example, are not covered.

Throughout this article, we use various examples to demonstrate the value of a tip. To keep things simple, we use a single application, that is, a Help Desk application containing a Contact form and a Ticket form. The basic workflow of this application is that a customer calls, you create a Ticket document, and then, as you get information about the customer's company and name, you choose the appropriate company name and contact name from drop-down lists. You also choose the appropriate category of the call (for example, Product Help, Sales, and so on). Right away, we have a few cases in which an @DbLookup formula would be helpful:

  • Selecting the company name from a list of all companies
  • Selecting the customer name from a list of all contacts at that company
  • Selecting the problem category from a list of predetermined, or dynamic, problem categories

Trap for errors

We all wish we didn’t need to trap for errors, but we do. In @DbLookup formulas (in this article, @DbLookup is used to refer to both @DbLookup and @DbColumn formulas), errors tend to be trickier because your formula often depends on user input for both the key and the returned data. This means that your formula might work on day 1, but fail on day 2 -- always a tough situation for a developer.

For example, suppose that the company name for your company is spelled three or four different ways in different Contact documents (LSDevelopment Corporation and LS Development Corporation, for example). If you then try to look up the contact names for everyone at that company, but type in "LSDevelopment Company," for example, you will fail to find any matches.

There are ways to enter the key more accurately (for example, drop-down lists), but for the sake of argument let’s assume that your @DbLookup may return an error, even with rigorous development techniques.

The basic method for error checking is to write your code as shown in listing 1:

Listing 1. Error checking
v := @DbLookup(“Notes”; “”; “(Lookup-ContactsByCompany)”; CompanyName; 
“ContactName”);
@If(@IsError(v); “The program cannot find any contacts for this 
company”; v)

In this code, we perform the lookup and then check for an error condition. If an error exists, we return a text string to let the user know that there is a problem; otherwise, we return the value(s) from the lookup.

Although this has nothing to do with performance, it’s such a basic problem seen in so many applications that we cannot skip it. Consider it a bonus tip.


Minimize the frequency of your lookups

Consider the following code for a lookup formula:

@If(@IsError(@DbColumn(“Notes”; “”; “(Lookup-Companies)”; 1); “There are no
company names”; @DbColumn(“Notes”; “”; “(Lookup-Companies)”; 1))

This is a very common mistake in that we conduct the lookup twice, which is a problem from a performance perspective. You might imagine that the first lookup perfectly caches your lookup so that the second lookup is free, but that is not correct. The second lookup is faster than the first one because the results are indeed cached, but it’s not free. Because there is no functional reason to code your formula that way, you should instead code it like this:

v := @DbColumn(“Notes”; “”; “(Lookup-Companies)”; 1);
@If(@IsError(v); “There are no company names”; v)

Another kind of mistake that causes unnecessary lookups is to have, for example, a computed field that has a lookup formula that computes when:

  • The document is first created
  • Various buttons are pressed that refresh the document in edit mode
  • The document is saved
  • The document is opened in read mode at a later date
  • The document is opened in edit mode at a later date
  • The document is saved at a later date

Most often, you are interested in having the lookup performed only once, when it’s first created. In this case, make the field Computed When Composed to prevent further computations.

In other cases, you may want this lookup to be performed every time the document is opened in edit mode. In this case, rewrite the formula as follows:

@If(@IsDocBeingEdited; “”; @Return(FieldName));
v := @DbColumn(“Notes”; “”; “(Lookup-Companies)”; 1);
@If(@IsError(v); “There are no company names”; v)

In this case, FieldName is the name of the field in which we have this formula. If the document is not being loaded in edit mode, then the formula simply retains that value and stops executing (see figure 1). Otherwise, we execute the @DbColumn, as before.

Figure 1. Keyword formula
Keyword formula

Use caching correctly

Another common error is to misunderstand the use of the NoCache argument. Often, the error in reasoning is to think that the more important the data is, the more important it is to use NoCache. In fact, the line of reasoning should simply be how frequently your data changes with respect to how frequently you perform your lookups.

For example, if the data you are looking up changes infrequently, say, monthly, then it is utterly inconceivable that you would ever need NoCache in your lookup formulas. In our example, suppose that we had created a handful of categories for the Ticket document to reference. The list may be maintained by the owner of the application and updated every few months, perhaps as new products are released or new business ventures are entered into.

On the other hand, suppose that our Ticket document looks up the name of the customer calling in. If this customer calls in twice in an hour, then we’d hate to miss the name in our lookup on the second ticket simply because the list of names was cached in earlier lookups. In this kind of application, we frequently see Help Desk staff with the database open all day long, so it’s easy for cached lookups to remain cached for long periods during the day.


Avoid the pitfalls of drop-down lists

Of all the terrible performance problems we’ve seen in forms over the years, drop-down lists have figured prominently in many of them for two simple reasons: Drop-down lists often look up large lists, and they calculate even when in read mode.

You can perform a simple test on your own application. Open a document in read mode that uses a form you perceive as being slow. As the document opens, watch the screen carefully to see where it pauses and make a note of those spots. This may be tricky because you have only a second before the screen shifts, but if you get someone to do it with you, then one of you can call out a field label, say, while the other writes down the labels. Now open your form in IBM Lotus Domino Designer and look at what is directly below those spots. Most likely, you’ll find drop-down lists with @DbLookup formulas. What’s going on?

When a document is opened in read mode, all the @DbLookup formulas are verified. They don’t actually return the values because the document is in read mode, but they still go through the motions, and that takes time. For drop-down lists, it’s even worse because the keyword field is populated with the values the user can choose, just in case he or she switches to edit mode. You may think that we’d wait until the user switches to edit mode, and then make the user pay, but that’s not how it works in Lotus Notes. Interestingly, that is exactly how it works for Web browsers, even running against exactly the same forms.

One trick that has served us well in cleaning up drop-down lists is to combine three different features to prevent unnecessary lookups. These are all easy to do, but you must do all three.

First, in the formula of your drop-down field, use this formula:

@If(@IsDocBeingEdited; “”; @Return(FieldName));
v := @DbColumn(“Notes”; “”; “(Lookup-Companies)”; 1);
@If(@IsError(v); “There are no company names”; v)

This is similar to what is described in "Maximize the frequency of your lookups," and it prevents the keyword drop-down list from performing the lookup if the document is in read mode. If the user opens the document in edit mode, of course, the drop-down list will calculate normally. Now we must make sure that we calculate the lookup if a user switches from read mode to edit mode. That’s what the next two steps are for.

In the properties of your drop-down field, enable the attribute “Refresh choices on document refresh” (see figure 2). This allows the keyword drop-down formula to recalculate if the user switches from read mode to edit mode as long as the document is forced to refresh.

Figure 2. Properties for drop-down field
Properties for drop-down field

And, finally, in the Postmodechange event of your form (see figure 3), include the following code to force a refresh when the user switches from read mode to edit mode:

If source.EditMode Then Call source.Refresh

Figure 3. Postmodechange event
Postmodechange event

Use buttons and picklists instead of drop-down lists

In some cases, there are so many drop-down lists, and they’re so large and used so frequently, that the only reasonable solution is to stop using them on the main form. The line of reasoning to use is to ask yourself the following questions:

  • How many times will a document be read in its life?
  • How many times will a document be edited?
  • Of the times it’s edited, how many times will these lookups be required?

Frequently, the answer is something like, “The documents are typically read 10 different times but edited only a couple times. Of those edits, these drop-down lists are used only once.” Of course, mileage always varies according to use, but these are good questions to ask yourself. If it seems as though the document is frequently edited, but your lookups are seldom needed, the previous section will not suffice because the drop-down lists will recalculate every time the user is in edit mode, even though the lists won’t really be needed more than once. In that case, consider using buttons, possibly in conjunction with a picklist.

The advantage to using a button is that it lets you remove the lookup formulas from your field. Instead of being a drop-down list, your field becomes just a regular text field, probably Computed when Composed, with a formula of FieldName (in the example that follows, ProblemCategory). Your button would be hidden in read mode to avoid confusion, but when clicked in edit mode, it would use the formula shown in listing 2:

Listing 2. Formula for a button
tlist := @DbColumn("Notes"; ""; "(Lookup-Categories)"; 1);
list := @Unique(@Explode(tlist; "~"));
@If(@IsError(list); @Return(@Prompt([Ok]; "Error"; "The program was unable to lookup the 
Problem Categories.")); "");

dv := @If(ProblemCategory = ""; @Subset(list; 1); ProblemCategory);
v := @Prompt([OkCancelListMult]; "Problem Category"; "Choose one or more problem 
categories for this ticket."; dv; list);

FIELD ProblemCategory := v;

You can further streamline performance by using @Picklist instead of @Prompt and @DbLookup. The disadvantage is that you have less control over the layout of the pop-up and the underlying data. For example, see the code in listing 3.

Listing 3. Using @Picklist
v := @PickList( [CUSTOM] ; “” ; “(Lookup-Categories)” ; “Problem Category” ; 
“Choose one or more categories from the list.” ; 1 )
result := @If(@IsError(v); “The program was unable to lookup the Problem Categories”; v);
FIELD ProblemCategory := result;

Use layout regions (or pop-ups) instead of drop-down lists

If the previous section feels like the right approach for your application due to the size and frequency of your lookups, but if it doesn’t give you the control you need, then consider the dialog box. You can use @Formula language or LotusScript to bring up a dialog box, and you can have multiple lookups in a single dialog box. This is often a great solution when your form has multiple lookups that are interrelated. For example, after the user chooses a company, he or she should then get a list of contacts within that company and perhaps a third drop-down list containing the subject lines of open tickets for that contact. In such a case, you may want to have a button as in the previous section, but with the code shown in listing 4.

Listing 4. Using a dialog box
Dim w as NotesUIWorkspace
flag = w.DialogBox ( "(Dialog-CompanyLookup)", True, True, False, True, 
False, False,
"Company Name", doc, False, False, False )

We won’t detail each parameter here, but the developer’s help reference is shown in listing 5.

Listing 5. Help reference
flag = notesUIWorkspace.DialogBox( form$ , [autoHorzFit] , [autoVertFit] , 
[noCancel] , 
[noNewFields] , [noFieldUpdate] , [readOnly] , [title$] , [notesDocument] , 
[sizeToTable] ,
[noOkCancel] , [okCancelAtBottom] )

In your (Dialog-CompanyLookup) form, you can now insert a layout region and put in all your drop-down fields with no regard to @IsDocBeingEdited. Users pay the price of waiting for these lookups only when they click the button and bring up the dialog box.

Some additional thoughts for using dialog boxes:

  • It is sometimes helpful to create a temporary Notes document that you don’t save but is your [notesdocument] argument in the preceding code example. This allows you to perform more finely honed lookups using LotusScript to create a list that is dropped into the dialog box. For example, perhaps you want to reference only companies that have had contact in the last three months.
  • It may be convenient to have your validation checking in the dialog box form so that users cannot get back to the main document until their data has been verified. This can reduce user annoyance caused by having to go back into the dialog box.
  • You can add graphics, help text, and more to make your dialog box more attractive and functional than a plain drop-down list or @Prompt box.
  • If your application is ported to the Web, you can fairly easily replace your dialog box with a JavaScript pop-up window. The layout region itself cannot be presented to browsers, but you can mimic the layout with <div> tags (HTML code that allows you to position a block of content anywhere on the page) or tables.
  • And, finally, you can use nested tables and the [sizeToTable] argument to make it look attractive in the Notes client. This form would then port more easily to the Web.

Use @Eval to clean up your code

On occasion, we have seen long @Formula code filled with @If statements using @DbLookup formulas. This can be cumbersome to maintain, and it can be more difficult to perform appropriate error checking (see the section, "Trap for errors") and to avoid unnecessary lookups. Using @Eval can help you avoid such problems as shown in listing 6.

Listing 6. Using an @Eval
companyLookup := {@DbColumn(“Notes”; “”; “(Lookup-Companies)”; 1);};
contactLookup := {@DbLookup(“Notes”; “”; “(Lookup-ContactsByCompany)”; 
CompanyName; “ContactName”);};

@If(someCondition = 1; @Eval(companyLookup); @Eval(contactLookup))

This code prevents lookups from being performed until the @If statement. In other words, you can set up all your lookups in advance, give them appropriate variable names, and then call on them using @Eval as needed.


Use one lookup to get multiple fields

If you find that you are looking up against the same document multiple times in order to get different data points, consider concatenating that data into a single column in the lookup view. For example, suppose that we needed the following data points from a single document:

  • ContactName
  • ContactPhone
  • ContactAddress1
  • ContactAddress2
  • ContactCity
  • ContactState
  • ContactZip

We could do it by having seven fields that each perform the lookup. The ContactName field formula would look like the code shown in listing 7.

Listing 7. ContactName field formula
v := @DbLookup(“Notes”; “”; “(Lookup-ContactsByCompany)”; CompanyName; 
“ContactName”);
@If(@IsError(v); @Return(“The program could not locate that 
document.”); v);

And the ContactPhone field formula would look like the code shown in listing 8.

Listing 8. ContactPhone field formula
v := @DbLookup(“Notes”; “”; “(Lookup-ContactsByCompany)”; CompanyName; “
ContactPhone”);
@If(@IsError(v); @Return(“The program could not locate that 
document.”); v);

And so on, for each of the seven fields.

You get much faster performance, though, if you can perform a single lookup instead of seven lookups, even given that the seven lookups are all against the same document in the same view. One way to accomplish this is to set up your lookup view so that it has a second column with the formula shown in listing 9.

Listing 9. Setting up the lookup view with a second column
@If(ContactName = “”; “NA”; ContactName) + “~” + 
@If(ContactPhone = “”; “NA”; ContactPhone) + “~” +  
@If(ContactAddress1 = “”; “NA”; ContactAddress1) + “~” + 
@If(ContactAddress2 = “”; “NA”; ContactAddress2) + “~” + 
@If(ContactCity = “”; “NA”; ContactCity) + “~” + 
@If(ContactState = “”; “NA”; ContactState) + “~” + 
@If(ContactZip = “”; “NA”; ContactZip);

Now make a hidden field in your form -- let’s call it BigLookup -- and put the formula shown in listing 10 into BigLookup:

Listing 10. Looking up ContactsByCompany
v := @DbLookup(“Notes”; “”; “(Lookup-ContactsByCompany)”; 
CompanyName; 2);
@If(@IsError(v); @Return(“The program could not locate that 
document.”); v);

Now for the ContactName field, your formula might be the one shown in listing 11.

Listing 11. Looking up ContactName
tv := @Explode(BigLookup; “~”);
v := tv[1];
@If(@IsError(v); @Return(“The program could not locate that document.”); v = “NA”; “”; v);

And for ContactPhone, see the code in listing 12.

Listing 12. Looking up ContactPhone
tv := @Explode(BigLookup; “~”);
v := tv[2];
@If(@IsError(v); @Return(“The program could not locate that document.”); v = “NA”; “”; v);

And so on, for each of the seven fields.

A couple of notes about this technique:

  • We substitute "NA" for “” in the view column formula because otherwise @Explode would collapse any blank values and make a list of fewer than seven values, which would be problematic.
  • We substitute “” for “NA” in each field formula, but that’s entirely at your discretion as is the error message.

Make faster lookup views

The performance of any kind of lookup to a view, whether using the @Formula language or LotusScript, depends on the size and speed of that view. We have compiled a handful of tips that are helpful in optimizing the performance of lookup views.

Make a dedicated lookup view

It turns out that it takes approximately 100 ms to index (refresh) a fairly typical view in a production environment. This means that every 15 minutes your server spends less than one-tenth of a second to update each of your lookup views. Of course, these times are approximate, and each application and server varies quite a bit, but many years of studying this data has convinced us that adding even several lookup views costs very little in terms of performance. Adding a dedicated view for your lookups allows you to streamline that view for performance when a user is waiting for a lookup to complete.

Streamline the view design

If your view is being used solely for looking up data from certain documents, then you can eliminate the following from the view design:

  • All the interesting fonts and colors.
  • All the action bar buttons.
  • Any LotusScript code in your view events.
  • Any references to Profile documents.
  • Extraneous columns. If you return data from those columns, keep them; if not, then eliminate them.
  • Multiple sort options for view columns. For example, there is no need for sort ascending and descending if users never access the view; all it does is add to the size.
  • Readers name fields to limit the list of choices being returned by @DbLookup. They cause too much of a performance hit.

Also, categories can be turned into sorted columns, which provide the same functionality for lookups but allow the view to index more quickly.

Streamline the data

In addition to minimizing the design, you should also consider how to minimize the data that appears in your view. Some suggestions:

  • Aggressively archive data, if feasible. Business needs typically trump any design considerations here, but it's often feasible to archive data into a second Notes database that is accessible by users on a read-only basis, for example. Doing this speeds up many functions in your primary database and, of course, it limits the data in your views, making them faster, too.
  • Carefully optimize your view selection formula so that only the data you really need displays.
  • Make sure you’re not selecting response documents that do not display yet still need to be included in the view index. A selection formula that might inadvertently do this would be SELECT Form = “Main” | @IsResponseDoc. Instead, use @DocDescendants to ensure you are only including response documents that display.
  • As an example of removing unnecessary data, we’ve seen customer applications in which a lookup view needs only the past 30 days of data, but the database must hold data for a much longer time, say, two years. It doesn’t make sense to use time- or date-sensitive formulas in the view (too slow), but it may make sense to set a flag in the documents using a weekly agent that simply marks those documents as being more than 30 days old. Now the selection formula can simply reference that flag to exclude a large percentage of the documents that would otherwise slow down the view.
  • Eliminate any display of data that is not used for your lookups.

Use "Generate unique keys in index" to eliminate duplicate entries

This is a nice trick for dramatically reducing the size of certain views. You can use this feature if your lookups get a large number of duplicate values that are then weeded out using @Unique (or the LotusScript equivalent). In that case, enabling the "Generate unique keys in index" option (see figure 4) causes the view index to display only the first instance of that text string it finds.

Be careful, however, about using this option if your sort column references a multi-value field. In that case, if the Domino server is version 6.x, you might want to use @Implode(MultiValueField; “~”) and then have your lookup formulas use @Unique(@Explode(@DbColumn(); “~”)) to get the true set of unique values. If you're using Lotus Domino 7, then this is no concern because the server intelligently displays all the unique values. Note that views using this feature index a bit more slowly because presumably the server must do more work than usual when refreshing a view. Use the feature only if it will dramatically reduce the number of documents that display in the view. In many applications, views have been reduced to 1/100 of their former size when using this feature, in which case it’s valuable.

Figure 4. View Properties dialog box
View Properties dialog box

Use Profile documents

If you have lists that are not updated by multiple users and that are not updated frequently, then a fast method for retrieving those values is to store them in a Profile document. Regardless of whether you update the list manually in the Profile document or in regular document(s) that are then ported to the Profile document, users can cache the Profile document more effectively than they can cache views, so repeated lookups are faster.

If, however, your users are updating the lists, then a Profile document is probably not appropriate because multiple updates can overwrite each other and lead to replication/save conflicts.


Use lookup databases

Large applications can often use a separate lookup database, presumably on the theory that there is so much data to look up that it makes sense to get that data -- and the required views -- out of the main database. Although there is a conceptual logic to this, in reality you usually lose more than you gain. Here are some tips for when to have your lookup data in a separate database versus when it makes sense to keep it in your main database:

  • If you have multiple applications that access the same lists, then a separate database for those lists makes sense.
  • If you have a tremendous amount of data to look up and this data requires multiple forms, then it makes sense to pull that out of a main user database.
  • On the other hand, if your lookup data uses a single form and if there are only hundreds of documents, then there is a negligible effect on your main database. Due to the advantages in terms of maintenance and performance, we therefore encourage you to keep your lookups in the main database in this case.

A twist on all this is that there may be times when performance is paramount and you have multiple databases accessing a few short lists. In that case, you may want to have these lists maintained in a separate lookup database but ported by a LotusScript agent (or Lotus Enterprise Integrator) into each of your main databases. This is more of a hassle to maintain, but it yields the best performance.


Conclusion

We hope that these tips have given you some new tools to use when coding your next application or when troubleshooting performance problems in your existing applications. Dynamic lookups are an important part of most of our applications; thus, using them so that their performance impact is minimal is the best way to ensure that we have top-shelf programs that can be used successfully for many years.

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into IBM collaboration and social software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Lotus
ArticleID=201525
ArticleTitle=Coding faster lookups in IBM Lotus Notes and Domino
publish-date=03132007