Well it’s New Year’s Day so the obvious subject that is on everyone’s lips is database full text searching. Isn’t it?
Yesterday I tweeted about having to remove a load (and I mean a load) of database.FTSearch calls from an application I’ve inherited recently. Several people asked why I would want to do that, so I thought I’d explain.
On the face of it using the Full Text Index to find a collection of documents is an ideal way of working, it saves you having to create a suite of views that will only be used by your LotusScript and we all know that less views is a good thing. But, in my view, there is almost no situation when you should use an FTSearch unless you are looking for a word or phrase across *all fields* in *all documents* in your database. If you find you are writing query syntax that uses the FIELD or CONTAINS keywords then you should probably be thinking again about your code design.
The main issue with relying on the Full Text Index is that it is normally out of date. As a rule of thumb you can’t rely on any changes that have happened in the last 30 minutes to show up in your search results. So in my case yesterday, the agents were being run almost immediately after a new document is added to the database and then expecting to find that document and ones related to it. When you’re relying on luck rather than judgement then you know you have a problem.
So, what to do? Generally the answer is to design your database views carefully. You can build a suite of views that can be used across the database that will allow you to build collections of documents that match your requirements. Remember, you can have multiple sorted columns if needed and pass an array as the key parameter in you getAllDocumentsByKey call, or build composite keys in the first column of your views.
In fact I’d go as far as saying that if your document collection isn’t too big that it may be better to build a larger collection than you need and loop through it to find the exact documents you need, rather than performing an FTSearch. The other benefit of using views, of course, is that you can build a NotesViewEntryCollection which will perform a lot better than a document collection and you get the added benefit of being able to use the sorting from the view design rather than having to apply your own quick sort after you’ve built whatever output array you’re trying to get to.
As with all of these types of general design principals, your mileage may vary, if you have a good reason to use the Full Text Index then go ahead and use it. But please make sure you understand what limitations you face when using the index rather than views.