• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1754
  • Last Modified:

DB2 SQL - performance issue

Hello Experts,
I need help in fine tuning this DB2 sql to help performance:

here is my sql:

WITH    
T1 (PONumber,Store, POStatus,ProjectedDueDate,Vendor,VendorName,InvoiceTotalCost, APMatchType)
AS (    SELECT IH.PO_ID AS PONumber, IH.LOC_ID, IFNULL(MIN(VHG.PO_STATUS_CDE), 'AC') AS POStatus,  
MIN(IH.INV_PROJECTED_DTE) AS ProjectedDueDate,    IH.VENDOR_ID AS Vendor, MIN(V.VENDOR_NME) AS VendorName,  
Sum(IH.MDSE_COST_AMT) AS InvoiceTotalCost, MIN(PH.AP_MATCH_TYP) AS AP_MATCH_TYP    
FROM MRSQC.TAP405INVOICE_HDR IH   INNER JOIN MRSQC.TAP100TRANS_CODE TC ON IH.TRAN_CDE = TC.TRAN_CDE    
INNER JOIN MRSQC.VPO_HEADER_GLOBAL VHG ON IH.VENDOR_ID = VHG.VENDOR_ID AND IH.PO_ID = VHG.PO_ID    
INNER JOIN MRSQC.TAP300PO_HDR PH ON IH.PO_ID = PH.PO_ID     LEFT OUTER JOIN MRSQC.TMS402VH_VENDOR V
ON IH.VENDOR_ID = V.VENDOR_ID     WHERE IH.ROW_INSERT_TS > TIMESTAMP(CURRENT DATE - 12 MONTHS,'00.00.00') AND
IH.INV_STATUS_CDE = 'MCHRDY' AND TC.MATCH_FLG = 'Y'  And PH.AP_MATCH_TYP = 'MER'  
GROUP BY IH.PO_ID, IH.LOC_ID, IH.VENDOR_ID )    

,T2 (PONumber,ReceiptTotalCost)
AS (    SELECT RH.PO_ID AS PONumber,  SUM(RD.LOC_RECV_QTY * RD.VSTYL_DTL_MRKT_AMT) AS ReceiptTotalCost    
FROM MRSQC.TAP310RCPT_HDR RH JOIN MRSQC.TAP311RCPT_DTL RD ON RH.RECEIPT_ID = RD.RECEIPT_ID AND RH.PO_ID = RD.PO_ID  
JOIN T1 ON RH.PO_ID = T1.PONumber Where  RD.MATCH_SET_ID = 0  GROUP BY RH.PO_ID )    

,T3 (PONumber, InvoiceTotalCost, ReceiptTotalCost)
AS (     SELECT T1.PONumber, Sum(T1.InvoiceTotalCost) , T2.ReceiptTotalCost  
FROM T1 JOIN T2 ON T1.PONumber = T2.PONumber GROUP BY T1.PONumber, T2.ReceiptTotalCost )    

,T4 (Vendor, APMatchType) AS   ( Select VENDOR_ID AS Vendor, AP_MATCH_TYP AS APMatchType  
From  MRSQC.TAP125VEND_ASSIGN VA  ,   MRSQC.TMS402VH_VENDOR V  Where UCASE(VA.MATCH_USER_ID) = 'PMANI'
AND LEFT(V.VENDOR_NME, 1)     BETWEEN VA.VEND_STRT_LTTR_CDE AND VA.VEND_END_LTTR_CDE )  

SELECT DISTINCT T1.PONumber, T1.POStatus, Min(T1.ProjectedDueDate) AS ProjectedDueDate,
T1.Vendor, T1.VendorName, T3.InvoiceTotalCost     ,T3.ReceiptTotalCost ,
IFNULL(T3.InvoiceTotalCost - T3.ReceiptTotalCost , 0) AS VarianceAmt ,    
ROUND(((DEC(T3.InvoiceTotalCost, 11,2) - DEC(T3.ReceiptTotalCost, 11, 2)) / DEC(T3.InvoiceTotalCost,11,2) * 100), 2)
AS VariancePerct , T1.APMatchType as APMatchType  
FROM T1 JOIN T3 ON T1.PONumber = T3.PONumber   JOIN T4 ON T1.Vendor = T4.Vendor
AND T1.APMatchType = T4.APMatchType  
GROUP BY T1.PONumber,T1.POStatus,T1.Vendor, T1.VendorName, T3.InvoiceTotalCost ,T3.ReceiptTotalCost , T1.APMatchType  
ORDER BY ProjectedDueDate,  T1.PONumber  



