Thursday, December 13, 2012

this sql did not run very well

Part of a very long query put together by a developer and of course implemented in production. Lots of inline views ( not a bad idea many times ) ... big tables it was going against ... many of them. Still we have a pretty fast system ...

Things were still ok until you get to this part of the SQL:

 (SELECT POC.PO_ID, POC.LINE_ITEM_PO, POC.COMMENT_PO, POC.DATE_ADD_COMMENT FROM PO_COMMENT POC WHERE (POC.PO_ID,POC.LINE_ITEM_PO,POC.ROW_TIMESTAMP) IN (SELECT A.PO_ID,A.LINE_ITEM_PO,MAX(A.ROW_TIMESTAMP) FROM PO_COMMENT A WHERE A.TYPE_PO_COMMENT='LE' GROUP BY A.PO_ID, A.LINE_ITEM_PO) ) T193

So were are operating against one table PO_COMMENT ... lots of rows in there.

The WHERE clause takes multiple columns simultaneously and looks for that combination in ... where else ... the same blank ... table???

Using MAX(A.ROW_TIMESTAMP) to get the most recent one.

How might one fix this bad part of the SQL? How about not doing an IN against the same table? Just one of those days eh?

UPDATE:  New blog post shows a fix using analytic rewrite ... rewritten

2 comments:

  1. Teach your developer analytic functions? Maybe if he learns well, you get fewer of "those days" in the future? ;-)

    (
    SELECT PO_ID
    , LINE_ITEM_PO
    , COMMENT_PO
    , DATE_ADD_COMMENT
    FROM (
    SELECT POC.PO_ID
    , POC.LINE_ITEM_PO
    , POC.COMMENT_PO
    , POC.DATE_ADD_COMMENT
    , ROW_NUMBER() OVER (
    PARTITION BY POC.PO_ID
    , POC.LINE_ITEM_PO
    ORDER BY POC.ROW_TIMESTAMP DESC
    ) RN
    FROM PO_COMMENT POC
    WHERE POC.TYPE_PO_COMMENT='LE'
    )
    WHERE RN = 1
    ) T193

    :-)

    ReplyDelete
  2. Thanks Kim ( sorry did not see your comment ) yes right on target.

    Yes this old DBA also needs to spend some more time learning analytics but did come up with a solution ( thanks to Mladen Gogala sp? ) using analytics ... solution posted separately.

    The solution even was able to use PO_ID from other join and now does lookup using existing multi column index on PO_ID, LINE_ITEM_PO and TYPE_PO_COMMENT ...

    ReplyDelete