Lotus Notes

Time/Date views in Notes can cause performance problems, yet they are a
very important way to display information to the user.
To appreciate the value of Time/Date views, think about the following
examples:
1 A view that displays only Severity One problems that have been entered
today.
2 A view that displays only orders placed within the last seven days.
3 A view that displays only technotes updated within the last 30 days.
4 A column in a mail file that displays either the time or the date a
message was sent. If the message was sent today, it displays just the
time; otherwise, it displays just the date.
Why do these views cause performance problems? The reason is that the
selection formula or column formula contains @functions such as @Today or
@Now and as such, the formula result is never static. Because of the
changing nature of the @function results, the index needs to be updated
every time the view is accessed. Unless you have a small set of data, this is
going to make the view slow to display.
Several techniques have been developed to allow time/date views to have
good performance, even if they contain a large set of data.

Creating Time/Date Views Using Selection Formulas
Here we describe three ways to create time/date views using selection
formulas, and we identify the pros and cons of each method.
Use @Today in the Selection Formula
This is the easiest solution and is always up to date, but it can have the worst
performance of all the methods. Try to avoid using this type of selection for
large views.
In this example we want to select all documents that have the field DueDate
within seven days of today’s date.
Selection formula
Select all documents with a DueDate within seven days of today.
SELECT @Adjust(DueDate; 0;0;7;0;0;0) > @Today
Advantages
• This kind of view will always be up to date.
• There are no other tasks to perform.
Disadvantages
• When you open the view the index must be rebuilt. If the number of
documents is more than a handful, there will be a noticeable delay,
perhaps many seconds for large views.
• The view refresh symbol will always display in the top left, indicating
(incorrectly) that the view is out of date. This will encourage the user to
click it (or press F9). Doing so will cause a similar delay to the one
experienced when the view was opened.
• Whenever UPDATE or UPDALL runs against the database, this view
will need to be completely rebuilt.
Use @Environment in the Selection Formula
This solution is also simple and has good performance, but requires an
environment variable to be kept up to date.
We use the same example as before: that is, we want to select all documents
that have the field DueDate within seven days of today’s date.
Selection formula
Select all documents with a DueDate within seven days of an environment
value.
SELECT @Adjust(DueDate; 0;0;7;0;0;0) > @Environment("Today")

Other tasks
• You must create a scheduled agent that runs every night, after midnight
to update the value of the environment variable, $Today. This agent
does not have to reside within the database that has the time/date view,
but the agent must run on every server that contains a replica copy of
the database. The formula for the agent can be as simple as:
@SetEnvironment("Today"; @Text(@Today));
• The view index must be rebuilt once per day, after the environment
variable has been set. This can be accomplished using:
UPDALL dbName -r -t viewName
You can create a program record to perform this view update.
Advantages
• There is no delay when opening the view.
• The view refresh symbol behaves normally. That is, the symbol appears
only if updates occur while the view is open.
• When UPDATE or UPDALL runs against the database, the view will
update very quickly.
Disadvantages
• If the agent fails to run, the view will be out of date.
• If you make a replica copy on a new server and forget to create an agent
on this server, the view will not function properly (in most cases, the
selection formula will return an error and the view will display nothing).
In fact, prior to the first time the agent runs, the view will not function
properly.
• If the program record fails to execute, the view will be out-of-date (until
either the server performs the view rebuild, or a user presses SHIFT+F9
while in the view).
• If you make a replica copy on a new server, and forget to create a
program record for that server, the view will correctly display the first
time it is used, but will thereafter be out-of-date (until either the server
performs the view rebuild, or a user presses SHIFT+F9 while in the
view).
Use @Text in the Selection Formula
This third solution is simple, has good performance, and needs no
environment variable.
Again, the same example is used here: we want to select all documents that
have the field DueDate within seven days of today’s date.

Selection formula
Select all documents that contain “Today” in the list of dates within seven
days of DueDate. Use date-to-text formatting that translates today into
“Today”.
ddt := @Date(DueDate);
dwk := ddt : @Adjust(ddt; 0;0;1;0;0;0) : @Adjust(ddt;
0;0;2;0;0;0) :
@Adjust(ddt 0;0;3;0;0;0) : @Adjust(ddt; 0;0;4;0;0;0) :
@Adjust(ddt; 0;0;5;0;0;0) : @Adjust(ddt; 0;0;6;0;0;0);
dwk_fmt := @Text(dwk; "T1S3");
SELECT @If (@Contains(dwk_fmt; "Today"); @True; @False);
Other tasks
• The view must be updated once per day, after midnight. This can be
accomplished using:
UPDALL dbName -r -t viewName
You can create a program record to perform this view update.
Advantages
• There is no delay when opening the view.
• The view refresh symbol behaves normally. That is, the symbol appears
only if updates occur while the view is open.
• When UPDATE or UPDALL runs against the database, the view will
update very quickly.
• You do not have to run an agent every night to update an environment
variable.
• The view will never simply “not function properly.” The worst case
scenario is that the view will not be up to date. When it is first created, it
will be up to date automatically.
Disadvantages
• If the program record fails to execute, or if you make a replica copy on a
new server and forget to create the program record for that server, then
the view will be out-of-date (until either the server performs the view
rebuild, or they press SHIFT+F9 while in the view).

