--- In firebird-support@yahoogroups.com, "tomconlon7777777" wrote:
> If anyone can assist/offer advice I'd appreciate it:
>
> Background:
>
> - Firebird 1.5 2GB DB Win S/S
> - Delphi 7 Winapp
> - 50-70 Users accessing DB from 2 sites
>
> - Main table (120K recs) personal data
> - Child table (3 million + recs) masterid, childid, ratingid
> Child table is queried usually for a few required values
> and a few optional values *with or without ratings*.
> - Queries are varied and dynamically generated; except for a sp
> handling child table questions.
>
> New Requirements:
> - Ability to use results (ids) of a query as input to another.
> - Ability to use a list of ids from memory as input to a query
> - Abilty to use a list of ids from a table as input to a query
> - Ideally ability to kill a query.

***
Hmm, your first two new requirements probably need a lot of
consideration and thought on your part, it is not easily solved in
many cases (especially your second criteria - if I understand 'memory'
correctly). The third requirement is easy, simply do a JOIN. I think
the fourth currently is impossible with SuperServer, whereas Classic
could help you a bit.
***

> Problem:
> - The system is experiencing performance issues.
> - A major new release is coming up and not only is the performance
> to be sorted but also new requirements have arisen.
>
> Current Performance Issues, likely causes:
> - IN() performance. Appears to use Uses multiple indexes(!) even
> when IN() statement is constants. Some cases use 50+ constants.
> - Child table uses heavy GROUP BY processing to ensure matches.
>
> Question(s):
> - The new requirements seem to dictate processing in some form of
> loops. Building IN() clauses is far from ideal + performance
> worries. If you have any other approaches to recomment then great.
> FYI, a 'Temporary' user table (consisting of a single column to hold
> resultant ids that are joined into master table) is currently used.

***
Well, if you wanted to find Tom Conlon and Svein Erling Tysvaer in a
telephone directory, you would have to look us up individually. Hence,
I find it natural to use the index once for every element within an IN
clause. Though replace IN with BETWEEN or EXISTS where possible and
always steer away from IN (<subselect>). Make it a habit to only use
IN where strictly necessary! 'Regeneration' for each step would in
some cases be an alternative to 'loops'.

Like Martijn, I did not understand 'Child table uses heavy GROUP BY
processing to ensure matches'. Please explain. Maybe this could be
replaced by SELECT DISTINCT or WHERE EXISTS.

Set