Query is taking too much time SAP Business One HANA

[nikunjmehta2290]

Dear all,

Please check below query and do the needful. I am taking where condition with single document number and query is takes around 5 min for fetching the result. Please suggest to me where need to change required in below query.

SELECT 
DISTINCT 
T0."DocEntry",
T0."DocNum",
T0."NumAtCard",
T0."CardCode",T0."CardName",
T4."Cellular" AS "BP MOBILE NO",
T0."PayToCode" AS "BILL TO PARTY",
T0."DocCur" AS "DOCUMENT CURRECY",
T0."DocDate" AS "POSTING DATE",
T0."DocDueDate" AS "DUE DATE",
T0."TaxDate" AS "DOCUMENT DATE",
T0."NumAtCard" AS "CUSTOMER REF",
T0."ShipToCode",
T0."PayToCode",
T0."DocTotal",
T0."TotalExpns",T0."RoundDif",
T0."Comments" AS "REMARKS",
T0."U_G_ENTRY" AS "GATE ENTRY NO"
,T1."ItemCode" AS "ITEM Code",
T1."Dscription" AS "ITEM NAME",
T1."Quantity" AS "QUANTITY",
T1."Price" AS "PRICE",
T1."TaxCode" AS "TAX Code"
,T1."LineTotal" AS "LINE TOTAL",
T1."LineNum",
CAST(IFNULL(T5."Building",'') AS VARCHAR(100)) AS "Bill Building",
T5."City" AS "Bill CITY",
T5."ZipCode" AS "Bill ZIP",
T9."GSTCode" AS "BILL State GSTCODE",
T9."Name" AS "BILL State",

CAST(IFNULL(T11."Building",'') AS VARCHAR(100)) AS "SHIP Building"
,T11."City" AS "SHIP CITY",
T11."ZipCode" AS "SHIP ZIP",
T12."GSTCode" AS "SHIP State GSTCODE",
T12."Name" AS "SHIP State",
T20."Name" AS "SHIP COUNTRIES",
T11."GSTRegnNo" AS "SHIP GST NO"
,T0."Address" AS "BILL TO Address"
,T0."Address2" AS "SHIP TO Address"    
,(Select x."Street" from OLCT x where x."Code" = T1."LocCode") AS "Loc Street"
,(Select x."Block" from OLCT x where x."Code" = T1."LocCode") AS "Loc Block"
,(Select CAST (x."Building" AS VARCHAR(255)) from OLCT x where x."Code" = T1."LocCode") AS "Loc Building"
,(Select x."City" from OLCT x where x."Code" = T1."LocCode") AS "Loc City"
,(Select x."ZipCode" from OLCT x where x."Code" = T1."LocCode") AS "Loc Zipcode"
     ,T15."LocGSTN",
     T15."LocStatCod",
     T15."LocStaGSTN",
    (case when T15."LocGSTType"='2' then 'Casual Taxable Person' 
          when T15."LocGSTType"='3' then 'Composition Levy'
          when T15."LocGSTType"='4' then 'Government Department or PSU'
          when T15."LocGSTType"='5' then 'Non Resident Taxable Person' 
          when T15."LocGSTType"='1' then 'Regular/TDS/ISD'
          when T15."LocGSTType"='6' then 'UN Agency or Embassy' End) AS "LocGSTType"
          ,(SELECT x."TanWardNo" FROM OLCT x WHERE x."Code" = T1."LocCode")   AS   "Loc DLN No" ,
(SELECT x."TanOfficer" FROM OLCT x WHERE x."Code" = T1."LocCode")  AS  "Loc DLN No1" 
,(CASE WHEN T4."U_D_F_20" IS NULL THEN T4."Notes" else T4."U_D_F_20" end)  AS "Cust DLN No",
T4."U_D_F_21" AS "Cust DLN No1"
,CASE  WHEN IFNULL(T0."U_UNE_SONUM",'') ='' 
		THEN  (CASE  WHEN IFNULL(R0."NumAtCard",'') ='' THEN  CAST(R0."DocNum" AS VARCHAR(200)) ELSE  CAST(IFNULL(R0."NumAtCard",'') AS VARCHAR(200) ) END )
		ELSE  CAST(IFNULL(T0."U_UNE_SONUM",'') AS VARCHAR(200) ) END AS "PONo"