Creating Time/Date Views Using Agents
This alternative to using a time/date selection formula relies on agents
making changes to the documents themselves, or moving the document into
or out of a folder.
Save a DisplayInView Indicator in the Document
This method relies on the fact that every document has a computed field
which will contain a specific value (mark) whenever the document is saved.
All documents with this value will appear in a view of recently modified
documents. A scheduled agent is then run (daily) to unmark documents
which are older than seven days, so that they no longer appear in the view.
Selection formula:
Select all documents with the “DisplayInView” mark set.
SELECT DisplayInView = “1”
Other tasks
• Code is required in the QuerySave event to initialize the DisplayInView
field to “1” during document save. In our example, the setting would be
dependent on the value of DueDate. If DueDate is within seven days of
today, we set the indicator; otherwise we clear it. This QuerySave code
must be in every form that creates documents which will appear in the
view.
• An agent must be run daily to update the DisplayInView field:
'*** Update all ‘DisplayInView’ indicators
Sub Initialize
Dim s As New NotesSession
Dim db As NotesDatabase
Set db = s.CurrentDatabase
Dim dc As NotesDocumentCollection
Dim datetime As New NotesDateTime( "" )
Set dc = db.Search( {@adjust(DueDate; 0;0;7;0;0;0) < _
@Today & DisplayInView = "1"}, datetime, 0 )
Dim doc As NotesDocument
Set doc = dc.GetFirstDocument
While Not doc Is Nothing
doc.DisplayInView = ""
Call doc.save( True, True )
Set doc = dc.GetNextDocument( doc )
Wend
End Sub
Advantages
• There is no delay when opening the view.
• The view refresh symbol behaves normally. That is, the symbol appears
only if updates occur while the view is open.
• When UPDATE or UPDALL runs against the database, the view will
update very quickly.
• There is no need for a program record in the Domino Directory.
Disadvantages
• If the agent is not run, the view will be out-of-date.
• If the database replicates, changes must propagate to other replicas
before the first users enter the database in the morning.
• The database search may be resource intensive for large databases. This
is particularly important if many views require time/date document
updates, either in this database or others on this server.
Put Documents into a Folder
Put documents into a Most Recent folder. Run a scheduled agent (daily) to
remove documents which are older than seven days.
Selection formula
Not applicable. Folders do not have selection formulas. Documents are
moved in and out of folders under program (or user) control.
Other tasks
• Code is required in the QuerySave event to put the document into the
Most Recent folder. In our example, the action would be dependent on
the value of DueDate. If DueDate is within seven days of today, we put
the document into the folder. If not, we remove it from the folder, if
required. This QuerySave code must be in every form that creates
documents which will appear in the folder.
• An agent must be run daily to remove old documents from the folder:
Sub Initialize
Dim s As New NotesSession
Dim db As NotesDatabase
Set db = s.CurrentDatabase
Dim view As NotesView
Set view = db.GetView( "Most Recent" )
Dim doc As NotesDocument
Set doc = view.GetFirstDocument
'*** Clean out the folder
Do While Not doc Is Nothing
doc.RemoveFromFolder( "Most Recent" )
Set doc = view.GetNextDocument( doc )
Loop
'*** Re-populate the folder
Dim dc As NotesDocumentCollection
Dim datetime As New NotesDateTime( "" )
Set dc = db.Search( {@adjust(DueDate; 0;0;7;0;0;0) > _
@Today}, datetime, 0 )
Call dc.PutAllInFolder( "Most Recent" )
End Sub
Advantages
• There is no delay when opening the folder.
• There is no need for a program record in the Domino Directory.
• There are no modifications made to documents (placing a document into
a folder updates the folder, not to the document).
Disadvantages
• If the nightly agent is not run, the folder will be out of date.
• If the database replicates, changes must propagate to other replicas
before the first users enter the database in the morning.
• The database search may consume significant CPU resources for large
databases. This is particularly important if many views require
time/date document updates, either in this database or others for this
server.
• Users may move documents into and out of the folder. There is no ACL
setting to prevent this activity.