It's been a while since I last posted a Show-N-Tell Thursday item (and, yes, I know today's not Thursday but, well, whatever). Jim Sanders, one of our guys, came up with some really interesting numbers related to what is the fastest way to do custom searching of a Notes database with LotusScript. Below are Jim's findings...enjoy:

Recently a client has had the need to do a custom search routine in an existing Notes database. You've seen the kind where you build a dialog box which allows a user to choose various conditions they want to search for. Typically it runs something like this:

1. Prompt the user for what they want to retrieve.
   
2. Run the search using either the database Search or FTSearch methods.
   
3. Display the results.

Not a big deal until you need to also add on a custom export (say through Excel) in which you need to organize this lump of data into some criteria that has been predefined by the client. We all know that Search and FTSearch return (essentially) an unordered document collection. There are numerous ways of doing that kind of sorting. Heck, Teamwork has one built into a standard script library (TSFormulas) that we nearly always use. This library contains both our own document collection sorting routine as well as a lot of @Formula code re-written into script and some other cool stuff thrown in for good measure.

However, since we couldn't use that TSFormulas script library we were stuck coming up with a way to do this sort. The senior developer at the client was interested in using a sorted document collection object he'd pulled off the net a while back. My idea was to dump the entire document collection into a folder (using the handy little PutAllInFolder method of the DocumentCollection object) and let the folder do the sorting for us. Since we're interested in keeping performance as fast as possible we did a head to head comparison just to see who might come out on top. Using the same database and search criteria (returning 4,981 documents via the search) as our test bed and utilizing the new profiler capability of the R7 client we have a winner.

For the sorted document collection object we have the following results:

Not bad, but it's getting to the point where users will get a little impatient. The folder sort method give us the following:

Better. Still not the greatest performer and we still have to do the Excel export, but not bad for a quick and dirty "custom" sort comparison. The sorted collection object is basically using the same "method" for determining the sort order as the folder sort option. It reads in a view design and sorts the collection according to the columns in the view. Both the folder and the view used in these tests have the same structure... I built my sort folder from the design of the view used by the sort object.

This test didn't go any further than trying to determine which is the faster method of sorting. It's interesting to see that the view indexer (even locally) runs faster than processing a series of document references that are already in memory. We'll probably attempt a similar test in the future where we have the code spin through the entire collection and access each document to see if there is any degradation of the folder's advantage once you start having to get handles to all the documents in the two collections. But, that's waiting for another day.

If you're really curious about the details, go here.

1. Stephen Hood07/25/2006 02:15:52 PM

Scott, make sure you take into account the time it takes to REMOVE the items from the folder to get it ready for the next time it's populated. There isn't a folder.empty so it means getting all entries and using removefromfolder. On large collections I think that can take the bulk of your time vs the putinfolder. Then again maybe I've overlooked something - it was awhile ago that stuff was written.

I just remember wishing there was a Folder.empty that "quickly" zeroed out the items. There was something similar in the old paradox days - one function zeroed out the indexes and one deleted each row. The zero function was instantaneous and the delete was ..well much slower.

2. David Leedy07/26/2006 08:51:37 AM

In the old days of 4.6 and early 5 I used to use Folders as a kind of Dynamic view for an application that had 75 remote users. This started before show single category came to be. Anyway, we started having server crashes all the time. Eventally Lotus flew some people out to see what was going on and they found that putting lots of docs in a folder and removing them overloaded something in their folder structure. The number of users and also replication may have been an additional factor, I forget. I think I re-wrote the app to use show-single-cat.

This has probably been fixed by now but be aware that there once was a problem pumping tons of docs in a folder, removing them and doing it again.

I also seem to recall the remove of the documents took a while..

3. Volker Mannherz07/28/2006 06:17:25 AM

Scott, did you ever tried sorting a DocCollection using den SortedCollection class from bookmark.nsf (and others) ?
I actually have no 7.x Client installed, so profiling would be quite difficult...

4. Tim Crannigan09/07/2006 07:08:12 AM

Scott

Maybe I am missing something, but why not sort them once they are in Excel - that is bound to be quicker. (I assume you are using ole to export to excel)?

Regards