Is there any better way to write the above SQL so the performance will be better....
0
mani_sai
Asked:
mani_sai
  • 10
  • 8
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi mani,

I'd like to see an explain plan, or get some indication of how this query is spending its time.  Do you know how many rows are being returned by each subquery in the CTE?  How many rows in the result set?  etc.

I've reformatted the query to make it more legible and posted it below.



Kent

WITH    
T1 (PONumber,Store, POStatus,ProjectedDueDate,Vendor,VendorName,InvoiceTotalCost, APMatchType)
AS 
(    
  SELECT IH.PO_ID AS PONumber, IH.LOC_ID, IFNULL(MIN(VHG.PO_STATUS_CDE), 'AC') AS POStatus,  
    MIN(IH.INV_PROJECTED_DTE) AS ProjectedDueDate,    IH.VENDOR_ID AS Vendor, MIN(V.VENDOR_NME) AS VendorName,  
    Sum(IH.MDSE_COST_AMT) AS InvoiceTotalCost, MIN(PH.AP_MATCH_TYP) AS AP_MATCH_TYP    
  FROM MRSQC.TAP405INVOICE_HDR IH   
  INNER JOIN MRSQC.TAP100TRANS_CODE TC 
    ON IH.TRAN_CDE = TC.TRAN_CDE    
  INNER JOIN MRSQC.VPO_HEADER_GLOBAL VHG 
    ON IH.VENDOR_ID = VHG.VENDOR_ID AND IH.PO_ID = VHG.PO_ID    
  INNER JOIN MRSQC.TAP300PO_HDR PH 
    ON IH.PO_ID = PH.PO_ID     
  LEFT OUTER JOIN MRSQC.TMS402VH_VENDOR V
    ON IH.VENDOR_ID = V.VENDOR_ID     
  WHERE IH.ROW_INSERT_TS > TIMESTAMP(CURRENT DATE - 12 MONTHS,'00.00.00') 
    AND IH.INV_STATUS_CDE = 'MCHRDY' 
    AND TC.MATCH_FLG = 'Y'  
    And PH.AP_MATCH_TYP = 'MER'  
  GROUP BY IH.PO_ID, IH.LOC_ID, IH.VENDOR_ID 
),
T2 (PONumber,ReceiptTotalCost)
AS 
(    
  SELECT RH.PO_ID AS PONumber,  
    SUM(RD.LOC_RECV_QTY * RD.VSTYL_DTL_MRKT_AMT) AS ReceiptTotalCost    
  FROM MRSQC.TAP310RCPT_HDR RH 
  JOIN MRSQC.TAP311RCPT_DTL RD 
    ON RH.RECEIPT_ID = RD.RECEIPT_ID 
   AND RH.PO_ID = RD.PO_ID  
  JOIN T1 
    ON RH.PO_ID = T1.PONumber 
  Where  RD.MATCH_SET_ID = 0  
  GROUP BY RH.PO_ID 
),
T3 (PONumber, InvoiceTotalCost, ReceiptTotalCost)
AS 
(
  SELECT T1.PONumber, 
    Sum(T1.InvoiceTotalCost) , 
    T2.ReceiptTotalCost  
  FROM T1 
  JOIN T2 
    ON T1.PONumber = T2.PONumber 
  GROUP BY T1.PONumber, T2.ReceiptTotalCost 
),
T4 (Vendor, APMatchType) 
AS 
( 
  Select VENDOR_ID AS Vendor, 
    AP_MATCH_TYP AS APMatchType  
  From  MRSQC.TAP125VEND_ASSIGN VA, MRSQC.TMS402VH_VENDOR V  
  Where UCASE(VA.MATCH_USER_ID) = 'PMANI'
  AND LEFT(V.VENDOR_NME, 1) BETWEEN VA.VEND_STRT_LTTR_CDE AND VA.VEND_END_LTTR_CDE 
)  
SELECT DISTINCT T1.PONumber, T1.POStatus, Min(T1.ProjectedDueDate) AS ProjectedDueDate,
  T1.Vendor, T1.VendorName, T3.InvoiceTotalCost     ,T3.ReceiptTotalCost ,
  IFNULL(T3.InvoiceTotalCost - T3.ReceiptTotalCost , 0) AS VarianceAmt ,    
  ROUND(((DEC(T3.InvoiceTotalCost, 11,2) - DEC(T3.ReceiptTotalCost, 11, 2)) / DEC(T3.InvoiceTotalCost,11,2) * 100), 2) AS VariancePerct , 
  T1.APMatchType as APMatchType  
