Hi,

I have a query which contains a single parameter.

Eg.
Select x,y,z
From table
Where a = :param_name

When I run that query on its own supplying a known value for the parameter, it runs in under a second.

When I copy that same thing into a stored procedure, it runs extremely slow even when providing the same known parameter.

Eg.

create procedure myproc (
my_param varchar(20)
) returns (
var_a integer
, var_b integer
, var_c integer
As
Begin
for
Select x,y,z
From table
Where a = :param_name
Into :var_a
, :var_b
, :var_c
Do
Suspend;
end

Anyone had any similar behaviour?

Other considerations:

* There are several joins (about 5)

* All joins are indexed, except one

* Contains a derived table

* Uses a UDF on the non-indexed join.

While I did suspect the UDF, it still doesn't explain why the query runs fast on its own.

Paul.


[Non-text portions of this message have been removed]