Query Optimisation in Sap Hana SPS12

[former_member565459]

I've a query which gives the stock status.

I've tried optimizing it. Earlier it used to take 20mins for current_date -1 now it takes 6mins.

The problem here is I want to take a full load using this query for last 13 months which inturn uses a lot of memory.

How the full-load activity be achieved in a most efficient way for the same.

The query being used is :

SELECT A.EBELN PO_NO,A.EBELP PO_ITEM,A.LOEKZ DEL_IND_DTL,A.MATNR ARTICLE,A.WERKS RECEIVING_SITE,A.LGORT STORAGE_LOCATION,A.MENGE PO_QTY,A.MEINS PO_UNIT,A.UMREZ NUMERATOR,
A.UMREN DENOMINATOR,A.NETPR NET_PRICE,A.NETWR PO_VALUE,A.ELIKZ DEL_COMP_FLAG,A.PSTYP ITEM_CATEGORY,A.WEPOS GRN_IND,A.LMEIN BASE_UNIT,A.BANFN PR_NO,A.BNFPO PR_ITEM,
A.RETPO RTV_PO,REC_SITE.FORMAT_CD,REC_SITE.STORE_SHORT_NAME,REC_SITE.SITE_CATEGORY,REC_SITE.COMPANY_CODE RECEIVING_COMPANY_CODE,B.LIFNR VENDOR,B.BSTYP PO_CATEGORY,
B.AEDAT CREATION_DATE,B.BEDAT ORDER_DATE,B.STATU PO_STATUS,B.ERNAM CREATED_BY,B.EKGRP PURCHASE_GRP,B.BUKRS COMPANY_CODE,B.RESWK SUPPLY_SITE,B.FRGKE REL_IND,B.BSART PO_TYPE,
B.ZTERM,B.WAERS CURRENCY,B.FRGZU REL_STATE,
(CASE WHEN SUPPLY_SITE.STORE_SHORT_NAME IS NULL THEN V.NAME1 ELSE SUPPLY_SITE.STORE_SHORT_NAME END ) SUPPLY_SITE_NAME,
C.EINDT DELIVERY_DATE,C.SLFDT STAT_DELV_DATE,C.WEMNG GRN_QTY,C.WAMNG ISSUED_QTY,C.WADAT ISSUED_DATE,C.ON_TIME_GRN_QTY,C.RECEIPT_VAL,C.FIRST_GRN_DT,C.LAST_GRN_DT,
C.FIRST_DOC_NO,C.LAST_DOC_NO,D.FRGDT PR_DATE,D.MENGE PR_QTY,(CASE WHEN E.PARVW='ZR' AND (E.LIFN2 IS NULL OR E.LIFN2='') THEN B.LIFNR ELSE E.LIFN2 END) MAIN_VENDOR,
(CASE WHEN month(C.EINDT) in ('01','02','03') THEN TO_CHAR(TO_int(left(C.EINDT,4))-1) ELSE left(C.EINDT,4) END) FISCAL_YEAR,
(case when month(C.EINDT) in ('01','02','03') then to_int(month(C.EINDT))+9
else to_int(month(C.EINDT))-3 end)  fiscal_period,
'V3' FISCVARN,
to_CHAR(C.EINDT,'YYYYMMDD') DATE_SAP, 
'0'||RIGHT(QUARTER(C.EINDT),1) QUARTER,
'0'||WEEKDAY(C.EINDT) DAY_OF_WEEK,
RIGHT(to_CHAR(C.EINDT,'YYYYMMDD'),2) DAY,
left(to_CHAR(C.EINDT,'YYYYMMDD'),6) CAL_MONTH,
left(to_CHAR(C.EINDT,'YYYYMMDD'),4)||(case when length(week(C.EINDT)) = 1 then '0'|| to_char((week(C.EINDT))) else to_char((week(C.EINDT))) end) CAL_WEEK,
H.ASN_QTY,H.ASN_NO,H.ASN_DT,
(CASE WHEN ST.LGORT='1000' THEN ST.LABST END) STORE_STOCK,
(CASE WHEN SUP.LGORT='1000' THEN SUP.LABST END) SUPP_SITE_STOCK,
(CASE WHEN REC_SITE."FORMAT_CD" IN ('321','323') THEN 'AJIO'
     WHEN  REC_SITE."FORMAT_CD" IN ( '1','3','14','31','4','6','27','81','62','56','60','328','55') THEN 'VALUE' 
     WHEN  REC_SITE."FORMAT_CD" IN ( '2','511','520','302','13') THEN 'DIGITAL'
     WHEN  REC_SITE."FORMAT_CD" IN ('301','7') THEN 'TRENDS' 
     WHEN  REC_SITE."FORMAT_CD" IN ('359') THEN 'EGROCERY'
     WHEN  REC_SITE."FORMAT_CD" IN ('362') THEN 'B2B' END ) FORMAT_FLAG
