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