FROM T1 
JOIN T3 
  ON T1.PONumber = T3.PONumber   
JOIN T4 
  ON T1.Vendor = T4.Vendor
 AND T1.APMatchType = T4.APMatchType  
GROUP BY T1.PONumber,T1.POStatus,T1.Vendor, T1.VendorName, T3.InvoiceTotalCost ,T3.ReceiptTotalCost , T1.APMatchType  
ORDER BY ProjectedDueDate,  T1.PONumber

Open in new window

0
 
mani_saiAuthor Commented:
T1 -  94,700 rows -  4 secs

output from T1 is feed into T2

T3 uses T1 and T2

T4:  25,000 rows - 0.41 secs

Final result set: 90,000 rows ( but timing out most of the times)

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  T1 produces 94,700 rows in 4 seconds, the results are inner joined in t2, then t3, and finally inner joined again in the query.

Within T1, does the query produce a unique value for IH.PO_ID (AS PONumber) for all 94,700 rows?  Just want to make sure that a Cartesian doesn't cause the row count to explode before the final query.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mani_saiAuthor Commented:
NO.
T1 will have duplicate PO rows.
Combination of IH.PO_ID and IH.LOC_ID is unique.

attached screen of  T1 output.


Output-of-T1.doc
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  The timer in the lower right of the screen image suggests that this is returned to you in about 10 seconds (9.859s is the time on the screen).

Can you run the query below (returning only the count of the rows)?  If it returns in about 10 seconds, we can rule out an issue with the query.  The timeout would be a result of the time required to move the results across the network.


Kent

