Reports 2.5 Slow Query

From: Delphis Group Limited <richard.fairbarin_at_delphis-group.com>
Date: Thu, 11 Nov 1999 12:52:08 +0000
Message-ID: <YdamDDA4vrK4IwCT_at_delphis-group.com>

Hello,
[Quoted] [Quoted] I am writing a report to show details from 3 joined tables where the key [Quoted] 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
[Quoted] order by a.pv_no

The only trouble with this is that it is taking such a long, long time. [Quoted] However, the cos_job-run_cost table is the only available source for me [Quoted] to achieve the desired result. It has over 100,000 records and is making [Quoted] 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

Received on Thu Nov 11 1999 - 13:52:08 CET