[sqr-users] RE: Query to SQR

[edwin.hommes@bluewin.ch]

>   1. Query to SQR (Raghvendra Dharwada)

Hi,

First, a well written SQL can beat any SQR or other tool. As a proven rule:
Minimize your trips to the database, so splitting your SQL into several smaller
ones only makes sense if the only alternative would be outer joins, unions
and so on.

Just from looking at your statement I'd suggest you take out one of the C.EFFDT
subqueries. once it has to be smaller than SYSDATE and once D.EFFDT. You
should combine that into one subquery. 
Also make sure following fields are indexed and that the DB Scheme has recently
been analyzed (Ask your DBA!!)
A.ACCOUNT  
A.ACCOUNTING_PERIOD
B.ACCOUNT
C.RANGE_FROM - C.RANGE_TO

The other fields in your query seem PS standard indexed fields and should
therefor be ok.

Hope this helps

>Hi All,
>
>I am trying to convert the following long running query (I would
>rather say, it times out when I run) to an SQR. Can anyone suggest me
>how to tune this query ? Also, if I incorporate the same query in an
>SQR, how do I split it? I am thinking of putting all the subqueries
>into different procedures and use respective variables. Is it the
>right way of splitting it? Please suggest me different ways of doing
>it or tuning the query. I appreciate your help.
>
>SELECT :1 , :2 , 'XXGL1234', A.BUSINESS_UNIT, A.LEDGER, A.ACCOUNT,
>A.DEPTID, A.AFFILIATE, SUM( A.POSTED_TOTAL_AMT)
>  FROM PS_LEDGER A
>  WHERE A.ACCOUNT = (SELECT B.ACCOUNT
>  FROM PS_GL_ACCOUNT_TBL B, PSTREELEAF C, PSTREENODE D
>  WHERE B.ACCOUNT = A.ACCOUNT
>     AND B.EFFDT =
>        (SELECT MAX(B_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL B_ED
>        WHERE B.SETID = B_ED.SETID
>          AND B.ACCOUNT = B_ED.ACCOUNT
>          AND B_ED.EFFDT <= SYSDATE)
>     AND B.ACCOUNT BETWEEN C.RANGE_FROM AND C.RANGE_TO
>     AND C.EFFDT =
>        (SELECT MAX(C_ED.EFFDT) FROM PSTREELEAF C_ED
>        WHERE C.SETID = C_ED.SETID
>          AND C.SETCNTRLVALUE = C_ED.SETCNTRLVALUE
>          AND C.TREE_NAME = C_ED.TREE_NAME
>          AND C_ED.EFFDT <= SYSDATE)
>     AND C.TREE_NAME = 'ACCOUNT_ROLLUP'
>     AND C.SETID = D.SETID
>     AND C.TREE_NAME = D.TREE_NAME
>     AND C.TREE_NODE_NUM = D.TREE_NODE_NUM
>     AND D.EFFDT =
>        (SELECT MAX(D_ED.EFFDT) FROM PSTREENODE D_ED
>        WHERE D.SETID = D_ED.SETID
>          AND D.SETCNTRLVALUE = D_ED.SETCNTRLVALUE
>          AND D.TREE_NAME = D_ED.TREE_NAME
>          AND D_ED.EFFDT <= SYSDATE)
>     AND D.TREE_NODE IN ('PROFITLOSS','BALSHEET')
>     AND B.SETID = 'MODEL'
>     AND C.EFFDT =
>        (SELECT MAX(C_ED.EFFDT) FROM PSTREELEAF C_ED
>        WHERE C.SETID = C_ED.SETID
>          AND C.SETCNTRLVALUE = C_ED.SETCNTRLVALUE
>          AND C.TREE_NAME = C_ED.TREE_NAME
>          AND C_ED.EFFDT <= D.EFFDT))
>     AND A.FISCAL_YEAR = :1
>     AND A.ACCOUNTING_PERIOD BETWEEN 0 AND :2
>     AND A.LEDGER = 'ACTUALS'
>  GROUP BY  :1 ,  :2 ,  'XXGL1234',  A.BUSINESS_UNIT,  A.LEDGER, 
>A.ACCOUNT,  A.DEPTID,  A.AFFILIATE
>
>Thanks,
>Raghav


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users