IBM®
Skip to main content
    Country/region select      Terms of use
 
 
   
     Home      Products      Services & solutions      Support & downloads      My account     

developerWorks  >  Lotus  >  Forums & community  >  Best Practice Makes Perfect

Best Practice Makes Perfect

A collaboration with Domino developers about how to do it and how to get it right in Domino

Executive Summary: NoCache is an easy way to make sure your lookup data is up to date; however, it's expensive and people tend to use it without thinking about exactly how up to date the data needs to be. Here are some thoughts on the matter and techniques for finding the right balance between latency and performance and controlling caching precisely.

I see a lot of forms that are slow because the NoCache option has been used too enthusiastically in @DbLookup and @DbColumn functions. Beginning developers often use this option indiscriminately because they think, "Of course it should be up to date," or because it's an easy way to solve a problem in their application (just composed a document and it's not showing up in the keyword lookup), not realizing the performance penalty as the application accumulates more data, or not being aware of the alternatives. So cumulative days of users' time are wasted waiting for a lookup of information that hasn't changed in eons. The problem is compounded by overuse of the form option "Automatically refresh fields," which in most cases is unnecessary and annoying (because it triggers input validation formulas, forcing users to enter information in a particular order).

Often, the user pain caused by poor performance is far greater than that caused by use of cached data. I'm not saying it's never the other way around; just that it's worth thinking about the cost/benefit picture and alternatives instead of automatically coding NoCache.

If it's a keyword lookup document you're getting data from, it's usually best to simply leave the NoCache out or, even better, use a profile document instead. Most keyword list edits are additions, so it's not likely users with cached data are going to be entering invalid values (and again, if they do, just how serious a problem is it -- considering old values may still come in from local replicas for weeks afterwards, anyway?). The form you use to edit a keyword list -- whether it's a profile or a regular document -- can have static text on it to remind the administrator that users need to close and reopen the application before they see the new values (in case they have a user on the phone waiting for them to add a value so they can immediately make use of it).

But, the lookup usually isn't to a configuration document -- often it's to live data, and that's more volatile. Suppose you want a list of customer names as lookup choices; these are found by using @DbColumn in a view of Customer documents. If the user adds a Customer document, they need to then be able to create more documents about that customer immediately -- not have to exit and reopen the application first. Is there any way to make the lookup list be up to date after someone has just added a new customer, that doesn't also require us to scan the whole view in the 99.3% of cases that they have not just added a customer?

First, it's nice if we can only look up data when we actually need it for something. If you're just reading a document, you don't need the list of choices for some keyword field types (comboboxes that allow values not in list, and dialog list fields, in both cases provided keyword synonyms are not used). Even if you're editing a document, it's nice to defer looking up the values until you actually put focus in the field. The following formula lets you do that:

_t := @If(@IsDocBeingEdited; @DbColumn("":"NoCache"; ""; "Customers"; 1); @Return(@Unavailable));
@If(@IsError(_t); @Text(_t) + "|"; _t)

This is good enough for many cases; if you don't have to refresh the choices, at least you only have to look up the value once per editing session. But not every field is like that; computed fields and keyword lists that have to change during editing, if you have to use NoCache you'll be doing it on every refresh. In most cases, it's enough that the lookup result is up to date as of when the document was opened or composed. How can we do that?

Suppose we have a keyword field, CustomerContact, that depends on another field, Customer, as a key to look up its choices. Customer is set to "refresh on keyword change," CustomerContact is set to "refresh choices on document refresh" and its keyword formula is:

@DbLookup("":"NoCache"; ""; "ContactsByCust"; Customer; 7; [FailSilent])

Problem: this does a lookup even if Customer is blank, which is a waste of time, especially when the document is being composed. An @If would take care of that case.

@If(Customer = ""; ""; @DbLookup("":"NoCache"; ""; "ContactsByCust"; Customer; 7; [FailSilent]))

But even with this, the NoCache lookup occurs repeatedly as we edit the form and trigger other refreshes, even though we might be looking up the same key again and again.