WITH    
T1 (PONumber,Store, POStatus,ProjectedDueDate,Vendor,VendorName,InvoiceTotalCost, APMatchType)
AS 
(    
  SELECT IH.PO_ID AS PONumber, IH.LOC_ID, IFNULL(MIN(VHG.PO_STATUS_CDE), 'AC') AS POStatus,  
    MIN(IH.INV_PROJECTED_DTE) AS ProjectedDueDate,    IH.VENDOR_ID AS Vendor, MIN(V.VENDOR_NME) AS VendorName,  
    Sum(IH.MDSE_COST_AMT) AS InvoiceTotalCost, MIN(PH.AP_MATCH_TYP) AS AP_MATCH_TYP    
  FROM MRSQC.TAP405INVOICE_HDR IH   
  INNER JOIN MRSQC.TAP100TRANS_CODE TC 
    ON IH.TRAN_CDE = TC.TRAN_CDE    
  INNER JOIN MRSQC.VPO_HEADER_GLOBAL VHG 
    ON IH.VENDOR_ID = VHG.VENDOR_ID AND IH.PO_ID = VHG.PO_ID    
  INNER JOIN MRSQC.TAP300PO_HDR PH 
    ON IH.PO_ID = PH.PO_ID     
  LEFT OUTER JOIN MRSQC.TMS402VH_VENDOR V
    ON IH.VENDOR_ID = V.VENDOR_ID     
  WHERE IH.ROW_INSERT_TS > TIMESTAMP(CURRENT DATE - 12 MONTHS,'00.00.00') 
    AND IH.INV_STATUS_CDE = 'MCHRDY' 
    AND TC.MATCH_FLG = 'Y'  
    And PH.AP_MATCH_TYP = 'MER'  
  GROUP BY IH.PO_ID, IH.LOC_ID, IH.VENDOR_ID 
),
T2 (PONumber,ReceiptTotalCost)
AS 
(    
  SELECT RH.PO_ID AS PONumber,  
    SUM(RD.LOC_RECV_QTY * RD.VSTYL_DTL_MRKT_AMT) AS ReceiptTotalCost    
  FROM MRSQC.TAP310RCPT_HDR RH 
  JOIN MRSQC.TAP311RCPT_DTL RD 
    ON RH.RECEIPT_ID = RD.RECEIPT_ID 
   AND RH.PO_ID = RD.PO_ID  
  JOIN T1 
    ON RH.PO_ID = T1.PONumber 
  Where  RD.MATCH_SET_ID = 0  
  GROUP BY RH.PO_ID 
),
T3 (PONumber, InvoiceTotalCost, ReceiptTotalCost)
AS 
(
  SELECT T1.PONumber, 
    Sum(T1.InvoiceTotalCost) , 
    T2.ReceiptTotalCost  
  FROM T1 
  JOIN T2 
    ON T1.PONumber = T2.PONumber 
  GROUP BY T1.PONumber, T2.ReceiptTotalCost 
),
T4 (Vendor, APMatchType) 
AS 
( 
  Select VENDOR_ID AS Vendor, 
    AP_MATCH_TYP AS APMatchType  
  From  MRSQC.TAP125VEND_ASSIGN VA, MRSQC.TMS402VH_VENDOR V  
  Where UCASE(VA.MATCH_USER_ID) = 'PMANI'
  AND LEFT(V.VENDOR_NME, 1) BETWEEN VA.VEND_STRT_LTTR_CDE AND VA.VEND_END_LTTR_CDE 
)
SELECT count(*) 
FROM
(
  SELECT DISTINCT T1.PONumber, T1.POStatus, Min(T1.ProjectedDueDate) AS ProjectedDueDate,
    T1.Vendor, T1.VendorName, T3.InvoiceTotalCost     ,T3.ReceiptTotalCost ,
    IFNULL(T3.InvoiceTotalCost - T3.ReceiptTotalCost , 0) AS VarianceAmt ,    
    ROUND(((DEC(T3.InvoiceTotalCost, 11,2) - DEC(T3.ReceiptTotalCost, 11, 2)) / DEC(T3.InvoiceTotalCost,11,2) * 100), 2) AS VariancePerct , 
    T1.APMatchType as APMatchType  
  FROM T1 
  JOIN T3 
    ON T1.PONumber = T3.PONumber   
  JOIN T4 
    ON T1.Vendor = T4.Vendor
   AND T1.APMatchType = T4.APMatchType  
  GROUP BY T1.PONumber,T1.POStatus,T1.Vendor, T1.VendorName, T3.InvoiceTotalCost ,T3.ReceiptTotalCost , T1.APMatchType  
  ORDER BY ProjectedDueDate,  T1.PONumber
) t0

Open in new window

0
 
mani_saiAuthor Commented:
i tried to run your query to get the count, i am getting error:


 SQL0969N  There is no message text corresponding to SQL error "-4700" in the message file on this workstation.  The error was returned from module "DSNHSM1 " with original tokens "".  SQLSTATE=56038
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hmmm....   Odd.

Does the query in my earlier response work?

  http://www.experts-exchange.com/Database/DB2/Q_27020144.html?cid=1572#a35698509


0
 
mani_saiAuthor Commented:
Yes, that query works.

only the count does not work.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Wow.  Weird...

My first thought is that the SORT is an issue, but DB2 allows sorts of subqueries...

Remove the sort clause and try again.  


Kent
0
 
