Hi Markus!

I think your problem may be having too many non-selective indexes. Try
changing the where-part of your query to:

where
vp_parentpos = :PARENT and
(1=0 or
(vp_delete != "T" and
vp_posart = 1 and
vo_delete != "T" and
vo_status in ("2", "4", "9") and
vo_art in (2, 3)))

I'm assuming that you have an index for vo_id (or that it is a key)
even though you didn't tell me. That should make the query use the
indexes for vp_parentpos and vo_id (which I hope are pretty selective)
unless the tables are very different.

I cannot tell why changing as you did made anything faster, with
sensibly defined indexes changing from IN <constants> should not be
any slower than running three separate queries. I need to see the
plans to understand that, but I hope the change I proposed will help
increasing the execution speed of your procedure considerably. My
GUESS is that the optimizer had so many indexes to choose from that it
chose a completely different plan, which in your case turned out to be
faster for this particular query, but that the choice were different
for the other queries, hence no speed benefit.

One further hint: Drop the index on VO_ART - since it is the first
field in the index on VO_ART, VO_STATUS you already have the
neccessary index for it (even though I think the above mentioned query
would be better without using an index for this field at all).

Set

--- In firebird-support@yahoogroups.com, Markus wrote:
>
> Firebird back online ;) OK, the query is part of an recursive stored
> procedure. I simplified it, so it's easier to read and understand. I
> deleted all the variable declarations, ifs etc. and kept only the
> selects.
>
> for
> select
> vp_ar_id,
> vp_id,
> vp_menge-vp_erlmenge,
> vp_stueckliste,
> vp_sl_bestand,
> vp_sl_container
> from vorgangpos
> inner join vorgang on vp_au_id = vo_id
> where
> vp_parentpos = :PARENT and
> vp_delete != "T" and
> vp_posart = 1 and
> vo_delete != "T" and
> vo_status in ("2", "4", "9") and
> vo_art in (2, 3)
> into :OUT_AR_ID, :OUT_ID, :Menge, :SListe, :Bestand, :Container
>
> The procedure runs recoursivly through the position tree of all
> active (vo_status = 2,4,9) order confirmations (vo_art = 2) and
> delivery notes (vo_art = 3). The amounts of the items are summarized
> in another table (see procedure ADD_DURCHSATZ).
>
> Relevant indices in table VORGANG:
> - VO_ART
> - VO_STATUS
> - VO_ART, VO_STATUS
>
> Relevant indices in table VORGANGPOS:
> - VP_PARENTPOS, VP_DELETE
> Would a seperate index on VP_PARENTPOS help?
>
> In other Storeds I had similar problems with the runtime of in (...)
> statements. Replacing them by ORs didn't help. Using "vo_art in
> (4,5,12)" the runtime was more than 6 times higher, than running
> three seperate querys with vo_art = 4, vo_art = 5 and vo_art = 12.
> Also the runtime increased with the number of rows stored (talking
> of a difference of about 100.000 rows!)