Hi Martin

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Martijn Tonies
Sent: Friday, 11 March 2011 4:15 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Query runs extremely slow when run inside a stored procedure



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,


Here is the Query:

select pe.pat_id

, map.element_name

, max(cu_formatdatetime(pe.start_date, 'YYYYMMDDHHNNSS')||'.'||sp.service_provider_no||'~'||map.mbs_item_no) max_enc_string

from cat_tmp_patient_export x

join pat_encounter pe on pe.pat_id = x.pat_id

join mc_encounter_item mei on mei.enc_no = pe.enc_no

join mbs_item i on i.mbs_item_no = mei.mbs_item_no

join cat_mbs_item_map map on map.mbs_item_no = i.item_number

join current_encounter_place cep on cep.enc_place_no is not distinct from pe.enc_place_no

join service_provider sp on sp.enc_no = pe.enc_no and sp.service_provider_no = mei.service_provider_no

where mei.hic_claim_status >= 4

and mei.hic_claim_status < 64

and map.element_name = :v_element_name

group by pe.pat_id, map.element_name

) mbs

join service_provider sp on sp.service_provider_no = 1 --cu_striplastword(cu_stripfirstword(mbs.max_enc_string, '.'), '~')

join pat_encounter pe on pe.enc_no = sp.enc_no

left join cat_tmp_provider_links pr on pr.provider_no = sp.provider_no and pr.enc_place_no = pe.enc_place_no

order by pat_id




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