Hi all

I have this CTE (see below using FlameRobin) which works wonderfully (thanks S.E.T.!). This is just one of the set of CTEs that are run on various tables, each taking about 16 secs on a few years old Core Two Duo. The cumulative effect is that the final dataset that is gathered takes about 20-25 mins. I use SQL Pass Through from my application for each such query, the return row I then amalgamate for reporting purposes. Presently using FB 2.1.4.18393 (planning to go to FB 3.0 when released).

My questions are thus. Can this CTE be still optimized? Some indexes that can help with speeding it up? Can I send one huge .sql file to FireBird with all the queries together but will it then return one big dataset that I can directly use in the app? Any suggestion that I have not been able to think with my limited experiences?

Please advise

Thanks and regards
Bhavbhuti


Starting transaction...
Preparing statement: 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 1 AS iOrder
, Heir.iID AS iPID
, Heir.iID AS iGPID
, ')0' AS cPSource
, CAST(' ' AS CHAR(5)) AS cBk
, CAST(0 AS INTEGER) AS iNo
, CAST('2013-04-01 00:00:00' AS TIMESTAMP) AS tDt
, Heir.iID AS iItemID
, SUM(sGRNItem.bQty) AS bAdd
, CAST(0 AS DOUBLE PRECISION) AS bLess
, 'O0' AS cSource
, 'Opening Stock' AS mNarration
, CAST('Opening Stock' AS CHAR(25)) AS cDocName
, CAST(' ' AS CHAR(15)) AS cDocNo
, CAST('2013-04-01 00:00:00' AS TIMESTAMP) AS tDocDt
, CAST(0 AS INTEGER) AS iDocAcctID FROM tGoodsRecvNote tGRN
JOIN sGoodsRecvNoteItem sGRNItem
ON sGRNItem.iPID = tGRN.iID
AND sGRNItem.bQty <> 0 JOIN Heir
ON sGRNItem.iItemID = Heir.iPK WHERE tGRN.tDt < CAST('2013-04-01 00:00:00' AS TIMESTAMP) GROUP BY Heir.iID

Statement prepared (elapsed time: 0.000s).
Field #01: . Alias:IORDER Type:INTEGER
Field #02: . Alias:IPID Type:INTEGER
Field #03: . Alias:IGPID Type:INTEGER
Field #04: . Alias:CPSOURCE Type:STRING(2)
Field #05: . Alias:CBK Type:STRING(5)
Field #06: . Alias:INO Type:INTEGER
Field #07: . Alias:TDT Type:TIMESTAMP
Field #08: . Alias:IITEMID Type:INTEGER
Field #09: . Alias:BADD Type:DOUBLE PRECISION
Field #10: . Alias:BLESS Type:DOUBLE PRECISION
Field #11: . Alias:CSOURCE Type:STRING(2)
Field #12: . Alias:MNARRATION Type:STRING(13)
Field #13: . Alias:CDOCNAME Type:STRING(25)
Field #14: . Alias:CDOCNO Type:STRING(15)
Field #15: . Alias:TDOCDT Type:TIMESTAMP
Field #16: . Alias:IDOCACCTID Type:INTEGER
PLAN (HEIR D NATURAL)
PLAN SORT (JOIN (JOIN (JOIN (HEIR P NATURAL, HEIR GTT INDEX (PK_MITEMS_IID))
PLAN (HEIR P2 NATURAL), SGRNITEM INDEX (FK_SGOODSRECVNOTEITEM_IITEMID), TGRN INDEX (PK_TGOODSRECVNOTE_IID))))


Executing statement...
Statement executed (elapsed time: 0.000s).
38450770 fetches, 2 marks, 3 reads, 2 writes.
0 inserts, 0 updates, 0 deletes, 2952 index, 18647924 seq.
Delta memory: 31976 bytes.
Total execution time: 16.172s
Script execution finished.