Re: How do you like this SQL?

From: John Hurley <johnthehurley_at_gmail.com>
Date: Mon, 17 Dec 2012 06:27:00 -0800 (PST)
Message-ID: <4aa4ec36-a2f4-44d7-a556-be485e46d241_at_u19g2000yqj.googlegroups.com>

Mladen:

# The first stab at solving the problem would, in my opinion, look
something like this:

Changed your suggestion just a bit to fix syntax but it was dead on target ...

(WITH INNER AS
     ( SELECT PO_ID, LINE_ITEM_PO, COMMENT_PO, DATE_ADD_COMMENT, ROW_TIMESTAMP,

      MAX(ROW_TIMESTAMP) OVER (PARTITION BY PO_ID,LINE_ITEM_PO) AS REC
      FROM schema_name.PO_COMMENT
      WHERE   TYPE_PO_COMMENT='LE' )

SELECT PO_ID, LINE_ITEM_PO, COMMENT_PO, DATE_ADD_COMMENT FROM INNER
WHERE ROW_TIMESTAMP=REC ) TABLE_ALIAS,
# Generally speaking, this would go through the table only once, which
makes it faster. Nevertheless, this too will hit the limit when the table outgrows the threshold.

Well it turns out that existing multiple column index ( not a new one ) on PO_ID, LINE_ITEM_PO, and TYPE_PO_COMMENT is now able to be used effectively ... so no limits.

Still some big other tables involved elsewhere in the multiple other outer joins ( inventory, orders, line items etc ) but this is a huge improvement.

15 minute hog now runs in 20 seconds.

I owe you a beer Mladen!

Thanks to everyone else in this thread also for keeping me honest in eventually providing a solution. Received on Mon Dec 17 2012 - 15:27:00 CET