Hi all

I had a simple query but it just became lengthier and I had to add a few Sub Selects to get to the data.  This has made it slower.  The query I have pasted at the bottom of this post.

Essentially they are sets of 2 sub selects, one for the percentage value and the second for caculation based amount, there are four sets with only the IN () being different for the sets

Please advise

Thanks and regards
Bhavbhuti

The plan and time taken is here
PLAN (HEIR D INDEX (FK_MITEMS_IPID))
PLAN JOIN (SSIF INDEX (FK_SSALEINVOICEFOOTER_IPID), MA INDEX (PK_MACCOUNTS_IID))
PLAN JOIN (SSIF INDEX (FK_SSALEINVOICEFOOTER_IPID), MA INDEX (PK_MACCOUNTS_IID))
PLAN JOIN (SSIF INDEX (FK_SSALEINVOICEFOOTER_IPID), MA INDEX (PK_MACCOUNTS_IID))
PLAN JOIN (SSIF INDEX (FK_SSALEINVOICEFOOTER_IPID), MA INDEX (PK_MACCOUNTS_IID))
PLAN JOIN (SSIF INDEX (FK_SSALEINVOICEFOOTER_IPID), MA INDEX (PK_MACCOUNTS_IID))
PLAN JOIN (SSIF INDEX (FK_SSALEINVOICEFOOTER_IPID), MA INDEX (PK_MACCOUNTS_IID))
PLAN JOIN (SSIF INDEX (FK_SSALEINVOICEFOOTER_IPID), MA INDEX (PK_MACCOUNTS_IID))
PLAN JOIN (SSIF INDEX (FK_SSALEINVOICEFOOTER_IPID), MA INDEX (PK_MACCOUNTS_IID))
PLAN (DATEDMY NATURAL)
PLAN SORT (MERGE (SORT (JOIN (HEIR P NATURAL, HEIR GTT INDEX (PK_MITEMS_IID))
PLAN (HEIR P2 INDEX (FK_MITEMS_IPID))), SORT (JOIN (JOIN (JOIN (SSIITEM NATURAL, TSI INDEX (PK_TSALEINVOICE_IID)), MACCT INDEX (PK_MACCOUNTS_IID)), MCITY INDEX (PK_MLOOKUPS_IID)))))


Executing statement...
Statement executed (elapsed time: 0.000s).
1938766 fetches, 0 marks, 7836 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 285347 index, 263874 seq.
Delta memory: 181228 bytes.
Total execution time: 1.584s
Script execution finished.


The query:

