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.

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.


Thanks,
Tom