All Forums Database
Keep Smiling 4 posts Joined 09/14
15 May 2015
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.

dnoeth 4628 posts Joined 11/04
15 May 2015

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.
 

Dieter

Keep Smiling 4 posts Joined 09/14
18 May 2015

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

dnoeth 4628 posts Joined 11/04
18 May 2015

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

Dieter

Keep Smiling 4 posts Joined 09/14
19 May 2015

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.

dnoeth 4628 posts Joined 11/04
19 May 2015

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

Dieter

You must sign in to leave a comment.