WITH RECURSIVE Heir(iID, iPK) AS
(SELECT p.iID, p.iID AS iPK FROM mItems p JOIN mItems GTT
ON p.iID  = GTT.iID WHERE (p.lHierarchialStock = 1
OR NOT EXISTS (SELECT 1
FROM mItems d
WHERE d.iPID = p.iID)) UNION ALL
SELECT H.iID, p2.iID AS iPK FROM mItems p2
JOIN Heir H on p2.iPID = H.iPK) SELECT 2                                                                   AS iOrder
 , sSIItem.iID                                                                AS iPID
 , tSI.iID                                                                    AS iGPID
 , '%0'                                      AS cPSource
 , tSI.cBk                                                                    AS cBk
 , tSI.iNo                                                                    AS iNo
 , tSI.tDt                                                                    AS tDt
 , Heir.iID                                                                   AS iItemID
 , CAST(0 AS QTY)                                                             AS bAdd
 , sSIItem.bQty                                                               AS bLess
 , '%0'                                      AS cSource
 , 'Sale Invoice no. ' || CASE WHEN tSI.cBK = '' THEN '' ELSE TRIM(tSI.cBK) || '/' END || TRIM(CAST(tSI.iNo AS CHARACTER(10))) || ' dtd. ' || (SELECT * FROM DateDMY(tSI.tDt)) || ', ' || TRIM(mAcct.cName) || ', ' || TRIM(COALESCE(mCity.vValue, ''))                                                            AS mNarration
 , 'Sale Invoice'                                 AS cDocName
 , TRIM(LEADING '/'
   FROM TRIM(tSI.cBk) || '/') || TRIM(CAST(tSI.iNo AS DOCNO)) AS cDocNo
 , tSI.tDt                                                                    AS tDocDt
 , tSI.iBuyerID                                                               AS iDocAcctID
 , tSI.cDocType                                                               AS cDocType
 , IIF(sSIItem.bMRP > 0
   , IIF(sSIItem.nAbatementPerc > 0
   , ROUND(sSIItem.bMRP - ROUND(sSIItem.bMRP * sSIItem.nAbatementPerc / 100, 2), 2)
   , sSIItem.bMRP)
   , sSIItem.bRate)                                           AS bAssRate
 , ROUND(IIF(sSIItem.bMRP > 0
   , IIF(sSIItem.nAbatementPerc > 0
   , ROUND(sSIItem.bMRP - ROUND(sSIItem.bMRP * sSIItem.nAbatementPerc / 100, 2), 2)
   , sSIItem.bMRP)
   , sSIItem.bRate) * sSIItem.bQty, 2)                AS bAssAmt
 , (SELECT sSIF.nPerc
FROM sSaleInvoiceFooter sSIF
JOIN mAccounts mA
ON mA.iID = sSIF.iAccountID
AND mA.cTypeID IN ('PLA', 'CENVAT', 'RG23C', 'RG23D', 'SERVTAX')
WHERE sSIF.iPID = tSI.iID)                                                           AS nBEDPerc
 , ROUND((sSIItem.nAmtPerc * (SELECT sSIF.bAmt
  FROM sSaleInvoiceFooter sSIF
  JOIN mAccounts mA
  ON mA.iID = sSIF.iAccountID
  AND mA.cTypeID IN ('PLA', 'CENVAT', 'RG23C', 'RG23D', 'SERVTAX')
  WHERE sSIF.iPID = tSI.iID)) / 100, 2)                      AS bBEDAmt
 , (SELECT sSIF.nPerc
FROM sSaleInvoiceFooter sSIF
JOIN mAccounts mA
ON mA.iID = sSIF.iAccountID
AND mA.cTypeID IN ('PLAECESS', 'CENVATECESS', 'RG23CECESS', 'RG23DECESS', 'SERVTAXECESS')
WHERE sSIF.iPID = tSI.iID)                                                           AS nCessPerc
 , ROUND((sSIItem.nAmtPerc * (SELECT sSIF.bAmt
  FROM sSaleInvoiceFooter sSIF
  JOIN mAccounts mA
  ON mA.iID = sSIF.iAccountID
  AND mA.cTypeID IN ('PLAECESS', 'CENVATECESS', 'RG23CECESS', 'RG23DECESS', 'SERVTAXECESS')
  WHERE sSIF.iPID = tSI.iID)) / 100, 2)                      AS bCessAmt
 , (SELECT sSIF.nPerc
FROM sSaleInvoiceFooter sSIF
JOIN mAccounts mA
ON mA.iID = sSIF.iAccountID
AND mA.cTypeID IN ('PLASHCESS', 'CENVATSHCESS', 'RG23CSHCESS', 'RG23DSHCESS', 'SERVTAXSHCESS')
WHERE sSIF.iPID = tSI.iID)                                                           AS nSHCessPer
 , ROUND((sSIItem.nAmtPerc * (SELECT sSIF.bAmt
  FROM sSaleInvoiceFooter sSIF
  JOIN mAccounts mA
  ON mA.iID = sSIF.iAccountID
  AND mA.cTypeID IN ('PLASHCESS', 'CENVATSHCESS', 'RG23CSHCESS', 'RG23DSHCESS', 'SERVTAXSHCESS')
  WHERE sSIF.iPID = tSI.iID)) / 100, 2)                      AS bSHCessAmt
 , (SELECT sSIF.nPerc
FROM sSaleInvoiceFooter sSIF
JOIN mAccounts mA
ON mA.iID = sSIF.iAccountID
AND mA.cTypeID IN ('CENVATADDLDUTY')
WHERE sSIF.iPID = tSI.iID)                                                           AS nAEDPerc
 , ROUND((sSIItem.nAmtPerc * (SELECT sSIF.bAmt
  FROM sSaleInvoiceFooter sSIF
  JOIN mAccounts mA
  ON mA.iID = sSIF.iAccountID
  AND mA.cTypeID IN ('CENVATADDLDUTY')
  WHERE sSIF.iPID = tSI.iID)) / 100, 2)                      AS bAEDAmt
FROM tSaleInvoice tSI
 JOIN sSaleInvoiceItem sSIItem
 ON sSIItem.iPID = tSI.iID
AND sSIItem.bQty <> 0
 LEFT JOIN mAccounts mAcct
 ON mAcct.iID = tSI.iBuyerID
 LEFT JOIN mLookups mCity
 ON mCity.iID = mAcct.iCityID JOIN Heir
ON sSIItem.iItemID = Heir.iPK WHERE tSI.tDt BETWEEN CAST('2014-09-19 00:00:00' AS SRDT) AND CAST('2014-09-19 00:00:00' AS TIMESTAMP) AND tSI.cDocType NOT LIKE 'PROFORMA%'
 AND tSI.cDocType NOT LIKE 'CANCELLED%'
 AND tSI.cDocType NOT LIKE 'HOMESUPPLI%' ORDER BY tSI.tDt, tSI.cBk, tSI.iNo, iPID