jayco437
Premium
join:2001-08-11
El Paso, TX
 ·AT&T Southwest
| reply to dropshadow
Re: how to write SQL query
I don't know anything about DB2, but my first thought if I was doing this in Postgres would to analyze the table and check the indexes.
Can you change the select max() to a select.... order by comments.date desc limit 1, and notice any improvement? |
|
Ray
Mahnahmahna
Premium
join:2001-04-02
Mesa, AZ
edit:
May 11th, @04:47PM
| That's what I was thinking. It's slow because that subselect is re-run for every row in the outer query. First, I'd create an index on the comment date if there isn't one already and see if that becomes acceptable.
Secondly, I'd try to make a query of the latest comment for each invoice a one-time deal as part of the FROM clause using a table alias. I'd need to create some test data & play with it for a while to actually give you some specific SQL, but something along the lines of
SELECT i.blah, c.comment
FROM invoices i
JOIN (SELECT most recent comment for each invoice) c
ON (c.invoice_id = i.invoice_id)
--
ON DELETE CASCADE |
|
