Hi Christian!

Your question is a bit too general for my brains liking, but I'll try
to answer.

With only 20000 records, I'd say your query looks fine (except that
you select fields from UNFALL_FEA rather than its alias). Though with
bigger tables, my guess is that you may be better off with

select UNF.ERFASST, UNF.BFSNUMMER
from UNFALL_FEA UNF
join UNFALL_FEA UF1 on UNF.UNFALL_FEAID = UF1.UNFALL_FEAID
join UNFALLOBJEKT UOB1 on UF1.UNFALL_FEAID = UOB1.UNFALLID
join UNFALLATTRIBUT UA1 on UOB1.UNFALLOBJEKTID = UA1.UNFALLOBJEKTID
where UF1.ERFASST = '11.03.2002'
and ((UA1.KATALOGID = 230) and (UA1.ITEMID in (210)))

This gives the optimizer more leeway, making it possible to avoid the
initial NATURAL (not too bad for small tables like UNFALL_FEA, but
with millions of records it may make things slower). In case of AND,
just add further joins. In case of OR, add UNION.

Admittedly, I've never implemented such a general query builder,
giving the users as much choice as it seems you are doing.

HTH,
Set

--- In firebird-support@yahoogroups.com, Christian Kaufmann wrote:
> Hi,
>
> we have a query builder, that allows the user to set filters for a
> query. Based on this information we build SQL statements. A possible
> statement looks like this:
>
> select UNFALL_FEA.ERFASST, UNFALL_FEA.BFSNUMMER
> from UNFALL_FEA UNF
> where
> UNF.UNFALL_FEAID in
> (select UF1.UNFALL_FEAID from UNFALL_FEA UF1
> join UNFALLOBJEKT UOB1 on UF1.UNFALL_FEAID = UOB1.UNFALLID
> join UNFALLATTRIBUT UA1 on UOB1.UNFALLOBJEKTID = UA1.UNFALLOBJEKTID
> and ((UA1.KATALOGID = 230) and (UA1.ITEMID in (210)))
> where UF1.ERFASST = '11.03.2002')
>
> And the plan:
>
> PLAN (UNF NATURAL)
> PLAN JOIN (UA1 INDEX (IX_UNFALLATTRIBUT_KATALOG),UOB1 INDEX
> (PK_UNFALLOBJEKT),UF1 INDEX (IX_UNFALL_FEA_PRIMARY2))
>
> UNFALL_FEA > 20'000 records
> UNFALLOBJEKT > 35'000 records
> UNFALLATTRIBUT > 550'000 records
>
> The subselect result is only 8 records, but it could be several
> hundred too.
>
> Right now there is only one subselect, but we can have more than
> one, combined with AND and/or OR.
>
> My question is, how can I improve the SQL? Or should I run each
> subselect first and collect the id's in my application. Then union /
> intersect the different id sets and finally get the selected records
> with the result id set and a new query?
>
> cu Christian