> For us to be able to help you, I would be good if you posted the
> actual query, the plan and told us a bit about the indexes and
> table(s) involved.

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.

CREATE PROCEDURE AUFTRAGSBESTAND_AKTUELL (
PARENT INTEGER,
MULTMENGE FLOAT,
LBEWEG CHAR (1))
RETURNS (
OUT_AR_ID INTEGER,
OUT_ID INTEGER,
OUT_NEU CHAR (1))
AS
BEGIN

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
do begin

select * from ADD_DURCHSATZ(:OUT_AR_ID, :LMenge, :DMenge)
into :OUT_NEU;
Suspend;

if (SListe = "T") then begin
for select * from AUFTRAGSBESTAND_AKTUELL
(:OUT_ID, :DMenge, :Temp) into :OUT_AR_ID, :OUT_ID, :OUT_NEU do
Suspend;
end
end

END


CREATE PROCEDURE ADD_DURCHSATZ (
ARTIKEL INTEGER,
MENGE1 FLOAT,
MENGE2 FLOAT)
RETURNS (
NEW CHAR (1))
AS
declare variable id integer;
BEGIN
select std_id from st_durchsatz where std_Ar_id = :artikel
into :id;

if (id is null) then begin
insert into st_durchsatz(std_id, std_ar_id, std_lbewegung,
std_durchsatz)
values (gen_id(std_id,1), :artikel, :menge1, :menge2);

new = "T";
end

else begin
update st_durchsatz set std_lbewegung = std_lbewegung
+ :menge1,
std_durchsatz = std_durchsatz + :menge2 where std_id = :id;

new = "F";
end
SUSPEND;
END


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?

Relevant indices in table ST_DURCHSATZ:
- STD_AR_ID


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
threee 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!)