Interesting Observations: > faster than <....

I have a very large database (>1.4 million records). Users need to
search on number fields using operators like > and <.

Despite the size of the file, searchers on indexed fields using simple
criteria can be very fast (less than 3 seconds!)

However, I have noticed a few interesting quirks - eg,
* that searches that result in big found sets take much longer.
* that searches using > seem to go faster than searches using <
* that if you search a field with > and then do the next search on the
same field with <, it takes an eternity. It's almost as if the field
is re-indexed.
.... and more!

I'm interested in understanding something about the way that FMP
processes searches - does anyone know of any resources where I can
find out more?

Thanks, Gareth

Re: Interesting Observations: > faster than <....

On Oct 2, 6:16 am, Gareth.Den...@gmail.com wrote:
> I have a very large database (>1.4 million records). Users need to
> search on number fields using operators like > and <.
>
> Despite the size of the file, searchers on indexed fields using simple
> criteria can be very fast (less than 3 seconds!)
>
> However, I have noticed a few interesting quirks - eg,
> * that searches that result in big found sets take much longer.
> * that searches using > seem to go faster than searches using <
> * that if you search a field with > and then do the next search on the
> same field with <, it takes an eternity. It's almost as if the field
> is re-indexed.
> ... and more!
>
> I'm interested in understanding something about the way that FMP
> processes searches - does anyone know of any resources where I can
> find out more?
>
> Thanks, Gareth

The first point I've seen, and it makes sense. The more results their
are the more work to enumerate them, and also to find them.

The 2nd and 3rd observations I have not seen; and I wonder if they are
an artifact of your particular database, or perhaps reflect your
system resource usage rather than an inherent performance
characteristic of filemaker. e.g perhaps searching for > allocates a
boat load of memory, that is fast the first time, and then when
searching for < filemaker has to free up that memory before it can
start searching, for example. So its not so much that > is faster than
<, but rather that, searching is fast when your memory is empty, and
slow when its not... I'm just thinking out loud here.

Also, what version of filemaker are you using? And is it patched up to
date?

-cheers,
Dave