I've tried the following (individual changes):

* Removed the for, and just selected into local variables

* Used just the derived table SQL (still slow), and removed the outer select, so now it's just a plain select with no fancy work, just a max on a string concatenation.

* Restarting firebird (v 2.1.2 btw)

* Restarting PC

* Dropping and re-creating

All still take much longer in the stored procedure.

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



Hello Paul,

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

What if you make it a SELECT instead of a FOR SELECT
and remove the SUSPEND?

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!

> Paul.
>
> From: firebird-support@yahoogroups.com<mailto:firebird-support%40yahoogroups.com>
> [mailto:firebird-support@yahoogroups.com<mailto:firebird-support%40yahoogroups.com>] On Behalf Of Martijn Tonies
> Sent: Friday, 11 March 2011 4:25 PM
> To: firebird-support@yahoogroups.com<mailto:firebird-support%40yahoogroups.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]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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]