For instance, we compose a document -- Customer is initially blank. We select a value for Customer. This causes a refresh, so the keyword formula for CustomerContact executes. Then we change another field, which also causes a refresh. We mustn't use the cache for the first lookup, because the cached value might be out of date. But it's a waste of time to not use the cache on the second calculation of this same formula with the same key. So we shouldn't use NoCache, and we shouldn't not use it. Is there a middle road?

This is the sort of situation the ReCache option was meant for. We can cache the result the first time it's looked up in a particular editing session, and use the cached result thereafter.

The difference between NoCache and ReCache is a bit subtle. NoCache ignores the lookup cache altogether, and if the same lookup was already cached, the cache isn't updated. ReCache doesn't use the cached result, but it does store its result in the cache when it's done. If you later do the same lookup with default caching, you get the result that was stored by ReCache. For example, if document "B" has just been created, and "A" was in the lookup cache from before, you get:

@Statusbar("1: " + @Implode(@DbColumn(""; ""; "Weebles"; 2); ", "));
@Statusbar("2: " + @Implode(@DbColumn("":"NoCache"; ""; "Weebles"; 2); ", "));

@Statusbar("3: " + @Implode(@DbColumn(""; ""; "Weebles"; 2); ", "))
@Statusbar("1: " + @Implode(@DbColumn(""; ""; "Weebles"; 2); ", "));
@Statusbar("2: " + @Implode(@DbColumn("":"ReCache"; ""; "Weebles"; 2); ", "));

@Statusbar("3: " + @Implode(@DbColumn(""; ""; "Weebles"; 2); ", "))
output:
1: A

2: A, B

3: A
output:
1: A

2: A, B

3: A, B

So, to refresh the contacts lookup only when a Customer value is first selected, add a hidden Computed for Display field, CustomerWas, after the Customer and CustomerContact fields, with the formula Customer. Then, our keyword formula becomes:

@If(@IsDocBeingEdited & Customer != ""; ""; @Return(@Unavailable));
_cache := @If(Customer = CustomerWas; ""; "ReCache");
@DbLookup("" : _cache; ""; "ContactsByCust"; Customer; 7; [FailSilent])

The formulas are a little more complex, but this is more than made up for by not having to do a fresh lookup -- assuming that additional refreshes are likely during editing.

Further efficiencies are possible depending on your knowledge of how the application is used. Suppose you analyze use cases and discover that only the user who added a contact really needs to be able to select that contact right away. For all other users, it's OK to use default caching for this lookup. How can we set things up so that the cached value of a Customer key lookup is invalidated only when the current user adds or edits a contact record for that customer?

Once again, it's ReCache to the rescue. In this case, knowing that someone editing a Contact document is likely to be wanting to select that contact from another form in the near future, we could write the following in the Postsave event of the CustContact form:

@DbLookup("":"ReCache"; ""; "ContactsByCust"; Customer; 7; [FailSilent])

(assuming there's a Customer field on this form). The keyword formula for the CustomerContact field on other forms now can be written:

REM {CustContact form Postsave recaches this lookup};
@If(@IsDocBeingEdited & Customer != ""; @DbLookup(""; ""; "ContactsByCust"; Customer; 7; [FailSilent]); @Unavailable)

The downside to this is that the designs of your forms are now more tightly coupled; each form has to know something about how other forms are likely to look up its values -- hence the comment to alert later developers who maintain the design. Also, since each database you have open keeps its own lookup cache, this only works with lookups in the same database. But in cases where performance is a critical problem and there are internal lookups, this is a good option to optimize your use of the cache. You pay for better performance by impairing maintainability -- not always a good tradeoff unless performance is seriously bad.

One last thing I see often that bugs me: Never use @Unique on the results of an @DbColumn or @DbLookup. Instead, look up from a categorized view so that the values are already unique. Why scan and transmit from the server thousands of row entries only to throw most of them away, when you really just needed the six unique values that the view code can read in a tiny fraction of the time by scanning the top-level category entries?

Andre Guirard | 3 December 2007 11:45:00 PM ET | Munkabean’s Coffee, Hopkins, MN, USA | Comments (17)

Search this blog 

Disclaimer 

    About IBM Privacy Contact