Hi Anderson

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Anderson Farias
Sent: Saturday, 12 March 2011 4:54 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Query runs extremely slow when run inside a stored procedure



Hi,

What are the query plans executing the SELECT alone and inside SP ?

Regards,
Anderson
__._
The plans are very different at the beginning… don’t understand why.
,_.
*SQL*

select mbs.pat_id
--, mbs.element_name
, cu_formatdateddmmyyyy(pe.start_date) service_date
, pr.provider_link_id provider_link_id
, case
when (select count(*) from cat_mbs_item_map where element_name = mbs.element_name) > 1 then
cu_stripfirstword(mbs.max_enc_string, '~')
else
null
end mbs_item_no
from (
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
/* for mods */
join current_encounter_place cep on cep.enc_place_no is not distinct from pe.enc_place_no
/* end mods */
left 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 = 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

*Plan External to Procedure*

PLAN
(CAT_MBS_ITEM_MAP INDEX (CAT_MBS_ITEM_MAP_ELEMENT)
)
PLAN SORT
(JOIN
(JOIN
(JOIN
(SORT
(JOIN
(JOIN
(MBS MAP INDEX (CAT_MBS_ITEM_MAP_ELEMENT)
, MBS I INDEX (IDX_MBS_ITEM_NUMBER)
, MBS MEI INDEX (FK_MC_ENC_ITEM_MBS_ITEM, IDX_MC_ENC_ITEM_CLAIM_STATUS)
, MBS PE INDEX (PK_PAT_ENCOUNTER)
, MBS X INDEX (IX_CAT_PATIENT_EXPORT)
, MBS CEP INDEX (IDX_CURRENT_ENC_PLACE_NO)
)
, MBS SP INDEX (PK_SERVICE_PROVIDER)
)
)
, SP INDEX (PK_SERVICE_PROVIDER)
)
, PE INDEX (PK_PAT_ENCOUNTER)
)
, PR INDEX (PK_CAT_PROVIDER_LINKS)
)
)

*Plan Internal to Procedure*

PLAN
(CAT_MBS_ITEM_MAP INDEX (CAT_MBS_ITEM_MAP_ELEMENT)
)
SORT
(JOIN
(JOIN
(JOIN
(SORT
(JOIN
(JOIN
(MBS MAP INDEX (CAT_MBS_ITEM_MAP_ELEMENT)
, MBS I INDEX (IDX_MBS_ITEM_NUMBER)
, MBS MEI INDEX (FK_MC_ENC_ITEM_MBS_ITEM, IDX_MC_ENC_ITEM_CLAIM_STATUS)
, MBS PE INDEX (PK_PAT_ENCOUNTER)
, MBS X INDEX (IX_CAT_PATIENT_EXPORT)
, MBS CEP INDEX (IDX_CURRENT_ENC_PLACE_NO)
)
, MBS SP INDEX (PK_SERVICE_PROVIDER)
)
)
, SP INDEX (PK_SERVICE_PROVIDER)
)
, PE INDEX (PK_PAT_ENCOUNTER)
)
, PR INDEX (PK_CAT_PROVIDER_LINKS)
)
)


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