Re: Optimizing a VIEW

On Fri, 15 Aug 2008, Madison Kelly wrote:
>  Below I will post the VIEW and a sample of the query's EXPLAIN ANALYZE.
> Thanks for any tips/help/clue-stick-beating you may be able to share!

This query looks incredibly expensive:

>    SELECT
...
>    FROM
>        customer a,
>        history.customer_data b,
>        history.customer_data c,
>        history.customer_data d,
>        history.customer_data e,
>        history.customer_data f,
>        history.customer_data g,
>        history.customer_data h,
>        history.customer_data i,
>        history.customer_data j,
>        history.customer_data k,
>        history.customer_data l
>    WHERE
>        a.cust_id=b.cd_cust_id AND
>        a.cust_id=c.cd_cust_id AND
>        a.cust_id=d.cd_cust_id AND
>        a.cust_id=e.cd_cust_id AND
>        a.cust_id=f.cd_cust_id AND
>        a.cust_id=g.cd_cust_id AND
>        a.cust_id=h.cd_cust_id AND
>        a.cust_id=i.cd_cust_id AND
>        a.cust_id=j.cd_cust_id AND
>        a.cust_id=k.cd_cust_id AND
>        a.cust_id=l.cd_cust_id AND
...

I would refactor this significantly, so that instead of returning a wide
result, it would return more than one row per customer. Just do a single
join between customer and history.customer_data - it will run much faster.

Matthew

--
Here we go - the Fairy Godmother redundancy proof.
                                        -- Computer Science Lecturer