Re: FW: query optimization question

On Wed, 6 Nov 2002 terry@ashtonwoodshomes.com wrote:

> Actually, come to think of it, just the implementation of re-querying a
> temporary table could alone significantly improve performance, because the
> temp table would:
> a) have fewer records to scan on the subselects
> b) not require any joins

Yeah, that's what I was thinking.  However the example I gave was
bogus.  I realized that I needed to do more, then forgot before sending.

> > Maybe something like (untested):
> > create temp table defs as
> >  select agefunc(dt.days_old_start_date) as ageval,
> >   count(lots.lot_id) as lots from
> >   deficiency_table as dt, lots, deficiency_status as ds
> >   where dt.lot_id = lots.lot_id
> >   and lots.dividion_id=proj.division_id
> >   and lots.project_id=proj.project_id
> >   and dt.deficiency_status_id=ds.deficiency_status_id
> >   and ts.is_outstanding
> >   and dt.assigned_supplier_id='101690'
> >  group by ageval;

You'll almost certainly need to add projects as proj in the from clause,
proj.project_id in the select clause and group by (and possibly
division_id - I can't quite tell if that's a composite key).

> > -- same general thing for other repeated queries
> >
> > select project_id, marketing_name,
> >  (select sum(lots) from defs) as def_count,
> >  (select lots from defs where ageval=0) as def_count_less_30,

In these you'd want to limit it to the appropriate rows from defs
by project_id (and possibly division_id).