Re: [GENERAL] a JOIN to a VIEW seems slow

> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman <frank@chagford.com> 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;