mani_saiAuthor Commented:
Thanks.
Removed the sort and the query ran for 1 min 29 secs and then times out.

 SQL0905N  Unsuccessful execution due to resource limit being exceeded.  Resource name = "ASUTIME", limit = "000000000027" CPU seconds ("000000883950" service units) derived from "SYSIBM.DSNRLST01".  SQLSTATE=57014


0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  We're learning a lot of good things here.  Timeout is 90 seconds, your version of DB2 doesn't allow sorting of subqueries, and the Cartesian is apparently causing a rather large intermediate result set.

In the T2 subqeury, PONumber will be unique.  In the T1 and T3 subqueries, it will not.  

The final query is joining T1 and T3 on PONumber, causing a Cartesian product.  I suspect that this is where the problem lies.

You might be able to improve this by changing the order of the joins.  Joining T4 to T1 before joining T3 may reduce the number of rows in the intermediate result set to reduce the Cartesian.  Try the query below.

Otherwise, when T1 and T3 are joined, is there a second field that can be used in the join key to reduce the Cartesian?

Kent



WITH    
T1 (PONumber,Store, POStatus,ProjectedDueDate,Vendor,VendorName,InvoiceTotalCost, APMatchType)
AS 
(    
  SELECT IH.PO_ID AS PONumber, IH.LOC_ID, IFNULL(MIN(VHG.PO_STATUS_CDE), 'AC') AS POStatus,  
    MIN(IH.INV_PROJECTED_DTE) AS ProjectedDueDate,    IH.VENDOR_ID AS Vendor, MIN(V.VENDOR_NME) AS VendorName,  
    Sum(IH.MDSE_COST_AMT) AS InvoiceTotalCost, MIN(PH.AP_MATCH_TYP) AS AP_MATCH_TYP    
  FROM MRSQC.TAP405INVOICE_HDR IH   
  INNER JOIN MRSQC.TAP100TRANS_CODE TC 
    ON IH.TRAN_CDE = TC.TRAN_CDE    
  INNER JOIN MRSQC.VPO_HEADER_GLOBAL VHG 
    ON IH.VENDOR_ID = VHG.VENDOR_ID AND IH.PO_ID = VHG.PO_ID    
  INNER JOIN MRSQC.TAP300PO_HDR PH 
    ON IH.PO_ID = PH.PO_ID     
  LEFT OUTER JOIN MRSQC.TMS402VH_VENDOR V
    ON IH.VENDOR_ID = V.VENDOR_ID     
  WHERE IH.ROW_INSERT_TS > TIMESTAMP(CURRENT DATE - 12 MONTHS,'00.00.00') 
    AND IH.INV_STATUS_CDE = 'MCHRDY' 
    AND TC.MATCH_FLG = 'Y'  
    And PH.AP_MATCH_TYP = 'MER'  
  GROUP BY IH.PO_ID, IH.LOC_ID, IH.VENDOR_ID 
),
T2 (PONumber,ReceiptTotalCost)
AS 
(    
  SELECT RH.PO_ID AS PONumber,  
    SUM(RD.LOC_RECV_QTY * RD.VSTYL_DTL_MRKT_AMT) AS ReceiptTotalCost    
  FROM MRSQC.TAP310RCPT_HDR RH 
  JOIN MRSQC.TAP311RCPT_DTL RD 
    ON RH.RECEIPT_ID = RD.RECEIPT_ID 
   AND RH.PO_ID = RD.PO_ID  
  JOIN T1 
    ON RH.PO_ID = T1.PONumber 
  Where  RD.MATCH_SET_ID = 0  
  GROUP BY RH.PO_ID 
),
T3 (PONumber, InvoiceTotalCost, ReceiptTotalCost)
AS 
(
  SELECT T1.PONumber, 
    Sum(T1.InvoiceTotalCost) , 
    T2.ReceiptTotalCost  
  FROM T1 
  JOIN T2 
    ON T1.PONumber = T2.PONumber 
  GROUP BY T1.PONumber, T2.ReceiptTotalCost 
),
T4 (Vendor, APMatchType) 
AS 
( 
  Select VENDOR_ID AS Vendor, 
    AP_MATCH_TYP AS APMatchType  
  From  MRSQC.TAP125VEND_ASSIGN VA, MRSQC.TMS402VH_VENDOR V  
  Where UCASE(VA.MATCH_USER_ID) = 'PMANI'
  AND LEFT(V.VENDOR_NME, 1) BETWEEN VA.VEND_STRT_LTTR_CDE AND VA.VEND_END_LTTR_CDE 
)
SELECT DISTINCT 
  T1.PONumber, 
  T1.POStatus, 
  Min(T1.ProjectedDueDate) AS ProjectedDueDate,
  T1.Vendor, 
  T1.VendorName, 
  T3.InvoiceTotalCost,
  T3.ReceiptTotalCost ,
  IFNULL(T3.InvoiceTotalCost - T3.ReceiptTotalCost , 0) AS VarianceAmt,    
  ROUND(((DEC(T3.InvoiceTotalCost, 11,2) - DEC(T3.ReceiptTotalCost, 11, 2)) / DEC(T3.InvoiceTotalCost,11,2) * 100), 2) AS VariancePerct, 
  T1.APMatchType as APMatchType  
