Hello Paul,

> 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.

OK, so the above example is not the real thing.

Want to explain better? Show us the real thing?

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!