IBM Support

Time/Date views in Notes: What are the options?

Product documentation


Abstract

Introduction One of the great features of Notes is its ability to display information in views, categorized, sorted, totaled, and always available. One kind of view that is particularly useful is the Time/Date view. Time/Date views display information based on today's date. If the time/date formula is in the Selection formula, then only certain (presumably recent) documents display in the view. If the time/date formula is in a column formula, then that column can display different kinds of info

Content

Hide details for Introduction
Introduction

One of the great features of Notes is its ability to display information in views, categorized, sorted, totaled, and always available. One kind of view that is particularly useful is the Time/Date view. Time/Date views display information based on today's date. If the time/date formula is in the Selection formula, then only certain (presumably recent) documents display in the view. If the time/date formula is in a column formula, then that column can display different kinds of information for each document, depending upon the time/date value in each document.

To appreciate the value of Time/Date views, think about the following examples:

A view that displays only Severity 1 problems that have been entered today.
A view that displays only orders placed within the last 7 days.
A view that displays only technotes updated within the last 30 days.
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.

As you might imagine, many applications can gain from having Time/Date views. Unfortunately, these views have historically been a cause of performance degradation for the application and server where they reside. Recent tests conducted indicate that this need not be so. This Technical Paper will examine the various methods of creating a Time/Date view, and the relative advantages and disadvantages of each.
Hide details for How to Create Time/Date Views with Selection Formulas
How to Create Time/Date Views with Selection Formulas

Method: Use @Today in the Selection Formula. Only the most recent documents will display.
Sample Formula: today := @Today;
SELECT @Adjust(Date; 0;0;7;0;0;0) > today
Other Tasks: N/A
Advantages:
    1. This kind of view will always, without a doubt, be up to date.
    2. There are no Other Tasks to perform.
Disadvantages:
    1. When you open the view, there will be a noticeable delay, perhaps 10's of seconds or more, before the view displays.
    2. The yellow question mark (or blue reload symbol) will always display in the top left, encouraging you to click it, or press F9. Doing so will cause a similar delay to the one you experienced when you just opened the view.
    3. Whenever UPDATE or UPDALL runs against the database, this view will need to be completely rebuilt, which takes about the same amount of time as opening the view.

Method: Use @Environment in the Selection Formula. Only the most recent documents will display.
Sample Formula: today := @Environment("Today");
SELECT @Adjust(Date; 0;0;7;0;0;0) > today
Other Tasks:
    1. 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));
    2. The view must be updated once per day, after the environment variable has been set. This can be accomplished using "UPDALL filename -r -t viewname" You can create a program record to perform this view update.
Advantages:
    1. There is no delay when opening the view.
    2. The yellow question mark (or blue reload symbol) appear just as they would for any other view (i.e. if updates occur while the view is open, the symbol flashes).
    3. When UPDATE or UPDALL runs against the database, this view will update very quickly.
Disadvantages:
    1. If the agent fails to run, the view will be out of date.
    2. 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.
    3. 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).
    4. 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).

Method: Use @Text in the Selection Formula. Only the most recent documents will display.
Sample Formula: ddate := @Date(Date);
d := ddate : @Adjust(ddate; 0;0;1;0;0;0) : @Adjust(ddate; 0;0;2;0;0;0) : @Adjust(ddate; 0;0;3;0;0;0) : @Adjust(ddate; 0;0;4;0;0;0) : @Adjust(ddate; 0;0;5;0;0;0) : @Adjust(ddate; 0;0;6;0;0;0);
dt_format := @Text(d; "T1S3");
include_date := @If(@Contains(dt_format; "Today"); @True; @False);
SELECT include_date
Other Tasks:
    1. The view must be updated once per day, after midnight. This can be accomplished using "UPDALL filename -r -t viewname" You can create a program record to perform this view update.
Advantages:
    1. There is no delay when opening the view.
    2. The yellow question mark (or blue reload symbol) appear just as they would for any other view (i.e. if updates occur while the view is open, the symbol flashes).
    3. When UPDATE or UPDALL runs against the database, this view will update very quickly.
    4. You do not have to run an agent every night to update an environment variable.
    5. The view will never simply "not function properly." The worst case scenario is that the view is not up to date. When it is first created, it will be up to date automatically.