,R0."DocDate" AS "PO DATE"
,CASE WHEN IFNULL(IBH."MnfSerial",'') = '' THEN IBH."DistNumber" ELSE IBH."MnfSerial" END AS "BATCHNUM"
,SB1."PrdDate" AS "BATCH MFG DATE" 
,SB1."ExpDate" AS "BATCH EXP DATE",
IFNULL(SFGI."Quantity",0) AS "BATCH QTY" 
,IFNULL(T1."U_UNE_MRP",0)  AS "BATCH MRP"
,T4."U_D_F_20",
T4."U_D_F_21"
,(CASE WHEN  IFNULL(SB1."U_MNFTR",'') !='' THEN IFNULL(SB1."U_MNFTR",'') ELSE T0."CardName" END)  AS "Manufacturer"
FROM 
OPDN T0 
LEFT JOIN PDN1 T1 ON T0."DocEntry"=T1."DocEntry"
LEFT JOIN OCRD T4 ON T4."CardCode"=T0."CardCode"
LEFT JOIN CRD1 T5 ON T5."CardCode"=T0."CardCode" AND T5."AdresType"='B' AND T0."PayToCode"=T5."Address" AND T5."Country"='IN'
LEFT JOIN OCST T9 ON T9."Code"=T5."State" AND T9."Country"='IN'
LEFT JOIN CRD1 T11 ON T11."CardCode"=T0."CardCode" AND T11."AdresType"='S'  AND T0."ShipToCode"=T11."Address" AND T11."Country"='IN'
LEFT JOIN OCRY T20 ON T20."Code"=T11."Country"
LEFT JOIN OCST T12 ON T12."Code"=T11."State" AND T12."Country"='IN'
LEFT JOIN PDN12 T15 ON T15."DocEntry"=T0."DocEntry"   
LEFT  JOIN POR1 R1 ON T1."BaseEntry"=R1."DocEntry" AND T1."BaseLine"=R1."LineNum"
LEFT  JOIN OPOR R0 ON R1."DocEntry"=R0."DocEntry"
LEFT JOIN IBT1 SFGI On T1."DocEntry" = SFGI."BaseEntry" and T1."LineNum" = SFGI."BaseLinNum" AND T1."ItemCode"=SFGI."ItemCode" AND SFGI."BaseType"=T1."ObjType" AND SFGI."WhsCode" =T1."WhsCode"
LEFT JOIN OIBT SB1 On SFGI."ItemCode"=SB1."ItemCode" And SB1."BatchNum"=SFGI."BatchNum"  AND SFGI."WhsCode" =SB1."WhsCode" 
LEFT JOIN OBTN IBH On SFGI."ItemCode"=IBH."ItemCode" AND IBH."DistNumber"=SFGI."BatchNum"  
WHERE T0."DocNum" = '222502466'
ORDER BY T1."LineNum";

SAP Business One HANA

Regards,

Nikunj Mehta

[nikunjmehta2290]

Hi,

Thanks for the update.

But I have made the one small changes in above query. I have used INNER join instead of Left join with OIBT to IBT1 tables and found the result successful as per below query. It's worked fine.

LEFT JOIN IBT1 SFGI On T1."DocEntry" = SFGI."BaseEntry" and T1."LineNum" = SFGI."BaseLinNum" AND T1."ItemCode"=SFGI."ItemCode" AND SFGI."BaseType"=T1."ObjType" AND SFGI."WhsCode" =T1."WhsCode"
INNER JOIN OIBT SB1 On SFGI."ItemCode"=SB1."ItemCode" And SB1."BatchNum"=SFGI."BatchNum"  AND SFGI."WhsCode" =SB1."WhsCode" c

[Johan_Hakkesteegt]

Hi,

The only thing that comes mind, that you can try is to left join the OLCT table, instead of using it many times in subqueries. In each subquery you link it to PDN1.LocCode anyway.

Regards,

Johan

[nikunjmehta2290]

Hi,

Thanks for the update. I have tried with OLCT table join with PDN1 and remove the relevant sub queries. But Now , It's take few more times to executing. Even, I am remove that fields from the query then it's taking a same times.

Regards,

Nikunj

[Johan_Hakkesteegt]

Hi Nikunj,

Please test this small change. Replace this:

,(CASE WHEN  IFNULL(SB1."U_MNFTR",'') !='' THEN

with this:

,(CASE WHEN LENGTH(IFNULL(SB1."U_MNFTR",'')= > 0 THEN

If that does not help, which is likely, then you need to go through the tables. One of the tables is possibly not set to be in-memory. It is probably a table that is not used a lot.

Next possible cullprit is a table with a lot of rows. Likely candidates are POR1, PDN1, and the batch tables.

Another thing you can try is to collect related header and rows tables (for example OPOR and POR1) into their own subqueries in the FROM clause.

Regards,

Johan