FROM T1 
JOIN T4 
  ON T1.Vendor = T4.Vendor
 AND T1.APMatchType = T4.APMatchType  
JOIN T3 
  ON T1.PONumber = T3.PONumber   
GROUP BY T1.PONumber,T1.POStatus,T1.Vendor, T1.VendorName, T3.InvoiceTotalCost ,T3.ReceiptTotalCost , T1.APMatchType  
ORDER BY ProjectedDueDate,  T1.PONumber

Open in new window

0
 
mani_saiAuthor Commented:
Thanks.

i modifed as per your suggestion:

i took T4 out and used it in T1.

i am getting this error:

 SQL0199N  The use of the reserved word "DISTINCT" following "" is not valid.  Expected tokens may include:  "AS (".  SQLSTATE=42601



Here is my SQL:
WITH    
T1 (PONumber,Store, POStatus,ProjectedDueDate,Vendor,VendorName,InvoiceTotalCost, APMatchType)
AS 
(    
  SELECT IH.PO_ID AS PONumber, IH.LOC_ID, IFNULL(MIN(VHG.PO_STATUS_CDE), 'AC') AS POStatus,  
    MIN(IH.INV_PROJECTED_DTE) AS ProjectedDueDate,    IH.VENDOR_ID AS Vendor, MIN(V.VENDOR_NME) AS VendorName,  
    Sum(IH.MDSE_COST_AMT) AS InvoiceTotalCost, MIN(PH.AP_MATCH_TYP) AS AP_MATCH_TYP    
  FROM MRSQC.TAP405INVOICE_HDR IH   
  INNER JOIN MRSQC.TAP100TRANS_CODE TC 
    ON IH.TRAN_CDE = TC.TRAN_CDE   
  INNER JOIN MRSQC.VPO_HEADER_GLOBAL VHG 
    ON IH.VENDOR_ID = VHG.VENDOR_ID AND IH.PO_ID = VHG.PO_ID    
  INNER JOIN MRSQC.TAP300PO_HDR PH 
    ON IH.PO_ID = PH.PO_ID   
  INNER JOIN MRSQC.TAP125VEND_ASSIGN VA
   ON PH.AP_MATCH_TYP = VA.AP_MATCH_TYP  
  LEFT OUTER JOIN MRSQC.TMS402VH_VENDOR V
    ON IH.VENDOR_ID = V.VENDOR_ID   
  WHERE IH.ROW_INSERT_TS > TIMESTAMP(CURRENT DATE - 12 MONTHS,'00.00.00') 
      AND IH.INV_STATUS_CDE = 'MCHRDY' 
    AND TC.MATCH_FLG = 'Y'  
    And PH.AP_MATCH_TYP = 'MER' AND UCASE(VA.MATCH_USER_ID) = 'PMANI'
  AND LEFT(V.VENDOR_NME, 1) BETWEEN VA.VEND_STRT_LTTR_CDE AND VA.VEND_END_LTTR_CDE 
  GROUP BY IH.PO_ID, IH.LOC_ID, IH.VENDOR_ID 
),
T2 (PONumber,ReceiptTotalCost)
AS 
(    
  SELECT RH.PO_ID AS PONumber,  
    SUM(RD.LOC_RECV_QTY * RD.VSTYL_DTL_MRKT_AMT) AS ReceiptTotalCost    
  FROM MRSQC.TAP310RCPT_HDR RH 
  JOIN MRSQC.TAP311RCPT_DTL RD 
    ON RH.RECEIPT_ID = RD.RECEIPT_ID 
   AND RH.PO_ID = RD.PO_ID  
  JOIN T1 
    ON RH.PO_ID = T1.PONumber 
  Where  RD.MATCH_SET_ID = 0  
  GROUP BY RH.PO_ID 
),
T3 (PONumber,InvoiceTotalCost, ReceiptTotalCost)
AS 
(
  SELECT T1.PONumber,  
    Sum(T1.InvoiceTotalCost) , 
    T2.ReceiptTotalCost  
  FROM T1 
  JOIN T2 
    ON T1.PONumber = T2.PONumber 
  GROUP BY T1.PONumber, T2.ReceiptTotalCost 
),

