Performance-tuning Lotus Notes applications is more an art than a science,
and there are probably hundreds of causes for slowness in a database. However, when you're looking at an application that's not performing well there are a handful of common places to look first. Here are 10 things that seem to come up quite often.- NotesView.AutoUpdate. Opening a NotesView and stepping through the documents to find or gather information is an incredibly common task. One of the best things you can do to improve the speed of walking the view, especially if you are modifying or deleting documents, is to set the NotesView.AutoUpdate property to False after you open the view and before you start getting documents. For example:
Set view = db.GetView("MySpecialView")view.AutoUpdate = FalseSet doc = view.GetFirstDocument - Use NotesViewEntry for view lookups. Using NotesViewEntry and NotesViewEntryCollection can be significantly faster than using NotesDocuments to walk through views or do lookups. This is because you are accessing the view index directly (already built) instead of having to access each document individually. If you're looking for specific information about each document (like field data), you will only see the real speed improvements if you use NotesViewEntry.ColumnValues instead of getting the Document object and looking up the field from there.
- Cache your NotesView references in code. While we're on the subject of NotesViews, another thing you should do is cache any NotesView objects that you need to use more than once, rather than opening the same view multiple times. Opening a database and/or a view takes a very long [relative] time, so you only want to do it once per agent/script library/form. Pass the NotesView by reference to functions, or use global variables if you must.
- Watch out for excessive string concatenations or array building. String concatenations only get to be a burden if you're doing thousands and thousands of concatenations. If you ARE doing that many, the process slows significantly. I posted some performance comparisons a few years ago on If you're building arrays, fixed size arrays are fastest, dynamic arrays are slightly slower (use ReDim as little as possible), and ArrayAppend can be extremely slow if you use it in a loop. You should also consider using lists instead of arrays in many circumstances.
- Only use Readers fields if you really have to. Readers fields are a very convenient way to lock down access to documents in a database. However, they can cause a database to respond very slowly when you open a view (Notes client or Web). This is because the view has to check whether you have access to a document before it can display it in the view, which is much less efficient than just reading through the view index. Here's an it's pretty old but describes the problem (and some possible solutions).
- Check your view indexes. You can easily check the size of all the view indexes in a database by issuing a "show database [dbname]" command at the server console, or if you don't have access to the console, the server's log.nsf file should have that information in the database usage documents. In general, the larger the view index, the slower the view will be to open, re-index, and use programmatically. Index size and speed is a reflection of how many columns in the view are indexed, how efficient the view selection formula is, how complex the column formulas are (especially for indexed columns, although they all play a part), and how many columns there are. Interestingly, recently found that using @IsResponseDoc instead of @AllDescendents in a view formula made the index over 150 times bigger!@Now and @Today are big performance drains when used in view selection and column formulas too, but you probably knew that already.
- Excessive @DbLookups on forms. I still see this problem all the time: forms that use the same @DbLookup in multiple places, or forms that have dozens of @DbLookups (often with the NoCache flag set). Poor use of @DbLookups can cause forms to take forever to open. You should reuse lookup results whenever possible, cache the lookups when it makes sense, and consolidate lookups that are using the same view by creating a column with all the lookup values as a delimited string. Here are some articles and information to get you started:
- Excessive Form/Page refreshes. There is a Notes Form property for "Automatically refresh fields." You almost NEVER want this to be set. This will recalculate all the fields on the form whenever a field value changes, which normally happens quite a bit. I've seen forms with a lot of field calculations and lookups that were almost unusable because the form keeps refreshing going from field to field. Refreshes can also be triggered by code in field events or the field property for "Refresh fields on keyword change" that can be set for some field types. Sometimes refreshing the form fields is good, but use it only when you have to. Also keep in mind that if you are using a NotesUIDocument object, the AutoReload property defaults to "True." You can set it to "False" if you don't need it.On the Web, try to do validation and calculations using Javascript when you can. XPages also have some nice "partial refresh" options.
- Tune up your ODBC queries. Sometimes it's not your database that's slow, it's the interaction with other systems. If you're doing ODBC queries against external data sources (using LS:DO, LEI, ADO, or JDBC), check how long it takes just to do the SQL lookup in the first place. If the SQL query is overly broad or complex, you might be able to refine it or work with your DBA to create a special lookup table. Also, check the fetch batch size and result caching options of your ODBC connections to make sure you're fetching more than one row at a time when stepping through a result set.
- Smaller is better. Simplify your databases. Prefer smaller forms — fewer fields, fewer and less complex tables, fewer subforms, fewer hide-when formulas — to larger ones. Prefer two or three small views to one large one. Remove views you don't use. Make sure your lookup views have the smallest number of computations, columns, and indexes as possible. Don't maintain unread marks or a full-text index on a database if you don't need them. Archive old documents to another database. If you have a lot of frequently deleted documents due to daily data refreshes or similar processes, consider purging the deletion stubs more often than the default (search the Lotus technotes or forums for "purge interval" to find out how).
For ideas on other ways you can performance-tune your Notes applications,
here are a few places to start looking: