| Subject | Re: [firebird-support] Hierarchical SQL Query |
|---|---|
| Author | |
| Post date | 2017-06-13T15:24:25Z |
Hi Michał Kurczabiński,
Based on your suggestion for Hierarchical SQL Query i designed the below query, it is working,
but the problem is the execution time taken by the below query is almost 4 seconds.
WITH TBLCF AS (SELECT
TBLLEDGERMAST.LEDGERMASTID,
TBLLEDGERMAST.LINKLEDGERGROUPID,
TBLLEDGERMAST.LEDGERNAME,
iif(TBLTRANSMAST.TRANSAMOUNT < 0, 0, TBLTRANSMAST.TRANSAMOUNT) AS DRVAL,
iif(TBLTRANSMAST.TRANSAMOUNT > 0, 0, TBLTRANSMAST.TRANSAMOUNT) AS CRVAL
FROM
(SELECT
TBLTRANSMAST.LEDGERMASTID,
TBLTRANSMAST.REFSGUID
FROM
TBLTRANSMAST
INNER JOIN TBLLEDGERMAST ON TBLLEDGERMAST.LEDGERMASTID = TBLTRANSMAST.LEDGERMASTID
WHERE
(TBLLEDGERMAST.ISCASHGROUP = 1 OR
TBLLEDGERMAST.ISBANKGROUP = 1) AND
TBLTRANSMAST.TRANSDATE BETWEEN '01.04.2017' AND '30.04.2017') TBLNCF
INNER JOIN TBLLEDGERMAST ON TBLLEDGERMAST.LEDGERMASTID <> TBLNCF.LEDGERMASTID
INNER JOIN TBLTRANSMAST ON TBLLEDGERMAST.LEDGERMASTID = TBLTRANSMAST.LEDGERMASTID AND TBLTRANSMAST.REFSGUID =
TBLNCF.REFSGUID)
SELECT
parent.LEDGERGROUPID,
parent.LEDGERNAME,
parent.LEFTEXTENT,
parent.RIGHTEXTENT,
SUM(TBLCF.DRVAL),
SUM(TBLCF.CRVAL)
FROM
TBLLEDGERGROUP AS node,
TBLLEDGERGROUP AS parent,
TBLCF
WHERE
node.LEFTEXTENT BETWEEN parent.LEFTEXTENT AND parent.RIGHTEXTENT
AND NODE.LEDGERGROUPID=TBLCF.LINKLEDGERGROUPID
AND parent.ISPRIMARYGROUP <> 1
GROUP BY
parent.LEDGERGROUPID,
parent.LEDGERNAME,
parent.LEFTEXTENT,
parent.RIGHTEXTENT
ORDER BY
parent.LEFTEXTENT
When i execute the query for TBLCF alone , the total execution time is 0.236s (Total no of rows fetched is 178)
when i execute the main query without referring the TBLCF, Total execution time: 0.086s (Total no of rows fetched in 28).
But when i join the list with the main query, the execution time is almost 4 sec.
Please guide me how to make this query more efficient performance wise.