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.
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.
thanks for replying so soon. I have added my code which i need to optimize.
REPLACE PROCEDURE LAB_SVC_FTC.IT1_SLA_updated()
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
FETCH sample_cur into dps_num1,crea_date,disp_date,dsp_ack, contract_level,COUNTRYCODE,TMZN_LCN_ID;
IF (SQLSTATE = '02000') THEN
set crea_date = rtn;
If (disp_date IS NULL) OR (disp_date < Crea_Date) Then
SET disp_date = Crea_Date;
IF (cast(dsp_ack as timestamp(0))) > (cast (disp_date as timestamp(0))+ interval '15' minute) Then
set start_point=disp_date + interval '15' minute,
set start_point=disp_date + interval '15' minute,
END LOOP label1;
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)
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 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);
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);
WHILE No_Of_Days>=0 DO
WHILE RTN_FLG=1 do
WHEN day_of_week = 1 AND Sun_flag = 0 THEN 1
WHEN day_of_week = 2 AND Mon_flag = 0 THEN 1
WHEN day_of_week = 3 AND Tue_flag = 0 THEN 1
WHEN day_of_week = 4 AND Wed_flag = 0 THEN 1
WHEN day_of_week = 5 AND Thu_flag = 0 THEN 1
WHEN day_of_week = 6 AND Fri_flag = 0 THEN 1
WHEN day_of_week = 7 AND Sat_flag = 0 THEN 1
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;
IF day_add>0 THEN
SET RtnVal = RTNVAL + CAST(day_add AS INTERVAL DAY);
IF No_Of_Days>0 THEN
SET RtnVal = RTNVAL + INTERVAL '1' DAY;
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);
Please see if it can be improved somehow.
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:
case when hldy.hldy_strt_dts is not null -- exclude holidays
or cal.day_of_week in (1,7) -- exclude weekends
end as BusinessDayFlag,
sum(BusinessDayFlag) -- -> only buiness days increase
over (order by cdate
rows unbounded preceding) as BusinessDay#
( select calendar_date as cdate, day_of_week
where cdate between date -100 and date + 30
) as cal
select * from svc_base.gbl_hldy_cldr
WHERE TMZN_LOC_ID=:TMZN_LCN_ID and ctry_cd=Country
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...
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.