RE: [sqr-users] Query to SQR

[Bob Stone <bstone@fastenal.com>]

As is often the case with long running SQLs, there is probably a join to a
relatively large table that is not using any indexes.  It would be very hard
for someone on this SQR list to give specific tuning advice, because we are
unfamiliar with your exact tables (unless they are all PS delivered,
non-altered...in which case, I still don't know them because I have not
worked with GL).  Talk to your DBA about the explain plan to see if they can
find what join(s) are not using any index...or, in general, how you might
tune the statement.  You can probably create an index (or 2) that would help
the query run faster, or it might help to not have so many subselects.  

Usually you don't want to break up the SQLs and nest them in SQR, especially
more than once.  Because, unless you fixed the bad section of SQL anyway
(the full table scan), it will still go against all the tables just as
slowly...but with added processing and IO in between. 

Your best bet is to have your DBA set up an index on the slower table (or
help you make your join use an existing index), and then adding a hint so
that it uses that index to find those rows as quickly as possible. 

Also, the 'between' function is slightly slower than saying "and b.account
>= c.date_from and b.account <= c.date_to", and 'in' is slightly slower than
"and ( d.treenode = 'PROFITLOSS' or d.treenode = 'BALSHEET')" , but neither
of those should noticeably affect your query (although it may make a minor
improvements since you use them multiple times).  

Hope that helps.

Bob


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Raghvendra Dharwada
Sent: Tuesday, May 31, 2005 2:44 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Query to SQR


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