IDS Forum

Re: Functions taking more time execute

Posted By: Art Kagel
Date: Sunday, 9 July 2017, at 12:48 a.m.

In Response To: Functions taking more time execute (MUKESH TANUKU)

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

Messages In This Thread

• Functions taking more time execute
  MUKESH TANUKU -- Saturday, 8 July 2017, at 1:53 a.m.
  • Re: Functions taking more time execute
    Art Kagel -- Sunday, 9 July 2017, at 12:48 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.