Disadvantages:
    1. 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 a user presses SHIFT+F9 while in the view).


Hide details for How to Create Time/Date Views (folders) with Agents
How to Create Time/Date Views (folders) with Agents

Method: Have a computed field which will take on the value "x" whenever the document is saved. All documents with this value will appear in a view of recently modified documents. Run a scheduled agent (daily) to unmark documents which are older than 7 days, so that they do not appear in the view anymore.
Sample Formula: ' The name of the field is Seven.
Sub Initialize
Dim s As New notessession
Dim db As notesdatabase
Set db = s.currentdatabase
Dim collection As notesdocumentcollection
Dim datetime As New notesdatetime ( "1/1/97" )
Set collection = db.search ( {@adjust(Date; 0;0;7;0;0;0) < @Today & Seven = "x"}, datetime, 0 )

Dim doc As notesdocument
Set doc = collection.getfirstdocument

Do While Not(doc Is Nothing)
doc.Seven = ""
call doc.save (true, true)
Loop
End Sub
Other Tasks: N/A
Advantages:
    1. There is no performance hit for users during the day.
    2. There is no need for a program record in the N&A Book.
Disadvantages:
    1. It may not be reasonable to run this agent 'on the fly' during the day. You will have to decide what kinds of views you want to have, and then have one agent per view run every night.
    2. Agent must run every night, and changes must propagate via replication before the first users enter the database in the morning.

Method: Use a scheduled agent (daily) to place documents in a folder.
Sample Formula: Sub Initialize
Dim s As New notessession
Dim db As notesdatabase
Set db = s.currentdatabase
Dim view As notesview
Set view = db.getview ( "7" )
Dim doc As notesdocument
Set doc = view.getfirstdocument

' Clean out folder
Do While Not( doc Is Nothing)
doc.removefromfolder ( "7" )
Set doc = view.getnextdocument (doc)
Loop

' Populate folder
Dim collection As notesdocumentcollection
Dim datetime As New notesdatetime ( "1/1/97" )
Set collection = db.search ( {@adjust(Date; 0;0;7;0;0;0) > @Today}, datetime, 0 )
Call collection.PutAllInFolder( "7" )
End Sub
Other Tasks: N/A
Advantages:
    1. There is no performance hit for users during the day.
    2. There is no need for a program record in the N&A Book.
    3. There are no modifications made to documents (placing a document into a folder makes an update to the folder, not to the document).
    4. You can build a more flexible agent than the sample script above, so that users can specify conditions, and have the folder populated on the fly.
Disadvantages:
    1. Agent must run every night, and changes (to the folder) must propagate via replication before the first users enter the database in the morning.
    2. For this method to work on documents that are saved today, a second agent will be required, which runs against all documents, as they are saved. Or, a QuerySave event can place the document into the appropriate folder(s).


Hide details for Performance Testing
Performance Testing

To test the performance of each method listed above, the following questions were considered:

#1: How long does it take for a user to open the view during normal use?
#2: How long does it take for UPDATE to refresh the view? How often does this occur during normal use?
#3: How long does it take for UPDALL to refresh/rebuild the view? How often does this occur during normal use?

Note that it is only relevant to consider performance characteristics of the "Selection Formula" methods.

To derive answers to these questions, a database was created with 50,000 documents, 31 of which appeared in the test views. 15 - 20 users created several hundred documents in this database during the course of an hour. They also updated several dozen existing documents, and scrolled through the Main view (not listed), and read documents. In addition, there were three replication cycles with another server. And finally, each test view was opened one time manually (this was the only time those views were directly accessed).

DEBUG_NIF was used to produce detailed output regarding the frequency and duration of all NIF activity (refreshing and rebuilding views).

#1 How long does it take to open the view?

Column #2 in the table below shows the time it took to open each view during the test period. In fact, even when there was no user activity on the server, and no documents had been modified or created since the last time the views were refreshed, they consistently took about the same amount of time to open. A stopwatch was used to determine these values.

