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 = False Set 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 my
blog.
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 article on developerWorks; 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, Andrew Pollack 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:
http://www-10.lotus.com/ldd/bpmpblog.nsf/dx/caching-strategy?opendocument&comments
http://www-10.lotus.com/ldd/ddwiki.nsf/dx/dblookup-troubleshooting.htm - 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:
Jamie Magee's Performance
Session
Bill
Buchan's Best Practices Sessions