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 (8)


 Comments

1) The third thing you might not have known about @DbFunctions
Olivier | 3/27/2008 6:17:32 AM

Hi,

It's a great (and quite hidden) option, but I've noticed that when adding a new document with a new key, it's supposed to appear in the view (a view generating unique keys in index) but it's not the case until, until..well, until some cache process updates it.

And it may take hours to be "refreshed". Shift+ctrl+F9 in the view thru the Notes client does not even "refresh" it. Maybe a "load updall -R" does, I haven't tested out.

But that issue make that option unfortunately not really usable in a production context, where users except to see their updates immediately (for example, if the view is used to populate a combo box).

Your feeling ?

2) The third thing you might not have known about @DbFunctions
Nathan T. Freeman | 3/27/2008 6:49:27 AM

Agreed. This sounds like a really cool edge feature that could be used in some creative ways, until you get to that last bullet point. That's a show-stopper, every bit as much as what happens when you use @Text(@Today) in a view's selection formula.

3) The third thing you might not have known about @DbFunctions
Erik Brooks | 3/27/2008 8:15:45 AM

Here's another show-stopper: If you're using "Show multiple values as separate entries".

Picture 2 docs, with keys as such:

Doc #1: "A"

Doc #2: "A" : "B"

With the ODBC feature off you'll get two categories, "A" and "B".

With it on you might get two categories. Or you might get just one (A). It all depends on which doc the indexer hits first.

Combined with your last bullet point it REALLY gets screwy. If you have one category and delete doc #1 then your view will have NO categories at all when it should have *two*. Doc #2 was discarded.

4) The third thing you might not have known about @DbFunctions
Charles Robinson | 3/27/2008 8:48:16 AM

The following is based on my experience using this for the first time, which was somewhere around 2002. I have no idea what version I was using, so things could be different now.

Back then I learned that when there are rep/save conflicts you'll get "Entry not found in index" from any @DBColumn or @DBLookup that references the view. It will look fine in Designer (the rep/save conflicts will not be visible), but when you open it in the Notes client the view will look empty.

To make the rep/save conflicts visible and the view to work at all you have to disable the "show response documents in a hierarchy" option. That still doesn't cause the view to work as expected, though, because it will snow both the parent and all conflict documents. If your lookup hits a document with a rep/save conflict it will give you "Entry not found in index". Appending @IsUnavailable($CONFLICT) to the selection formula didn't fix it when I was learning how to get this going, either. It may work in a more logical manner now. Back then I was told it was "working as designed".

As I said, it may work differently now. This is another case where once you finally wrap your head around how Notes is behaving you just go with it. It's very much like Bob's idea that Len(strvar) = 0 is faster than strvar = "" or your own firmly held belief that Delete'ing NotesDocuments when using a GetNextDocument loop offered significant benefits. We don't have time to go back and reassess every assumption to figure out whether it still holds true.

5) The third thing you might not have known about @DbFunctions
Nathan T. Freeman | 3/27/2008 10:47:53 AM

"your own firmly held belief that Delete'ing NotesDocuments when using a GetNextDocument loop offered significant benefits"

Actually, if memory serves, Andre pointed it out, Rocky adopted it, then Andre later pointed out that it looked like the caching had been improved so there was no longer a substantial gain to be had.

6) The third thing you might not have known about @DbFunctions
Charles Robinson | 3/27/2008 12:41:03 PM

@5 - I remember this very specifically because Rocky brought it up in his Writing Maintainable Code session at Lotusphere 2007, and I tracked Andre down in the lab and discussed it with him. Rocky said it had been fixed and when I asked Andre I distinctly remember him saying it had not.

Not that any of that matters. The point is that as the behavior of Notes or Domino changes it's difficult to know what you should go back and revisit.

7) The third thing you might not have known about @DbFunctions
Nathan T. Freeman | 3/28/2008 5:57:07 AM

@6 - Last I saw it was an open question. And good to know that it no longer is (although the resolution is less-than-thrilling.) Thanks for that.

And yes, I agree 100% that it's difficult to know and adjust to changes in platform behavior.

8) The third thing you might not have known about @DbFunctions
Werner Götz | 3/29/2008 5:50:05 AM

Maybe it should be noted that similar to the problem with deleted documents there can be a problem with documents with reader names.

If the document used for key "A" in the view has a reader names field and is not visible for some users, these users get no "A" from a @DbColumn even if there are enough other documents with key "A" for which these users have read access.

9) The third thing you might not have known about @DbFunctions
Andre Guirard | 3/29/2008 9:51:24 AM

Werner, good point -- whereas a categorized column can show you all the keys even if there are no documents to which the user has access -- if you choose that option. I guess there are really too many disadvantages to the "unique keys" view option for it to be useful for this purpose.

 Add a Comment
Subject:
   
Name:
Comment:  (No HTML - Links will be converted if prefixed http://)
 
Remember Me?     Cancel

Search this blog 

Disclaimer 

    About IBM Privacy Contact