#2 How long does it take to refresh the view?

Opening a view causes Notes to refresh the view, so it turns out that this is really just rephrasing question #1. Since DEBUG_NIF monitors the time for us, however, we have precise numbers (see column #3 in the table below).

How often do the views get refreshed during normal use?

For details on what causes Notes to refresh a view, see this Technical Paper ---> "The Basics of View Index Refreshing" (#114202 ). Risking oversimplification, the Notes Server will check every database every 15 minutes, looking for views which are out of date. A view is out of date if it contains documents which have been modified (create, modify, delete), or if there is a Time/Date formula in the view (Time/Date formulas always act as thought they are out of date). A Time/Date formula in this context means a formula that contains @Today or @Now. The ENVIRONMENT and TEXT methods described above would not automatically flag the view as being out of date.

Therefore, in the test period of one hour, UPDATE ran a total of five times for each view. Once every fifteen minutes, plus one time for the manual opening of each view (see the beginning of this section for the description of the test).

For your applications, you can count on all Time/Date views being refreshed via UPDATE every 15 minutes during the day (as long as modifications are taking place within the database), as well as being refreshed every time someone opens one of those views.

#3 How long does it take when UPDALL runs against these views?

If UPDALL is run without parameters, then the views are simply being refreshed; it takes the same amount of time as UPDATE.

If UPDALL -r is run against the database, then all the views are rebuilt. See column #4 in the table below for those numbers. Note that for your applications, you would rebuild the Time/Date views using UPDALL -r -t. This will only rebuild the view(s) you specify.

How often does this occur during normal use?

Typically, UPDALL is run at night, when no one is using the application, so these UPDALL numbers are relevant only for planning your nightly tasks.


Test Results

Time/Date views which use @Today are about 100 times slower than the other Time/Date views to refresh (UPDATE or UPDALL). For applications that have at least moderate use, this kind of refresh will occur every 15 minutes, not counting the times it refreshes due to being directly accessed by a user.

The @Today views are significantly faster (5 times) to rebuild (UPDALL -r) than the other Time/Date views, but you will probably only run UPDALL -r once per night, at most.

Each view contains only 31 documents.

View Time to open Time to refresh (UPDATE or UPDALL) Time to rebuild (UPDALL -r)
No Time/Date formula less than 1 second .2 seconds 18 seconds
@Environment less than 1 second .44 seconds 113 seconds
@Text less than 1 second .48 seconds 164 seconds
@Today about 37 seconds 33.47 seconds 31 seconds


Hide details for Conclusion
Conclusion

Time/Date views can be powerful tools, and should not be shunned. However, their performance impact must be taken into consideration, especially for large or heavily used applications.

Time/Date views using @Today are the most convenient, because with no additional work or maintenance, they will always be up to date. Unfortunately, the cost is that they perform so much worse than the other Time/Date methods. It is recommended that customers use this method for applications with less than 1000 documents, and which are minimally used.

For larger or more heavily used applications, consider each of the other methods, and review their advantages and disadvantages. Consider the following issues which may lend more weight to one or another of the methods:
  • Is it easy for you to get agents written, and maintained?
  • Is it easy for you to get program records written, and maintained?
  • Do you have access to the server's Notes.ini?
  • Do you have multiple servers hosting the same application?
  • If so, do they replicate frequently?
  • Do you have a lot of remote (laptop) users?
  • If so, do they replicate nightly?
  • Do they use field level replication?

Related information

The Basics of View Index Refreshing

Cross reference information
Segment Product Component Platform Version Edition
Messaging Applications IBM Domino Designer Database Design Windows 8.5, 8.0, 7.0, 6.5

Product Alias/Synonym

Lotus Notes Client
Lotus Domino Designer Client
Notes Designer for Domino

Document information

More support for: IBM Notes
Views

Software version: 6.5, 7.0, 8.0, 8.5

Operating system(s): Windows, iOS

Reference #: 7003557

Modified date: 03 October 2007


Translate this page: