Re: Reports 2.5 Slow Query

You might consider creating a new table initially containing only the records from cos_job_run_cost b where b.job_cost_type in (10,21,22,23,24,25,26,27,29,30) and having a column to hold just the substr(b.job_no,3) (so that you could index on this column) and whatever other columns are necessary for your report (looks like a date column might be needed). You could then use database triggers to insert a record into this table whenever an appropriate record was added to the cos_job_run_cost table, thus keeping them in sync.

In article <YdamDDA4vrK4IwCT_at_delphis-group.com>,   Delphis Group Limited <richard.fairbarin_at_delphis-group.com> wrote:
> Hello,
> I am writing a report to show details from 3 joined tables where the
key
> field does not exist in a fourth table. I have determined a way to
> achieve this but am not sure if there is a better way.
>
> Here is the query that I use:
>
> select d.pv_no,
> d.pv_type,
> d.pv_marker,
> d.pv_desc,
> a.recovery_s,
> a.recovery_we,
> a.pv_hours_s,
> a.pv_hours_we,
> e.depot_name
> from cos_recovery a, cos_plant_vehicle d, cos_depot e
> where a.pv_no = d.pv_no
> and d.depot_no = e.depot_no
> and a.pv_no not in (select substr(b.job_no,3) from
> cos_job_run_cost b where b.job_cost_type in
(10,21,22,23,24,25,26,27,29,
> 30) &p_date_clause2) &p_rec_where
> order by a.pv_no
>
> The only trouble with this is that it is taking such a long, long
time.
> However, the cos_job-run_cost table is the only available source for
me
> to achieve the desired result. It has over 100,000 records and is
making
> the testing of this report a very slow process (not to mention the
> forthcoming training!!).
>
> If you have any better ideas than this please feel free to voice them.
>
> Thanks
>
> R.W. Fairbairn
> --
> Delphis Group Limited
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 11 1999 - 17:56:03 CET