Hi Martin,

For the particular parameter I use, it returns only 1 record.

Running the select using IB_SQL cursor on it's own returns the row instantaneously (making sure I fetch all rows).

Running it via the stored procedure takes up to a minute.

Paul.

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



Hello Paul,

And what makes you think it runs slower?

How are you using this Stored Procedure? You do know that if you use
SUSPEND in a procedure, you have to SELECT from it?

How many rows are returned?

>> 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]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>

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