At 08:22 AM 3/02/2004 +0100, you wrote:

>Hi there,
>
>I have a proc calling another a statement with an in-clause referring to a
>recursive proc, as
>
>Create procedure proc_1 returns (F2 integer)
>as
> for select F2 from proc_2 where F1 in (select F1 from proc_recursive) do
>suspend;
>end
>
>Run separately, the procs are lightning fast, also the recursive one. But
>put together like this, something happens, resulting in a 10 sec response
>even for the smallest result sets.
>
>Any clue?

You have three SP's here including one recursive one that requires to be
executed just to find out whether it outputs anything. Sight unseen, that
seems a bit wet. Turn it around so that the cursor is on the output of the
recursive procedure.

Parameterise the procs that you're calling - don't put them into
subqueries! even if they work from a "mechanical" point of view, they are
doing a lot of work for little gain.
Create procedure proc_1 returns (F2 integer)
as
declare variable local_f1 whatevertype;
begin
for select PR.F1 from proc_recursive
into :local_f1 do
begin
if (local_f1 is not null) then
begin
EITHER (p2 returns multiple rows)
for select p2.F2 from proc_2 p2 (:local_f1)
into :F2 do
suspend;

OR, if it's a single value per invocation:
select p2.F2 from proc_p2
RETURNING_VALUES F2;
suspend;
end
end
end

create procedure proc_2(v_f1 whatevertype) as
begin
for select...blah.......
where aField = :v_f1
into :v_f1 do
suspend;
end


/hb