FROM P22.EKKO B
INNER JOIN P22.EKPO A
ON A.EBELN=B.EBELN 
INNER JOIN HADMIN.S_SITE_MASTER REC_SITE
ON A.WERKS=REC_SITE.STORE_NO
AND REC_SITE.FORMAT_CD IN ('321','323','1','3','14','31','4','6','27','81','62','56','60','328','55','2','511','520','302','13','301','7','359','362')
LEFT OUTER JOIN 
HADMIN.S_SITE_MASTER SUPPLY_SITE
ON LTRIM(B.LIFNR,0)=SUPPLY_SITE.STORE_NO
AND SUPPLY_SITE.FORMAT_CD IN('321','323','81','62','58','68','66','60','328')
LEFT OUTER JOIN P22.LFA1 V
ON B.LIFNR=V.LIFNR
LEFT OUTER JOIN
(SELECT A.EBELN,A.EBELP,A.EINDT,A.SLFDT,A.WEMNG,A.WAMNG,A.WADAT,
SUM(CASE WHEN "BWART" IN ('101','105') AND TO_DATE("BUDAT")<=TO_DATE("SLFDT") THEN B.MENGE END)-
SUM(CASE WHEN "BWART" IN ('102','106') AND TO_DATE("BUDAT")<=TO_DATE("SLFDT") THEN B.MENGE END) ON_TIME_GRN_QTY,
SUM(CASE WHEN "BWART" IN ('101','105') THEN "DMBTR" ELSE 0 END)-
SUM(CASE WHEN "BWART" IN ('102','106') THEN "DMBTR" ELSE 0 END) RECEIPT_VAL,
MIN(CASE WHEN "BWART" IN ('101','105') THEN "BUDAT" ELSE NULL END) FIRST_GRN_DT,
MAX(CASE WHEN "BWART" IN ('101','105') THEN "BUDAT" ELSE NULL END) LAST_GRN_DT,
MIN(CASE WHEN "BWART" IN ('101','105') THEN "BELNR" ELSE NULL END) FIRST_DOC_NO,
MAX(CASE WHEN "BWART" IN ('101','105') THEN "BELNR" ELSE NULL END) LAST_DOC_NO
FROM P22.EKET A
LEFT OUTER JOIN P22.EKBE B
ON A.EBELN=B.EBELN AND
   A.EBELP=B.EBELP
AND TO_DATE(SLFDT) = ADD_days(CURRENT_DATE,-1)
AND BUDAT = ADD_days(CURRENT_DATE,-1)
WHERE
BWART IN ('101','102','105','106')
GROUP BY A.EBELN,A.EBELP,A.EINDT,A.SLFDT,A.WEMNG,A.WAMNG,A.WADAT) C
ON A.EBELN=C.EBELN AND
   A.EBELP=C.EBELP
LEFT OUTER JOIN P22.EBAN D
ON A.BANFN=D.BANFN AND
   A.BNFPO=D.BNFPO
   AND D.LOEKZ=''
LEFT OUTER JOIN P22.MARD ST
ON A.WERKS=ST.WERKS AND A.MATNR=ST.MATNR 
AND ST.LGORT = '1000'
LEFT OUTER JOIN P22.MARD SUP
ON B.LIFNR=SUP.WERKS AND A.MATNR=SUP.MATNR 
AND SUP.LGORT = '1000'
LEFT OUTER JOIN P22ERP.WYT3 E
ON LTRIM(B.LIFNR,0)=LTRIM(E.LIFNR,0)
AND E.PARVW='ZR'
LEFT OUTER JOIN
(SELECT B.VGBEL,
       LTRIM(B.VGPOS,0) VGPOS,
       MAX(A.LFDAT) ASN_DT,
       SUM(B.LFIMG) ASN_QTY,
       MAX(A.VBELN) ASN_NO
FROM P22.LIKP A
INNER JOIN P22.LIPS B
ON A.MANDT=B.MANDT AND
   A.VBELN=B.VBELN
WHERE A.LFDAT=ADD_days(CURRENT_DATE,-1)
GROUP BY B.VGBEL,LTRIM(B.VGPOS,0)) H
ON A.EBELN=H.VGBEL AND LTRIM(A.EBELP,0)=H.VGPOS
WHERE 
 A.AEDAT = ADD_days(CURRENT_DATE,-1) 
AND B.AEDAT = ADD_days(CURRENT_DATE,-1)  
 and A.LOEKZ='' AND B.LOEKZ=''