Yesterday the @NotesDevTips
twitter account tweeted about not using the ‘click to sort’ option in lookup
views based on a suggestion that I gave them.
The reason for the suggestion was due to what I found when I looked at
the design of one of our inhouse applications that was originally designed
by a Business Partner and then customized for our needs, both internally
and with the BP’s assistance. Users had been complaining about how slow
the application was and as time progressed and more and more data was going
into the system the application got so slow it was starting to become unusable.
What I found was that in quite a lot of the HIDDEN LOOKUP views there were
about 10 columns and all of the columns had the ‘click to sort’ option
enabled to allow sorting in both directions. This was causing the application
to be extreamly slow because the indexer was been given extra unneccessary
work. Every view has an index and every click to sort option is an extra
index for the view, so for a view with 10 columns, all with the click to
sort turned on in both directions this meant that a single view contained
21 indexes, multiply that by the number of views in the database and you
end up with a lot of extra indexes that users would never use.
In our case the application data was taking up about 2GB and the index
for the database was taking up around 4GB. After the changes the index
size dropped to about 500Mb. A few simple changes and we have saved about
3.5Gb of disk space, that alone is a big saving but this application is
replicated to about 10 servers so we have saved about 35Gb across our entire
infrastructure.
Talking to one of the applications power users this morning resulted in
the complaint that they no longer have time to go get a cup of coffee
when opening the application, it is opening within seconds now as opposed
to taking a minute or two previously. Yes, Notes Sucks because I took away
their coffee getting time. Seriously, however, the users have noticed that
the system is now a lot faster then it was previously and they are very
happy. All those minutes of waiting are gone which leads to them being
more productive in the long term.
The moral of the story is
be very careful about how you design your views in Lotus Notes applications.
Design for performance, don;t turn on view options that will tax the indexer
unless they are really needed and test your applications with realistic
amounts of data to catch performance issues.
The next @NotesDevTips
entry will be about never using @Now or @Today in a notes view selection
formula. There were a few of them in the database design also.
Dec's Dom Blog 
If a view has been resorted by a user clicking the ‘click to sort’ option, and then another user runs code containing getdocumentbykey or some variation against that view, the doc or docs returned probably aren’t going to be the ones intended.So even if performance wasn’t a problem, you still don’t want ‘click to sort’ being used in a view used for programmatic lookups.
LikeLike