Optimise Code

[rakeshkumar1890]

Hi Folks,

Need to optimise below code. Could you please suggest how I can remove the if condition with same result.

On every left join it increases data load and getting slow process.

CTSUniqueLineMapping:
LOAD cts_unique_order_line,
STFA_SoliMaKey,
STFA_Material,
STFA_MSLcode,
STFA_OSLocation,
STFA_ItemCleaned,
STFA_OSLastReplanDate,
STFA_OSMaterialRecoveryDate,
CalFirstMatRecoveryDate
FROM
[C:\HP Environment\Projects\BRD\QVD\BRD6.qvd]
(qvd);

Left Join(CTSUniqueLineMapping)
LOAD cts_unique_order_line
, Date(max(CalFirstMatRecoveryDate)) as FirstRecoverydate
, Date(max(STFA_OSLastReplanDate)) as MaxLastReplanDt
Resident CTSUniqueLineMapping Group by cts_unique_order_line;

Left Join(CTSUniqueLineMapping)
LOAD cts_unique_order_line, Date(max(STFA_OSMaterialRecoveryDate)) as LastRecoverydate1
Resident CTSUniqueLineMapping
where STFA_OSLastReplanDate=MaxLastReplanDt Group by cts_unique_order_line;

Left Join(CTSUniqueLineMapping)
LOAD cts_unique_order_line,
if(IsNull(LastRecoverydate1) and DATE(LastWorkDate(STFA_ItemCleaned, 3))<=STFA_OSLastReplanDate ,Date#('9999-12-31','YYYY-MM-DD'),
If( DATE(LastWorkDate(STFA_ItemCleaned, 3))>STFA_OSLastReplanDate,'NA',LastRecoverydate1)) AS LastRecoverydate
Resident CTSUniqueLineMapping;

Left Join(CTSUniqueLineMapping)
LOAD cts_unique_order_line, STFA_Material, STFA_MSLcode, STFA_SoliMaKey, Count(STFA_SoliMaKey) as STFA_SoliMaKey_cnt
Resident CTSUniqueLineMapping
Group by cts_unique_order_line, STFA_Material, STFA_MSLcode, STFA_SoliMaKey;

NoConcatenate
STFA_T1:
LOAD Distinct cts_unique_order_line,
STFA_Material,
STFA_MSLcode,
lower(trim(STFA_MSLcode)) as STFA_MSLCode_key,
STFA_SoliMaKey_cnt,
STFA_SoliMaKey as STFA_ImpactedSolima,
STFA_ItemCleaned,
IF(LEN(FirstRecoverydate)=0,'NA',FirstRecoverydate) as FirstRecoverydate,
LastRecoverydate,
STFA_OSLastReplanDate,
MaxLastReplanDt,
CalFirstMatRecoveryDate,
(if((LastRecoverydate=FirstRecoverydate), 0,
if(LastRecoverydate>FirstRecoverydate,
(NetWorkDays(Date(left(FirstRecoverydate,10),'YYYY-MM-DD')-1,Date(left(LastRecoverydate,10),'YYYY-MM-DD'))),
((NetWorkDays(Date(left(LastRecoverydate,10),'YYYY-MM-DD')-1,Date(left(FirstRecoverydate,10),'YYYY-MM-DD')))*(-1))))) AS MatRecoveryDiff

Resident CTSUniqueLineMapping
order by cts_unique_order_line,STFA_MSLcode,STFA_OSLastReplanDate,STFA_SoliMaKey;

Left Join(STFA_T1)
Load Distinct cts_unique_order_line,
STFA_Material as STFA_MCP1,
if(MatRecoveryDiff<-3.0,'Material Early',
if((MatRecoveryDiff<=0.0 AND MatRecoveryDiff>=-3.0),'Material Ontime' ,
if(MatRecoveryDiff>0.0,'Material delay'))) as [STFA Root Cause 1]
Resident STFA_T1
where(CalFirstMatRecoveryDate = FirstRecoverydate);

