Stored Procedure performance improvement

Database

Stored Procedure performance improvement

I have two stored procedures. IT1_SLA procedure calls SP_GET_NBD and then does further calculation according to the value returned from it. SP_GET_NBD procedure finds the next business day for a date value and then IT1_SLA procedure use that value and calculates whether a metric is a miss or a make.I am calling IT1_SLA procedure and it is taking 30min for around 9500 records. I have to process more than 80K records. Can anyone give me tips to optimize the stored procedures. I am new to teradata.

5 REPLIES
Senior Apprentice

Re: Stored Procedure performance improvement

Sounds like your're using some cursor-based processing. Of course this is slow, in every DBMS, but especially bad in a parallel system.

E.g. you don't need an SP to find the next business day, better use a calendar, etc.

Can you show the source of those SPs?

You should change the logic to set-based processing, there are some good articles about that topic, see GeorgeColeman's blog.

Re: Stored Procedure performance improvement

thanks for replying so soon. I have added my code which i need to optimize.

REPLACE PROCEDURE LAB_SVC_FTC.IT1_SLA_updated()
BEGIN
DECLARE Same_Day BYTEINT default 0;
DECLARE RtnVal INTeger default 0;
declare dps_num1 varchar(20);
declare crea_date timestamp(0);
declare disp_date timestamp(0);
declare dsp_ack timestamp(0);
declare contract_level varchar(15) ;
declare COUNTRYCODE varchar(50) ;
declare TMZN_LCN_ID integer;
declare rtn timestamp(0);

DECLARE sample_cur CURSOR FOR SELECT dps_num,Crea_Date ,disp_date ,dsp_ack,contract_level,COUNTRYCODE,TMZN_LOC_ID from

LAB_SVC_FTC.FTC_emea_INC;

OPEN sample_cur;
label1:
LOOP
FETCH sample_cur into dps_num1,crea_date,disp_date,dsp_ack, contract_level,COUNTRYCODE,TMZN_LCN_ID;
IF (SQLSTATE = '02000') THEN
  LEAVE label1;
END IF;

 call   LAB_SVC_FTC.sp_get_nbd(:Crea_Date,:contract_level,:COUNTRYCODE,'00:00:00',:TMZN_LCN_ID,0,rtn);
 set crea_date = rtn;

            If (disp_date IS NULL) OR (disp_date < Crea_Date) Then
           SET disp_date = Crea_Date;
            End IF;
         
       IF  (cast(dsp_ack as timestamp(0))) > (cast (disp_date as timestamp(0))+ interval '15' minute)  Then
   
   Update LAB_SVC_FTC.FTC_SLA_Results
   set  start_point=disp_date + interval '15' minute,
   end_point=:dsp_ack ,Make_Miss=1
   where LAB_SVC_FTC.FTC_SLA_Results.dps_num=:dps_num1;
   
   ELSE
   
   Update LAB_SVC_FTC.FTC_SLA_Results
   set  start_point=disp_date + interval '15' minute,
   end_point=:dsp_ack,Make_Miss=0
   where LAB_SVC_FTC.FTC_SLA_Results.dps_num=:dps_num1;
                                
            END IF;

END LOOP label1;
CLOSE sample_cur;
END;

Replace procedure LAB_SVC_FTC.SP_GET_NBD
(
IN DateField TIMESTAMP(0),
IN Contract_Level VARCHAR(15),
IN Country VARCHAR(5),
IN TimeField Time(0),
IN TMZN_LCN_ID INTEGER,
IN NBD_DAYS INTEGER,
OUT RTN TIMESTAMP(0)
)

BEGIN
 DECLARE RtnVal TIMESTAMP(0);
 DECLARE Cut_Off TIME(6);
 DECLARE Start_time TIME(6);
 DECLARE Same_Day BYTEINT;
 DECLARE Holidays BYTEINT;
 DECLARE day_add INTEGER;
 DECLARE Mon_flag BYTEINT;
 DECLARE Tue_flag BYTEINT;
 DECLARE Wed_flag BYTEINT;
 DECLARE Thu_flag BYTEINT; 
 DECLARE Fri_flag BYTEINT;
 DECLARE Sat_flag BYTEINT;
 DECLARE Sun_flag BYTEINT;
 DECLARE HOLIDAY_FLG BYTEINT;
 DECLARE RTN_FLG BYTEINT ;
 DECLARE No_Of_Days INTEGER;
 
SET RtnVal=DateField;
SET No_Of_Days=NBD_DAYS;
SET HOLIDAY_FLG = 0;

IF DateField is not null  THEN
Select top 1 Mon_flg, Tue_flg, Wed_flg, Thu_flg, Fri_flg, Sat_flg, Sun_flg ,Hldy_Flg, SBD_FLG, Techl_supp_strt_tm , Techl_supp_cut_off_tm
into Mon_flag, Tue_flag, Wed_flag, Thu_flag, Fri_flag, Sat_flag, Sun_flag, Holidays, Same_Day, Start_time,Cut_Off
from  lab_svc_ftc.sla_cut_off_tm Where CNTRCT_LVL_CD =:Contract_Level  AND TMZN_LOC_ID=:TMZN_LCN_ID AND ctry_cd=Country and ctry_cd is not null and CNTRCT_LVL_CD is not null;

