IBM®
Skip to main content
    Country/region select      Terms of use
 
 
   
     Home      Products      Services & solutions      Support & downloads      My account     

developerWorks  >  Lotus  >  Forums & community  >  Best Practice Makes Perfect

Best Practice Makes Perfect

A collaboration with Domino developers about how to do it and how to get it right in Domino

You've decided to follow best practices, and create a separate view to use in a @DbColumn operation. You accept that this will slow the server somewhat and take up more room in your database, as compared to a single view used for both purposes. Can you at least optimize performance and space usage?

My first suggestion might seem a little obvious, but I mention it because it surprised someone I thought would find it "old hat." If you use @Unique to remove duplicate values from a list, that's always a danger signal. @Unique(@DbColumn...) in particular, can be improved on. Executing @Unique on a long list is expensive because it has to compare each element with each other element, with an execution time probably proportional to the square of the number of elements (O(n*log(n)) is possible in theory). Add to that the amount of time it takes @DbColumn to scan all the entries in a view, compile a list of values from a column, and (if using a server database) transmit information over the network to the client, and you'll see there's a lot of wasted time creating a list most of whose elements you're going to throw away anyway.

Far better if you can get a list whose elements are already unique. One way to do this is with a categorized view. Assuming there are far fewer categories than there are documents, it's a lot faster for @DbColumn to just scan through the category headings, compared to a regular view where it has to scan every row. Use @DbColumn to read a categorized column, and you don't need to use @Unique on it after.

One problem, though; categorized views are more expensive for the server to maintain, and their stored index is larger, than the same view without categories. We're only interested in the list of 100 unique values. Isn't there any way to get that without all the overhead of listing 30,000 documents in the index?

Generate unique keys...In fact, there is a way, or I probably wouldn't've brought it up. In the Advanced tab of the view properties, find the option "Generate unique keys in index". If you tick this box, Notes will show only one document per unique key value (the key consists of all the sorted columns). When a document is created or modified, if a document with the same values in the sorted columns is already present in the index, the document will not be added to the view. If there are many duplications, this saves a lot of space (though it's maybe only a little faster to index than a regular sorted view).

Using Domino Administrator, you can see the sizes of view indexes. A view with "normal" sorting, had a size of 1021KB. The same view with the sort column categorized instead, was 1297KB -- a little larger because it has to store the category rows and the document rows. But if instead of categorizing, I set the "unique keys" option, I can get the index size down to 118KB. Your mileage may vary, depending how many documents use the same key.

There are a few things to watch out for with this feature:

  • You can't predict which document will end up in the view, among the ones with the same key. It's not necessarily the oldest, or the newest, or the most recently modified, or any such thing. If you're just reading the unique values off the sorted column, of course, it doesn't matter.
  • If you have any replication/save conflicts, you must exclude them from the view using a selection formula such as SELECT Form = "Something" & @IsUnavailable($CONFLICT). Otherwise, if any of the conflict documents end up in the index, they will not be visible because they are response documents whose parents are not in the view. Setting the view to not display hierarchically should also do the trick.
  • If a document is deleted, you might expect another document with the same key value to take its place in the view. Alas, this does not occur. The view indexer has already considered those existing documents for inclusion in the view, and rejected them because the key was already there. Now that the key is no longer there, Notes doesn't hunt through the already-rejected documents for a replacement. Documents are considered for inclusion only when they are modified or created. So, if in your application the documents you're looking up might be deleted or their keys changed, this isn't an appropriate solution for you -- unless you can arrange to have the index rebuilt each time this occurs. For instance, if the documents are deleted by an archiving agent, have that same agent issue a NotesView.Rebuild against the view, or schedule the index rebuild as a periodic server task timed for after the agent runs. A refresh isn't good enough -- it has to be a rebuild.

Andre Guirard | 27 March 2008 04:40:00 AM ET | Man-Cave, Plymouth, MN, USA | Comments (7)

Search this blog 

Disclaimer 

    About IBM Privacy Contact