> 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.

Care to tell us an example query, metadata involved and the query
plan?

> - Child table uses heavy GROUP BY processing to ensure matches.

What does this mean?

> 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.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com