Hi All,

When performing a find (in my case via a script) on a FMS hosted DB, does it make much difference if I first perform a find on an indexed field, and them constrain that find as opposed to performing a find on all wanted fields?

To illustrate, I have some 300,000 invoices in the DB, of which only some 4,000 are active (active meaning they have a payment plan being paid off). Finding all active invoices is nice and quick as it's an indexed field. At some point though, I have to use a field that is not indexed (ie an unstored calc). Would it be quicker to find first all active invoices, then constrain the set further using a non-indexed field or would simply doing a perform find on both indexed and non-indexed fields be as quick?

I've found that including an unstored calc which contains data from a related field makes my find extremely slow.

I realize transforming a non-indexable field to an indexable field (changing an unstored calc to a lookup field or auto-enter calc) would eliminate this but that may not always be possible. Understanding how FileMaker performs a find allows me to determine the best approach.

Thanks!

Finding a set on indexed fields, then constraining is quicker.

With the constrain approach, it's only dealing with the found set for the unstored calcs... As opposed to the entire table.

I've found for most data sets, I can make the key searching fields indexed if I try hard enough - usually involves making sure that certain fields can only be updated in a controlled scripted fashion, to force them to recalculate and index correctly. I have almost no fields that are unstored calcs in my main system - just display, not hard data

Have you implemented both versions and run a test comparing them yet? That's always the best place to start. Folks on the forums can comment on what's worked well for them in the past or propose ideas you haven't thought of, but nobody's claim about what should work well for you has anything close to the validity of a test run by you of what actually works well in your particular situation.

I fully agree with webko -- an accounting system with that many records is crying out for stored balances. This also makes reporting much simpler, e.g., if you run monthly statements for your accounts and store the balances, it's then no problem to see how much was owed in Q1 of last year or whatever.

And yes, as jbante says, you must test, but yes in theory constrain should be faster. Another option if your unstored find is extremely slow is to loop through the records and process or omit, instead of doing the slow constrain. Sometimes that's actually faster.