--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> At 02:23 PM 16/07/2004 -0700, Clive Walden wrote:

Helen,

Thank you so much for your comprehensive response.

I am, in fact, very experienced with SQL (many years with Oracle
mainly) and the reason the query is slow is because the WHERE clause
is based on a two value field (is the record open or closed). In
Oracle I would have used a bitmap index.

Anyway, my wrong assumption was that the filtering was local on the
fetched data, superimposed upon the where clause and not a re-query of
the server.

With some tweaking and using the onFilterRecord event (which seems
faster than the Filter properties, don't know why) I have an
acceptable speed.

I am trying to avoid taking a financial "bath" on my quote for this
conversion, which is why I am not doing a complete re-write.

The two other approaches I am considering, if the speed is not
acceptable are:

1. Add a column with a unique index and triggers that will assign
random numbers to that column in two distinct ranges controlled by
whether the record is "Open" or "Closed". That should speed the query
considerably.

2. Add a single column table populated with just the key fields of
"Open" records. Again controlled by triggers on the main table. Join
this table to the query. In effect, rolling my own index.

With your deep experience of Firebird, do you have any feel for which
might be faster and/or more maintainable?

Thanks again!
Clive.