MoneyWorks

Sometimes you need to do a search that involves a fast component and a very slow component.

Maybe something like this

detail.parentseq = transaction.sequencenumber and testflags(detail.flags, #0008)


The problem with this search expression is that it defeats the search optimiser because of the function call. The search optimiser only works for sequences of field = constant. The result is that the entire expression is evaluated for every record in the Detail file. This is very slow (on the order of 30 seconds when done on a network). However, the first part of this search involves an indexed field and should be very fast. How can we force MoneyWorks to do that fast search first and then do the slow search for just the small number of records found by the first part? Well idealy the search optimiser would be smarter. But it isn't.

Relational search syntax to the rescue. The relational search syntax is explicit about separating a search into separate stages (normally for the purpose of searching in separate, related files). But how about we use it to separate two searches in the same file and make use of the implied and between the terms?

[detail:detail.parentseq = transaction.sequencenumber][detail:testflags(detail.flags, #0008)]

In the first stage, the search is simple and also is searching an indexed field, so this part of the search will be very fast. The second stage will only need to examine the relatively small number of records yielded by the first stage.

If you are implementing any kind of automation that may run frequently (especially REST requests!), avoid using non-indexed fields for a search when an indexed field is available. Search a non-indexed field can result in loading every record in the table, resulting in high CPU load.

How do I know what fields are indexed?

Here's a script to dump the entire database schema, which indicates which fields have indexes

on Load
    let x = Export("xmlschema")
    syslog(x) 
    // indexed fields have attribute indexed="true"
end