Re: FW: query optimization question

On Wed, 6 Nov 2002 terry(at)ashtonwoodshomes(dot)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).