SELECT DISTINCT 
  T1.PONumber, 
  T1.POStatus, 
  Min(T1.ProjectedDueDate) AS ProjectedDueDate,
  T1.Vendor, 
  T1.VendorName, 
  T3.InvoiceTotalCost,
  T3.ReceiptTotalCost ,
  IFNULL(T3.InvoiceTotalCost - T3.ReceiptTotalCost , 0) AS VarianceAmt,    
  ROUND(((DEC(T3.InvoiceTotalCost, 11,2) - DEC(T3.ReceiptTotalCost, 11, 2)) / DEC(T3.InvoiceTotalCost,11,2) * 100), 2) AS VariancePerct, 
  T1.APMatchType as APMatchType  
FROM T1 JOIN T3 ON T1.PONumber = T3.PONumber   
GROUP BY T1.PONumber,T1.POStatus,T1.Vendor, T1.VendorName, T3.InvoiceTotalCost ,T3.ReceiptTotalCost , T1.APMatchType  
ORDER BY T1.ProjectedDueDate,  T1.PONumber

Open in new window

0
 
mani_saiAuthor Commented:
Never mind, i figured out the error:

but still the query is timing out after 47 seconds...

here is my modified query:


WITH    
T1 (PONumber,Store, POStatus,ProjectedDueDate,Vendor,VendorName,InvoiceTotalCost, APMatchType)
AS 
(    
  SELECT IH.PO_ID AS PONumber, IH.LOC_ID, IFNULL(MIN(VHG.PO_STATUS_CDE), 'AC') AS POStatus,  
    MIN(IH.INV_PROJECTED_DTE) AS ProjectedDueDate,    IH.VENDOR_ID AS Vendor, MIN(V.VENDOR_NME) AS VendorName,  
    Sum(IH.MDSE_COST_AMT) AS InvoiceTotalCost, MIN(PH.AP_MATCH_TYP) AS AP_MATCH_TYP    
  FROM MRSQC.TAP405INVOICE_HDR IH   
  INNER JOIN MRSQC.TAP100TRANS_CODE TC 
    ON IH.TRAN_CDE = TC.TRAN_CDE   
  INNER JOIN MRSQC.VPO_HEADER_GLOBAL VHG 
    ON IH.VENDOR_ID = VHG.VENDOR_ID AND IH.PO_ID = VHG.PO_ID    
  INNER JOIN MRSQC.TAP300PO_HDR PH 
    ON IH.PO_ID = PH.PO_ID   
  INNER JOIN MRSQC.TAP125VEND_ASSIGN VA
   ON PH.AP_MATCH_TYP = VA.AP_MATCH_TYP  
  LEFT OUTER JOIN MRSQC.TMS402VH_VENDOR V
    ON IH.VENDOR_ID = V.VENDOR_ID   
  WHERE IH.ROW_INSERT_TS > TIMESTAMP(CURRENT DATE - 12 MONTHS,'00.00.00') 
      AND IH.INV_STATUS_CDE = 'MCHRDY' 
    AND TC.MATCH_FLG = 'Y'  
    And PH.AP_MATCH_TYP = 'MER' AND UCASE(VA.MATCH_USER_ID) = 'PMANI'
  AND LEFT(V.VENDOR_NME, 1) BETWEEN VA.VEND_STRT_LTTR_CDE AND VA.VEND_END_LTTR_CDE 
  GROUP BY IH.PO_ID, IH.LOC_ID, IH.VENDOR_ID 
),
T2 (PONumber,ReceiptTotalCost)
AS 
(    
  SELECT RH.PO_ID AS PONumber,  
    SUM(RD.LOC_RECV_QTY * RD.VSTYL_DTL_MRKT_AMT) AS ReceiptTotalCost    
  FROM MRSQC.TAP310RCPT_HDR RH 
  JOIN MRSQC.TAP311RCPT_DTL RD 
    ON RH.RECEIPT_ID = RD.RECEIPT_ID 
   AND RH.PO_ID = RD.PO_ID  
  JOIN T1 
    ON RH.PO_ID = T1.PONumber 
  Where  RD.MATCH_SET_ID = 0  
  GROUP BY RH.PO_ID 
),
T3 (PONumber,InvoiceTotalCost, ReceiptTotalCost)
AS 
(
  SELECT T1.PONumber,  
    Sum(T1.InvoiceTotalCost) , 
    T2.ReceiptTotalCost  
  FROM T1 
  JOIN T2 
    ON T1.PONumber = T2.PONumber 
  GROUP BY T1.PONumber, T2.ReceiptTotalCost 
)