Left Join(STFA_T1)
Load Distinct cts_unique_order_line,
if((FirstRecoverydate = 'NA' or CalFirstMatRecoveryDate = 'NA'),'NA',STFA_MCP1) as STFA_MCP
Resident STFA_T1;

Left Join(STFA_T1)
Load Distinct cts_unique_order_line,
STFA_Material as STFA_MCP_Last1,
if(MatRecoveryDiff<-3.0,'Material Early',
if((MatRecoveryDiff<=0.0 AND MatRecoveryDiff>=-3.0),'Material Ontime' ,
if(MatRecoveryDiff>0.0,'Material delay'))) as [STFA Root Cause Last]
Resident STFA_T1
where STFA_OSLastReplanDate = MaxLastReplanDt;

Left Join(STFA_T1)
Load Distinct cts_unique_order_line,
if(LastRecoverydate = 'NA','NA',STFA_MCP_Last1) as STFA_MCP_Last
Resident STFA_T1;

Left Join(STFA_T1)
Load Distinct
cts_unique_order_line,
if(IsNull(MatRecoveryDiff) AND MatRecoveryDiff<>'0','First MCP is not available', //DATE(LastWorkDate(STFA_ItemCleaned, 3))>STFA_OSLastReplanDate and
if((CalFirstMatRecoveryDate = FirstRecoverydate OR LastRecoverydate = FirstRecoverydate),[STFA Root Cause 1],
'First MCP is not available, last MCP is available')) as [STFA Root Cause]
Resident STFA_T1 ;

DROP Table CTSUniqueLineMapping;
DROP Field [STFA Root Cause 1];

Left Join(STFA_T1)
LOAD cts_unique_order_line, max(STFA_SoliMaKey_cnt) as STFA_SoliMaKey_MaxCnt
Resident STFA_T1 Group by cts_unique_order_line;

Left Join(STFA_T1)
LOAD cts_unique_order_line, Concat(DISTINCT STFA_MSLCode_key,'|') as STFA_All_MSLCode_key
Resident STFA_T1 Group by cts_unique_order_line;

STFA_T2:
NoConcatenate
LOAD *,
if(RowNo()=1,1,Peek(RowIndex)+1) as RowIndex,
if(wildmatch(STFA_All_MSLCode_key,'*ww supply issue*') ,'ww supply issue',
if(wildmatch(STFA_All_MSLCode_key,'*forecast*'),'forecast',
if(wildmatch(STFA_All_MSLCode_key,'*supplier issue*'),'supplier issue',STFA_All_MSLCode_key))) as STFA_Preference_MSLCode
Resident STFA_T1;

DROP Table STFA_T1;

Left join(STFA_T2)
LOAD cts_unique_order_line, STFA_MCP, RowIndex, STFA_MSLcode, STFA_ImpactedSolima,MatRecoveryDiff,
FirstRecoverydate, LastRecoverydate, STFA_ItemCleaned, [STFA Root Cause]

Resident STFA_T2
where STFA_Preference_MSLCode=STFA_MSLCode_key and STFA_SoliMaKey_cnt=STFA_SoliMaKey_MaxCnt;

Inner Join(STFA_T2)
LOAD cts_unique_order_line,
min(RowIndex) as RowIndex
Resident STFA_T2
Group by cts_unique_order_line;

DROP Field RowIndex;

EXIT SCRIPT;

[MikeA]

The first thing I would do is split the parts with a distinct clause.

Its better to load distinct into a temp table, then do any operations on a second load statement.

Distinct is supposedly a single threaded operation (I've heard people saying contrary to this so I'm not saying it as fact), but I do know a distinct clause will cause all complex operations on the same load statement to run incredibly slowly (e.g. joins, order by, group by, if statements).

Edit: I forgot to mention that the distinct needs to be completely separate load statement, a preceding load doesn't get out the performance hole of the distinct.

As for the Ifs, most appear to be if this is less/greater so probably not much we can do there.

I did spot one - "IF(LEN(FirstRecoverydate)=0,'NA',FirstRecoverydate) ", could this be replaced with an ALT() function?

[rakeshkumar1890]

Hi Mike

Thanks for  your suggestion.