IF RtnVal < CAST(cast(RtnVal as date) as timestamp(0)) + ((Start_time- time '00:00:00') hour to second) THEN
SET RtnVal =  CAST(cast(RtnVal as date) as timestamp(0)) + ((Start_time- time '00:00:00') hour to second);
END IF;

IF RtnVal > CAST(cast(RtnVal as date) as timestamp(0)) + ((cut_off- time '00:00:00') hour to second) THEN
SET RtnVal = CAST(cast(RtnVal as date) as timestamp(0)) + ((cut_off- time '00:00:00') hour to second);
END IF;

WHILE No_Of_Days>=0 DO
BEGIN
SET RTN_FLG=1;
WHILE RTN_FLG=1 do
BEGIN
Sel CASE
 -- Sun                                                     
 WHEN day_of_week = 1 AND Sun_flag = 0  THEN 1
 -- Mon                                                     
 WHEN day_of_week = 2 AND Mon_flag = 0  THEN 1
 -- Tue                                                     
 WHEN day_of_week = 3 AND Tue_flag = 0  THEN 1
 -- Wed                                                     
 WHEN day_of_week = 4 AND Wed_flag = 0  THEN 1
 -- Thu                                                     
 WHEN day_of_week = 5 AND Thu_flag = 0  THEN 1
 -- Fri                                                     
 WHEN day_of_week = 6 AND Fri_flag = 0  THEN 1
 -- Sat                                                         
 WHEN day_of_week = 7 AND Sat_flag = 0  THEN 1
 ELSE 0
END into day_add from sys_calendar.CALENDAR where calendar_date=RtnVal;

IF day_add=0 THEN
 IF Holidays = 1 AND EXISTS(SELECT * FROM svc_base.gbl_hldy_cldr WHERE TMZN_LOC_ID=:TMZN_LCN_ID and ctry_cd=Country and RtnVal>=hldy_strt_dts and rtnval<=hldy_end_dts)
 THEN SET day_add = 1;
 ELSE SET RTN_FLG = 0;
 END IF;
END IF;

IF day_add>0 THEN
SET RtnVal = RTNVAL + CAST(day_add AS INTERVAL DAY);
END IF;
      
END;
END while;

IF No_Of_Days>0 THEN
SET RtnVal = RTNVAL + INTERVAL '1' DAY;
END IF;

SET No_Of_Days=No_Of_Days-1;

END;
END while;

IF RtnVal <> DateField AND TimeField IS NOT NULL THEN
SET RtnVal = CAST(cast(RtnVal as date) as timestamp(0)) + ((TimeField- time '00:00:00') hour to second);
END IF;
END IF;

SET RTN=RTNVAL;

end;

Please see if it can be improved somehow.

thanks

Prachi

Senior Apprentice

Re: Stored Procedure performance improvement

Hi Prachi,

yep, that's a lot of logic. 

The WHILE in LAB_SVC_FTC.SP_GET_NBD assigns the next busines day, so I would start with this first.

If you got a calendar (do you actually use sys_calendar, don't you have your own calendar table?) you can utilize following approach.

Assign a sequence number to each business day and don't increase it during holidays/weekends:

SELECT cal.*,hldy.*,
case when hldy.hldy_strt_dts is not null -- exclude holidays
or cal.day_of_week in (1,7) -- exclude weekends
then 0
else 1
end as BusinessDayFlag,
sum(BusinessDayFlag) -- -> only buiness days increase
over (order by cdate
rows unbounded preceding) as BusinessDay#
FROM
( select calendar_date as cdate, day_of_week
from sys_calendar.calendar
where cdate between date -100 and date + 30
) as cal
left join
(
select * from svc_base.gbl_hldy_cldr
WHERE TMZN_LOC_ID=:TMZN_LCN_ID and ctry_cd=Country
) hldy
on cal.cdate between hldy_strt_dts and hldy_end_dts

You need to repeat this for the different Contract_Level/TMZN_LOC_ID/ctry_cd combinations (in a Volatile Table?).

Now for any date to find the nth business day needs two joins, one for the actual date to get the BusinessDay# of this date and another on BusinessDay# + n AND BusinessDayFlag = 1 to find the matching BusinessDay.

Get this working and you got rid of the WHILE, now everything else including the cursor can probably be rewritten using set based logic...

Re: Stored Procedure performance improvement

But you see my weekends are not fixed as sat and sun. They will be according to the flag values mon_flag,tue_flag,....

if any flag=0 then it is a weekend.

so i cant consider 1,7 always.

Senior Apprentice

Re: Stored Procedure performance improvement

Hi Prachi,

this logic can probably also be included in that CASE, it's just from another table...