Write quick search for new code in IBM Lotus Notes and Domino

Raphael Savir, main developer, ls development configurationraphael Savir is the main developer of LS Development Corporation (http://www.lsdevelopment.com) and has been committed to developing notes/Domino applications since the beginning of 1990s Program And analyze application performance issues. He gave speeches on these topics at lotusphere and several other conferences.

 

Introduction:Read quick search in IBM Lotus Notes and DominoCode11 tips. The author examines @ dblookup @ formula in Lotus Notes and Domino and describes some new techniques, it is used by developers when preparing new applications or performing troubleshooting of performance problems of existing applications.

 

This article will examine @ dblookup, which may be the most popular @ formula in IBM Lotus Notes and Domino. Currently, Lotus Notes/Domino application developers may not be able to create applications without using this formula. Over 15 years of performance testing and customer troubleshooting have shown that: in a form of an application, this formula is often used dozens of times in multiple forms.

However, the same experience shows that performance problems are often related to these @ dblookup formulas. As we have seen, complex enterprise applications use these formulas, resulting in unacceptable low performance and getting stuck to the actual pause.

This article describes 11 tips that can help accelerate the running of almost any application. These skills are widely used, from a simple line of code to a fundamental change in the @ dblookup formula processing method, but all these skills have been tested for a long time.

This article assumes that you have some knowledge of Lotus Notes/Domino development, so some basic parameters such as the @ dblookup formula will not be described in detail.

This article uses various examples to illustrate the value of each technique. For simplicity, we use a help desk application that contains a contact form and a ticket form. The basic workflow of this application is: the customer calls and creates the ticket document. Next, obtain the customer's company and name information and select the appropriate company name and contact name from the drop-down list. Select an appropriate call type (such as product help and sales ). The following examples show how to use the @ dblookup formula in these cases:

• Select a company name from a list containing all companies
• Select a customer name from a list containing all contacts of the company
• Select a problem category from a predefined or dynamic list of problem categories

Capture errors

We do not want to capture errors, but it is counterproductive. In the @ dblookup formula (in this article, @ dblookup refers to both @ dblookup and @ dbcolumn formula), errors are often more difficult to handle, because the formula is often not only dependent on the key entered by the user, it also depends on the returned data. That is to say, formulas may work on a certain day, but cannot be used on another day-this has always been a problem for developers.

For example, assume that company names are written in three different ways in different contact documents (for example, lsdevelopment Corporation and LS Development Corporation ). If you want to find the names of all contacts for the company, but type "lsdevelopment company" (for example), no matching names will be found.

There are multiple methods for more accurate input keys (such as drop-down lists), but for ease of discussion, assuming that @ dblookup returns an error even if strict development techniques are used.

The basic method for error check is to compile the code shown in Listing 1:

Listing 1. Error Check

V: = @ dblookup ("Notes"; ""; "(lookup-contactsbycompany)"; companyName; "contactname"); @ if (@ iserror (v ); "The program cannot find any contacts for this company"; v)

This code is used to perform the search and check the error conditions. If an error exists, a text string is returned, indicating that a problem exists. Otherwise, the obtained value is returned.

This is unrelated to performance, but it is a basic problem that many applications have. Therefore, it cannot be ignored. You can think of it as an additional technique.

Back to Top

Minimize search frequency

Consider the following formula code:

@ If (@ iserror (@ dbcolumn ("Notes"; ""; "(lookup-Companies)"; 1); "there are no
Company Names "; @ dbcolumn (" notes ";" (lookup-Companies) "; 1 ))

This is a common error, because the query is executed twice, which is a problem in terms of performance. You may think that the first query caches the search result, so the second query will not be executed, but this is not the case. The second query is faster than the first query because the latter caches the query results, while the former is executed as is. Because there is no functional reason to write formula code like that, you should write the following code as an alternative:

V: = @ dbcolumn ("Notes"; ""; "(lookup-Companies)"; 1 );
@ If (@ iserror (V); "there are no company names"; v)

Another error that causes unnecessary search execution is: for example, a computed field calculates the search formula in the following scenarios:

• First document creation
• Press various buttons to refresh the document in edit mode
• Save document
• Open the document in Read mode at a later date
• Open the document in edit mode at a later date
• Save the document on a later date

The most common is that you want to perform a search only when you create a search for the first time. In this case, set the field to computed when composed to prevent re-calculation.

In other cases, you may want to perform a search every time you open the document 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 where the formula is located. If the document is not loaded in edit mode, the formula will only keep this value and stop the execution (see figure 1 ). Otherwise, @ dbcolumn will be executed as before.

Figure 1. Keyword Formula

Back to Top

Use cache correctly

Another common error is the misunderstanding of nocache parameter usage. Generally, the incorrect reasoning is that the more important the data is, the more nocache should be used. In fact, the correct idea should be: the frequency of search execution is related to the frequency of data changes.

For example, if the data to be searched changes frequently, for example, once a month, it is hard to imagine that nocache is needed in the search formula. In the example, we assume that some categories are created for reference by the ticket document. The list may be maintained by the owner of the application and updated every several months (maybe when a new product is released or a new company is entered.

On the other hand, assume that the ticket document looks for the name of the customer who sent the call. If the customer sends two calls within one hour, it will be inconvenient because the customer name cannot be obtained in the search on the second ticket, this is because the name list is cached in the previous search. In such applications, we often see help desk staff driving the database all day, so it is easier to cache the cached search for a long time on the day.

Back to Top

Avoid drop-down list traps

For many years, the drop-down list is particularly prominent in all the serious performance problems in the form. There are two reasons: the drop-down list usually looks for a large list, the drop-down list is also calculated in Read mode.

You can perform a simple test on your application. Open a document with slow form execution speed in Read mode. When you open the document, carefully observe the screen, find its pause, and mark it. This operation may not be easy to process, because you only have one second of operation time, and then the screen jumps. However, if you can ask someone for help, one of them can call the field tag, at the same time, another person writes down these labels. Open the form in IBM Lotus Domino designer and view the marked content. Most likely, you will find that the tag is located in the drop-down list using the @ dblookup formula. What's going on?

When you open a document in Read mode, all @ dblookup formulas are checked. Because the document is in Read mode, the actual test values are not returned, but they still finish the process, which is time consuming. The drop-down list is even worse, because the keyword field is filled by the value selected by the user to prevent the user from switching to the editing mode. You may think that you can wait until the user switches to the editing mode and then ask the user to make a choice, but this is not the case in Lotus Notes. Interestingly, the drop-down list in a web browser works just as it does, even on the same form.

A good trick to clear the drop-down list is to combine three different features to prevent unnecessary searches. These features are easy to operate, but you must use them in combination.

First, use the following formula in the formula of the drop-down list field:

@ If (@ isdocbeingedited; ""; @ return (fieldname ));
V: = @ dbcolumn ("Notes"; ""; "(lookup-Companies)"; 1 );
@ If (@ iserror (V); "there are no company names"; v)

This is similar to the content described in "minimize search frequency" and prevents the keyword drop-down list from performing search when the document is in Read mode. If you open a document in edit mode, the drop-down list will be calculated normally. Now we must make sure that the search will be calculated when the user switches from read mode to edit mode. To this end, we will continue to perform the following two steps.

Enable the "Refresh choices on document refresh" attribute in the properties drop-down field (see figure 2 ). If you switch from read mode to edit mode, this attribute allows the keyword drop-down formula to be recalculated when the document is forcibly refreshed.

Figure 2. Properties of the drop-down field

Finally, the form postmodechange event (see figure 3) contains 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

Back to Top

Use the button and select list instead of the drop-down list

In some cases, there are many large and frequently used drop-down lists. The only reasonable solution is to stop using these drop-down lists on the main form. The correct idea should be to consider the following:

• How many times will the document be read during the term of use?
• How many times will the document be edited?
• How many times does this search need to be performed among the document edits?

The answer is often similar to: "A document is read 10 times, but edited once or twice. The drop-down lists are only used once during these edits ." Of course, the answer always varies with the document used, but these are some good questions worth thinking about. If you need to edit documents frequently, but do not need to perform searches, the first part will not be satisfied, because the list will be re-computed every time the user is in editing mode, this is true even if these lists are used at least once. In this case, you can consider using buttons, which may need to be used with the selection list.

The advantage of using buttons is that you can delete search formulas from fields. A field is no longer a drop-down list, but a common text field. It may be a computed when composed field with the fieldname formula (the problemcategory formula is used in the following example ). To avoid confusion, the button is hidden in Read mode, but when you click the button in edit mode, it uses the formula shown in Listing 2:

Listing 2. Button Formula

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;

By using @ picklist instead of @ prompt and @ dblookup, You can further streamline the performance. Its disadvantage is that it will reduce the control on the pop-up window layout and underlying data. For example, view the code shown in listing 3.

Listing 3. Use @ 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;

Back to Top

Use the layout area (or pop-up window) instead of the drop-down list

Because the previous part discussed the search size and frequency, it seems that the correct processing method of the application has been found, but what if the necessary control is not available, the answer is to consider using the dialog box. You can use the @ formula language or LotusScript to pop up a dialog box and use multiple searches in a dialog box. This is often a good solution when forms have multiple related searches. For example, after a user selects a company, the user should obtain a list of contacts of the company. A drop-down list may also contain the open tickets topic line of the contact. In this case, you may want to have a button (as in the previous section), but it should be used with the code shown in Listing 4.

List 4. Use Dialog Box

Dim w as notesuiworkspaceflag = W. dialogbox ("(dialog-companylookup)", true, true, false, true, false, false, "company name", Doc, false)

Each parameter is not described in detail here, but listing 5 shows the developer help reference.

List 5. Help reference

Flag = notesuiworkspace. dialogbox (Form $, [autohorzfit], [autovertfit], [nocancel], [nonewfields], [nofieldupdate], [readonly], [Title $], [notesdocument], [sizetotable], [nookcancel], [okcancelatbottom])

In the dialog-companylookup form, you can insert the layout area and put all drop-down fields without considering @ isdocbeingedited. You can click the button to wait for the search to be executed in the pop-up dialog box.

Other ideas about using the dialog box:

• Sometimes it is helpful to create a temporary Notes document. You do not need to save the document, but it can be used as the [notesdocument] parameter in the previous code example. This allows you to use LotusScript to perform more precise searches and create a list in the dialog box. For example, you may want to reference only the companies you have contacted in the last three months.
• Perform a verification check in the form of the dialog box. Therefore, the user can return to the main document only after the user data is verified. This process may be convenient. This reduces the trouble that users must return to the dialog box.
• You can add images, help text, and more to make a dialog box more attractive and versatile than a simple drop-down list or @ prompt box.
• If you port an application to the Web, you can easily use the Javascript pop-up window to replace the dialog box. The layout area itself cannot be displayed on the browser, but you can use the <div> tag or table to simulate the layout (<div> tag is an HTML code, used to place a piece of content anywhere on the page ).
• Finally, you can use nested tables and [sizetotable] parameters to make them more attractive in the Notes client. This form is easier to transplant to the Web.

Back to Top

Use @ eval to clear code

We sometimes see the @ formula code in a large segment, which is filled with the @ If statement using the @ dblookup formula. This code is difficult to maintain, and it is more difficult to avoid unnecessary searches and perform appropriate error checks (see "capture errors ). Use @ eval to avoid such problems, as shown in Listing 6.

Listing 6. Use @ eval

Companylookup :={ @ dbcolumn ("Notes"; ""; "(lookup-Companies)"; 1) ;}; contactlookup :={@ dblookup ("Notes "; ""; "(lookup-contactsbycompany)"; companyName; "contactname") ;}; @ if (somecondition = 1; @ eval (companylookup); @ eval (contactlookup ))

This code prevents searching before the @ If statement. In other words, you can set all the searches in advance, provide them with appropriate variable names, and then call these searches using @ eval as needed.

Back to Top

Obtain multiple fields with one query

If you find that you need to search for the same document multiple times for different data points, you can consider connecting the data to a column in the search view. For example, assume that we need to obtain the following data points from a document:

• Contactname
• Contactphone
• Contactaddress1
• Contactaddress2
• Contactcity
• Contactstate
• Contactzip

Therefore, we can use seven fields (each field performs a search. The contactname field formula is similar to the code shown in listing 7.

Listing 7. contactname field formula

V: = @ dblookup ("Notes"; ""; "(lookup-contactsbycompany)"; companyName; "contactname"); @ if (@ iserror (v ); @ return ("the program cocould not locate that document. "); v );

The contactphone field formula is similar to the code shown in listing 8.

Listing 8. contactphone field formula

V: = @ dblookup ("Notes"; ""; "(lookup-contactsbycompany)"; companyName; "contactphone"); @ if (@ iserror (v ); @ return ("the program cocould not locate that document. "); v );

And so on.

However, if you can perform one rather than seven searches, you can achieve better performance, even if you perform seven searches for the same document in the same view. There is one way to achieve this: Set the lookup view to include another column with the formula shown in listing 9.

Listing 9. Set the search view to include another 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, set a hidden field in the form -- biglookup -- and put the formula shown in listing 10 into biglookup:

Listing 10. Find contactsbycompany

V: = @ dblookup ("Notes"; ""; "(lookup-contactsbycompany)"; companyName; 2); @ if (@ iserror (v ); @ return ("the program cocould not locate that document. "); v );

For the contactname field, the formula may be shown in listing 11.

Listing 11. Search for contactname

TV: = @ explode (biglookup; "~"); V: = TV [1]; @ If (@ iserror (V); @ return ("the program cocould not locate that document."); V = "Na"; ""; v );

For contactphone, view the code shown in listing 12.

Listing 12. Find contactphone

TV: = @ explode (biglookup; "~"); V: = TV [2]; @ If (@ iserror (V); @ return ("the program cocould not locate that document."); V = "Na"; ""; v );

And so on.

Note:

• In the View column formula, we replace "Na" with "". If this is not done, @ explode will destroy all blank values and generate a list of less than seven values, this will cause problems.
• Replace "" with "Na" in each field formula, but the processing of error messages is entirely dependent on your judgment.

Back to Top

Create a faster search View

The performance of any kind of query view depends on the size and running speed of the view, whether @ formula or Lotus script is used. We have compiled some tips for optimizing view search performance.

Create a dedicated search View

It takes about 100 ms to index (refresh) a typical view in a production environment. That is to say, every 15 minutes, the server will spend less than a tenth of the second to update each query view. Of course, these time values are approximate, and there is a big difference between each application and the server, but years of research into this data has convinced us that even adding several lookup views has little impact on performance. Adding a dedicated search view allows you to streamline the view performance when you wait for the search to complete.

Streamline View Design

If a view is only used to find data from some documents, you can remove the following content from the View Design:

• All interesting fonts and colors.
• All action bar buttons.
• View any Lotus script code in the event.
• Any reference to the profile document.
• Unrelated columns. If you want to return data from these columns, the column is retained; otherwise, the column is eliminated.
• Multiple sorting options of view columns. For example, if you never access a view, you do not need to sort it in ascending or descending order. All you do is increase the size of the view.
• Restrict the reader Name field in the selection list returned by @ dblookup. These fields have a great impact on performance.

You can also convert a category into a sequence, which provides the same search function but allows the view to index more quickly.

Streamline data

In addition to minimizing the design, you can also consider how to minimize data in the view. The following are some suggestions:

• If possible, archive the data as much as possible. The design considerations for enterprises are generally better than those described in this article, but data can often be archived to another Notes database. Users can access the database on a read-only basis (for example ). This can speed up the running of many functions in the primary database. Of course, it also limits the data in the view to make it run faster.
• Carefully optimize the view and select the formula to display only the actual data.
• Make sure that some response documents that are not displayed but still need to be included in the View index are not selected. Select form = "Main" | @ isresponsedoc may inadvertently achieve this. In turn, use @ docdescendants to ensure that only the displayed Response document is included.
• As an example of removing unnecessary data, we can see some customer applications where the query view only uses the data of the past 30 days, but the database must save the data for a long time, for example, two years. The use of time-sensitive or date-sensitive formulas in this view is obviously inappropriate (too slow), but it may be feasible to use the weekly agent to set tags in the document, use this agent to only mark documents that have been created for more than 30 days. Now, you can select a formula to reference only this tag to exclude a large number of documents, which reduces the view running speed.
• Eliminate any data display that is not used for searching.

Use "generate unique keys in index" to remove duplicate entries

This is a good technique to greatly reduce the size of some views. This feature can be used if a large number of duplicate values are obtained and these values are subsequently eliminated using @ unique (or equivalent LotusScript. In this case, the "generate unique keys in index" option enables the View index to display only the first instance of the found text string (see figure 4 ).

However, if the sorting column references a multi-value field, be careful when using this option. In this case, if the Domino server is 6.x, @ implode (multivaluefield; "~") may be used. Then let the search formula use @ unique (@ explode (@ dbcolumn (); "~") To obtain the real unique value set. If you are using Lotus Domino 7, it does not matter because the server can intelligently display all unique values. Note: When a view uses this feature, the indexing speed is slightly slower, because the server may do more work than usual to refresh the view. Do not use this feature unless it can greatly reduce the number of documents displayed in the view. In many applications, you can use this feature to reduce the size of a view to 1/100. In this case, this feature is remarkable.

Figure 4. View Properties dialog box

Back to Top

Use profile document

If you have a list that is not updated by multiple users and is not updated frequently, a quick way to retrieve these values is to store them in the profile document. Whether you manually update the list in the profile document, or manually update the list in the general document, and then migrate it to the profile document, compared with the cache view, you can cache profile documents more effectively, so you can perform repeated searches more quickly.

However, if the user is updating the list, it may be inappropriate to use the profile document because multiple updates may overwrite each other and cause a copy/save conflict.

Back to Top

Use search database

Large applications can often use a separate database query. It may be based on the following Theory: There are a lot of data to be searched, so it is easier to obtain data from the master database and the required view. Although such a statement has certain conceptual logic, in reality, doing so often outweighs the loss. The following tips help you determine when to store the search data in a separate database and when to store the data in the primary database:

• If multiple applications access the same list, it is appropriate to store these lists using a separate database.
• If you want to find a large amount of data and the data requires multiple forms, it is more appropriate to obtain data from the primary user database.
• On the other hand, if you use a single form to search for data and only a few hundred documents, the impact on the primary database is negligible. Based on the advantages of maintenance and performance, we encourage you to save your search in the primary database.

One exception in all these cases is that performance may be very important in some cases and multiple databases can access several brief lists. In this case, you may need to save these lists in a separate search database, but port them to each primary database through the lotus script proxy (or Lotus enterprise Integrator. It may be a little difficult to maintain, but the best performance can be achieved.

Back to Top

Conclusion

We hope that these skills will provide you with some new tools available when you write the next application or conduct troubleshooting of performance problems in existing applications. Dynamic lookup is an important part of most applications, so using them to minimize their performance impact is the best way to ensure that high-quality programs can run smoothly for years.

References

Learning

• For more information, see the original article on the developerworks global site.

• ReadArticle"Using and understanding reader names fields in IBM Lotus Notes and domino ".
• Read the article "Lotus Notes/Domino 7 Application Performance: Part 1: optimizing the database view ".
• Read the article "Lotus Notes/Domino 7 Application Performance: Part 1: Database attributes and document set ".
• Read Lotus Support technical notes "performance and usability issues when using LotusScript vs. @ formulas ".
• IBM Lotus Notes/Domino product page.
• IBM Lotus Notes product documentation.

Discussion

• Participate in Forum discussions.

• Participate in the developerworks Lotus team blog.

About the author

Raphael Savir is the main developer of LS Development Corporation (http://www.lsdevelopment.com, since the beginning of 1990s, we have been committed to developing notes/Domino applications and analyzing application performance issues. He gave speeches on these topics at lotusphere and several other conferences.