Functions taking more time execute
Hello Friends,
Thanks for all who are responding for every query and who are very much active
in this group.
I am posing a strange question.
I have a select query which retries only one day data from a lakhs of records.
it takes an execution time of less than 50 milliseconds.
But when i put this SELECT statement inside a function with input arguments as
start date and end date.
While executing this function only for 1 day also its taking very long time
more than 4 minutes.
Im not able to trace the reason. Could anyone help on this how to find the
reason?
Below is my Query:
CREATE FUNCTION neura_ikonkrishi_pp.laboratory_worklist_ipd(start_date
Date,end_date Date)
RETURNINGVARCHAR(15),VARCHAR(100),VARCHAR(50),VARCHAR(15),VARCHAR(25),VARCHAR(15),VARCHAR
(15),VARCHAR(100),
DATETIME YEAR TO FRACTION,INT8,VARCHAR(100),VARCHAR(100),VARCHAR(100);
DEFINE v_patient_id VARCHAR(15);
DEFINE v_patient_name VARCHAR(100);
DEFINE v_patient_category_desc VARCHAR(50);
DEFINE v_gender VARCHAR(15);
DEFINE v_age VARCHAR(25);
DEFINE v_episode_id VARCHAR(15);
DEFINE v_order_id VARCHAR(15);
DEFINE v_generated_by VARCHAR(100);
DEFINE v_generated_time DATETIME YEAR TO FRACTION;
DEFINE v_count INT8;
DEFINE v_bed_name VARCHAR(100);
DEFINE v_floor_name VARCHAR(100);
DEFINE v_nursing_station VARCHAR(100);
/* This Function is used under Laboratory Worklist Screen : Java side */
/* This will return Laboratory Department Patient Details */
/* Only IPD Scenario */
--------------------------------------------------------------------------------
-----------------------------------------------------------------
FOREACH lab_wl_ipd FOR
SELECT
esrt.patient_id,
pdt.first_name||' '||pdt.last_name as Patient_Name,
(select patient_category_desc from master_patientcategory_tbl where
patient_category_id = pdt.patient_category_id) as Patient_Category,
(select gender_desc from master_gender_tbl where gender_id=pdt.gender_id) as
Gender,
ROUND(months_between(SYSDATE,pdt.DOB)/12) ||' years '||
ROUND(months_between(SYSDATE,pdt.DOB)-(TRUNC(months_between(SYSDATE,pdt.DOB)/12)
*12))||' Months ' as age,
esrt.episode_id,
esrt.order_id,
esrt.generated_by,
esrt.generated_time,
(select count(esrt1.priority) from episode_service_rendered_tbl esrt1
where esrt1.order_id=esrt.order_id and esrt1.priority=1 and
esrt1.episode_id=esrt.episode_id),
(select mbt.bed_name from master_bed_tbl mbt,book_bed_tbl bbt where
mbt.bed_id=bbt.bed_id
and bbt.cancel_flag=0 and bbt.episode_id=edt.episode_id) as Bed_Name,
(select mft.floor_desc from master_bed_tbl mbt,nursing_category_assgn_tbl
ncat,book_bed_tbl bbt,master_floor_tbl mft
where mbt.nursing_category_assgn_id=ncat.nursing_category_assgn_id and
mbt.bed_id=bbt.bed_id
and bbt.cancel_flag=0 and bbt.episode_id=edt.episode_id and
ncat.floor_id=mft.floor_id) as Floor_Name,
(select mnst.nursing_station_desc from master_nursing_station_tbl
mnst,book_bed_tbl bbt,
master_bed_tbl mbt,nursing_category_assgn_tbl ncat where bbt.bed_id=mbt.bed_id
and mbt.nursing_category_assgn_id=ncat.nursing_category_assgn_id and
ncat.nursing_station_id=mnst.nursing_station_id
and bbt.episode_id=edt.episode_id) as Nursing_Station
INTO
v_patient_id,v_patient_name,v_patient_category_desc,v_gender,v_age,v_episode_id,
v_order_id,v_generated_by,
v_generated_time,v_count,v_bed_name,v_floor_name,v_nursing_station
FROM
patient_details_tbl pdt,
episode_details_tbl edt,
episode_service_rendered_tbl esrt
WHERE
pdt.patient_id = edt.patient_id
and edt.episode_id = esrt.episode_id
and esrt.delete_flag = 1
and esrt.material_group_sp_id = 29
and esrt.service_avail = 0
and edt.episode_type IN ('ipd','emg','dac')
and esrt.generated_date between start_date and end_date
GROUP BY
esrt.patient_id,patient_name,Patient_Category,Gender,age,esrt.episode_id,
esrt.order_id,esrt.generated_by,esrt.generated_time,Bed_Name,Floor_Name,Nursing_
Station
ORDER BY
esrt.generated_time desc
RETURN
v_patient_id,v_patient_name,v_patient_category_desc,v_gender,v_age,v_episode_id,
v_order_id,v_generated_by,
v_generated_time,v_count,v_bed_name,v_floor_name,v_nursing_station WITH RESUME;
END FOREACH
--------------------------------------------------------------------------------
-------------------------------------------------------------
END FUNCTION;
First let me point out that some of the subqueries in that query would
perform better as simple joins. For example the patient_category and
patient_gender subqueries.
Now, keep in mind that when you run the query with hard coded dates the
dates can be used by the optimizer to decide the best query plan. In the
stored procedure version, however, when the proc is compiled and its query
plans are developed, the optimizer has no idea whether you will supply
dates one day apart or years apart so it has to produce a query plan with
less information. That is likely part of the problem. Another part is those
subqueries I mentioned. Executed by hand with hard dates, the optimizer may
be flattening them into joins for you. Within the stored procedure it may
not be able to do that due to the lack of knowledge of the data range.
Flattening them yourself may improve performance within the proc but not at
all with hard coded dates. It's hard to say without SET EXPLAIN output from
both.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Sat, Jul 8, 2017 at 12:53 AM, MUKESH TANUKU <mukeshbt1328@gmail.com>
wrote:
> Hello Friends,
> Thanks for all who are responding for every query and who are very much
> active
> in this group.
>
> I am posing a strange question.
>
> I have a select query which retries only one day data from a lakhs of
> records.
> it takes an execution time of less than 50 milliseconds.
>
> But when i put this SELECT statement inside a function with input
> arguments as
> start date and end date.
>
> While executing this function only for 1 day also its taking very long time
> more than 4 minutes.
>
> Im not able to trace the reason. Could anyone help on this how to find the
> reason?
>
> Below is my Query:
>
> CREATE FUNCTION neura_ikonkrishi_pp.laboratory_worklist_ipd(start_date
> Date,end_date Date)
> RETURNING
> VARCHAR(15),VARCHAR(100),VARCHAR(50),VARCHAR(15),VARCHAR(25),VARCHAR(15),
> VARCHAR(15),VARCHAR(100),> DATETIME YEAR TO FRACTION,INT8,VARCHAR(100),VARCHAR(100),VARCHAR(100);
>
> DEFINE v_patient_id VARCHAR(15);
> DEFINE v_patient_name VARCHAR(100);
> DEFINE v_patient_category_desc VARCHAR(50);
> DEFINE v_gender VARCHAR(15);
> DEFINE v_age VARCHAR(25);
> DEFINE v_episode_id VARCHAR(15);
> DEFINE v_order_id VARCHAR(15);
> DEFINE v_generated_by VARCHAR(100);
> DEFINE v_generated_time DATETIME YEAR TO FRACTION;
> DEFINE v_count INT8;
> DEFINE v_bed_name VARCHAR(100);
> DEFINE v_floor_name VARCHAR(100);
> DEFINE v_nursing_station VARCHAR(100);
>
> /* This Function is used under Laboratory Worklist Screen : Java side */
> /* This will return Laboratory Department Patient Details */
> /* Only IPD Scenario */
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> -------------------------
>
> FOREACH lab_wl_ipd FOR
>
> SELECT
> esrt.patient_id,
> pdt.first_name||' '||pdt.last_name as Patient_Name,
> (select patient_category_desc from master_patientcategory_tbl where
> patient_category_id = pdt.patient_category_id) as Patient_Category,
> (select gender_desc from master_gender_tbl where gender_id=pdt.gender_id)
> as
> Gender,
> ROUND(months_between(SYSDATE,pdt.DOB)/12) ||' years '||
> ROUND(months_between(SYSDATE,pdt.DOB)-(TRUNC(months_
> between(SYSDATE,pdt.DOB)/12)*12))||'
> Months ' as age,
> esrt.episode_id,
> esrt.order_id,
> esrt.generated_by,
> esrt.generated_time,
> (select count(esrt1.priority) from episode_service_rendered_tbl esrt1
> where esrt1.order_id=esrt.order_id and esrt1.priority=1 and
> esrt1.episode_id=esrt.episode_id),
>
> (select mbt.bed_name from master_bed_tbl mbt,book_bed_tbl bbt where
> mbt.bed_id=bbt.bed_id
> and bbt.cancel_flag=0 and bbt.episode_id=edt.episode_id) as Bed_Name,
>
> (select mft.floor_desc from master_bed_tbl mbt,nursing_category_assgn_tbl
> ncat,book_bed_tbl bbt,master_floor_tbl mft
> where mbt.nursing_category_assgn_id=ncat.nursing_category_assgn_id and
> mbt.bed_id=bbt.bed_id
> and bbt.cancel_flag=0 and bbt.episode_id=edt.episode_id and
> ncat.floor_id=mft.floor_id) as Floor_Name,
>
> (select mnst.nursing_station_desc from master_nursing_station_tbl
> mnst,book_bed_tbl bbt,
> master_bed_tbl mbt,nursing_category_assgn_tbl ncat where
> bbt.bed_id=mbt.bed_id
> and mbt.nursing_category_assgn_id=ncat.nursing_category_assgn_id and
> ncat.nursing_station_id=mnst.nursing_station_id
> and bbt.episode_id=edt.episode_id) as Nursing_Station
> INTO
>
> v_patient_id,v_patient_name,v_patient_category_desc,v_
> gender,v_age,v_episode_id,v_order_id,v_generated_by,
> v_generated_time,v_count,v_bed_name,v_floor_name,v_nursing_station
> FROM
> patient_details_tbl pdt,
> episode_details_tbl edt,
> episode_service_rendered_tbl esrt
> WHERE
> pdt.patient_id = edt.patient_id
> and edt.episode_id = esrt.episode_id
> and esrt.delete_flag = 1
> and esrt.material_group_sp_id = 29
> and esrt.service_avail = 0
> and edt.episode_type IN ('ipd','emg','dac')
> and esrt.generated_date between start_date and end_date
> GROUP BY
> esrt.patient_id,patient_name,Patient_Category,Gender,age,esrt.episode_id,
>
> esrt.order_id,esrt.generated_by,esrt.generated_time,Bed_
> Name,Floor_Name,Nursing_Station
> ORDER BY
> esrt.generated_time desc
>
> RETURN
> v_patient_id,v_patient_name,v_patient_category_desc,v_
> gender,v_age,v_episode_id,v_order_id,v_generated_by,
> v_generated_time,v_count,v_bed_name,v_floor_name,v_nursing_station WITH
> RESUME;
>
> END FOREACH
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> ---------------------
> END FUNCTION;
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>