SELECT DISTINCT
  T1.PONumber, 
  T1.POStatus, 
  Min(T1.ProjectedDueDate) AS ProjectedDueDate,
  T1.Vendor, 
  T1.VendorName, 
  T3.InvoiceTotalCost,
  T3.ReceiptTotalCost ,
  IFNULL(T3.InvoiceTotalCost - T3.ReceiptTotalCost , 0) AS VarianceAmt,    
  ROUND(((DEC(T3.InvoiceTotalCost, 11,2) - DEC(T3.ReceiptTotalCost, 11, 2)) / DEC(T3.InvoiceTotalCost,11,2) * 100), 2) AS VariancePerct, 
  T1.APMatchType as APMatchType  
FROM T1 JOIN T3 ON T1.PONumber = T3.PONumber   
GROUP BY T1.PONumber,T1.POStatus,T1.Vendor, T1.VendorName, T3.InvoiceTotalCost ,T3.ReceiptTotalCost , T1.APMatchType  
ORDER BY ProjectedDueDate,  T1.PONumber

Open in new window

0
 
mani_saiAuthor Commented:
is it possible to write without using CTE

i mean using subqueries..
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Writing as a CTE or subquery doesn't make a lot of difference.  It's just syntax as the DBMS has to do the same amount of work.  And neither the result of the CTE nor the subquery can be indexed, so no gain there.

0
 
Kent OlsenData Warehouse Architect / DBACommented:
That last query doesn't solve the underlying issue.  You've got duplicate PONumber values in T1 and T3, creating a Cartesian product when you join them.

The timeout that you're seeing (ASUTIME) is a system limit on resources.  Depending on the system load, you might hit the limit faster one time than another.  (That's what you're seeing.)

This is a lousy long term solution, but can you put the results of T1 and T3 into other tables, index them, and run a query against these indexed tables?

0
 
mani_saiAuthor Commented:
Thanks
let me talk to my DBA's and see if they can have staging tables with indexing options...
0
 
mani_saiAuthor Commented:
Thanks for all your help.

I made some changes to query by removing unwanted fields from group by clause.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now