|
On Thu,
Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: > >
> On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman <frank@xxxxxxxxxxxx>
wrote:
>
>
>
>
I did not get any response to this, but I am still persevering, and
feel
>
>
that I am getting closer. Instead of waiting 26 minutes for a result,
I
>
>
realise that I can learn a lot by using EXPLAIN. This is what I have
found
>
>
out.
>
>
Something
is not adding up here. Can you EXPLAIN ANALYZE the 26 minute
query?
>
I’m
working on it, but my machine is playing up and it is getting late, so I will
try again tomorrow. A
passing comment – the 26 minute query is more complex, so will need some
explaining (no pun intended). I was hoping that my simplified example would
illustrate what I think is the problem. Anyway,
here is the query - SELECT q.cust_row_id,
SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
) AS "balance_curr AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-08-31' AND q.tran_date >
'2015-07-31' THEN q.balance ELSE 0 END
) AS "balance_30 AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-07-31' AND q.tran_date >
'2015-06-30' THEN q.balance ELSE 0 END
) AS "balance_60 AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-06-30' AND q.tran_date >
'2015-05-31' THEN q.balance ELSE 0 END
) AS "balance_90 AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-05-31' THEN q.balance ELSE 0
END
) AS "balance_120 AS [DECTEXT]"
FROM
(SELECT
due_trans.cust_row_id,
due_trans.tran_date,
trans_due.amount_cust +
COALESCE((SELECT
SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
FROM
prop.ar_trans_alloc trans_alloc
LEFT
JOIN prop.ar_trans alloc_trans ON
alloc_trans.tran_type = trans_alloc.tran_type
AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
WHERE
trans_alloc.due_row_id = trans_due.row_id
AND alloc_trans.tran_date <= '2015-09-30'
),
0)
AS balance
FROM prop.ar_trans_due trans_due
LEFT JOIN prop.ar_trans due_trans ON
due_trans.tran_type =
trans_due.tran_type
AND due_trans.tran_row_id =
trans_due.tran_row_id
WHERE due_trans.tran_date <= '2015-09-30'
) AS q
GROUP BY q.cust_row_id
ORDER BY q.cust_row_id;
I will report back with the EXPLAIN ANALYSE tomorrow.
Frank
|
