Combined views were working, now report runs forever.

[dsaba]

I am using a combined view to pull information into Crystal Reports 8, from an Oracle 8 database. Several months ago the report processed very quickly, now it never returns and I never receive an error message. There was 10 months worth of data in the tables but now there is only three due to the new archive routine. Below are the views:
1.)create or replace view RPT_UNLINKEDAIDDID AS
Select
ha.SDT as SDT,
ha.FLC as HAFLC,
ha.FLN as HAFLN,
ha.FLT as HAFLT,
ha.STA as HASTA,
ha.TAR as HATAR,
ha.TER as HATER,
hd.FLC as HDFLC,
hd.FLN as HDFLN,
hd.FLT as HDFLT,
hd.STD as HDSTD,
hd.TAR as HDTAR,
hd.TER as HDTER
from HIST_ARR ha,HIST_DEP hd
where
ha.DID = hd.DID( + )and
ha.SST NOT IN 'SL' and
ha.DID = ' 0' and
ha.TER = 'T '
Union
Select
hd.SDT as SDT,
ha.FLC as HAFLC,
ha.FLN as HAFLN,
ha.FLT as HAFLT,
ha.STA as HASTA,
ha.TAR as HATAR,
ha.TER as HATER,
hd.FLC as HDFLC,
hd.FLN as HDFLN,
hd.FLT as HDFLT,
hd.STD as HDSTD,
hd.TAR as HDTAR,
hd.TER as HDTER
from
HIST_ARR ha,HIST_DEP hd
where
hd.AID = ha.AID( + ) and
hd.LKC = ' ' AND
hd.SST NOT IN 'SL' and
hd.AID = ' 0'and
hd.TER = 'T ';

2.)create or replace view RPT_LINKEDAIDDID AS
Select
ha.SDT as SDT,
ha.FLC as HAFLC,
ha.FLN as HAFLN,
ha.FLT as HAFLT,
ha.STA as HASTA,
ha.TAR as HATAR,
ha.TER as HATER,
hd.FLC as HDFLC,
hd.FLN as HDFLN,
hd.FLT as HDFLT,
hd.STD as HDSTD,
hd.TAR as HDTAR,
hd.TER as HDTER
from HIST_ARR ha,HIST_DEP hd
where
ha.DID = hd.DID( + )and
ha.SST NOT IN 'SL' and
ha.TER = 'T '
Union
Select
hd.SDT as SDT,
ha.FLC as HAFLC,
ha.FLN as HAFLN,
ha.FLT as HAFLT,
ha.STA as HASTA,
ha.TAR as HATAR,
ha.TER as HATER,
hd.FLC as HDFLC,
hd.FLN as HDFLN,
hd.FLT as HDFLT,
hd.STD as HDSTD,
hd.TAR as HDTAR,
hd.TER as HDTER
from
HIST_ARR ha,HIST_DEP hd
where
hd.AID = ha.AID( + )and
hd.LKC = ' ' AND
hd.SST NOT IN 'SL' and
hd.TER = 'T ';

3.)create or replace view RPT_COMBINEDAIDDID as
select * from RPT_UNLINKEDAIDDID
union
select * from RPT_LINKEDAIDDID;

Could the problem be my views, if so does anyone know how I could make these more efficient. Currently I am pulling arrivals and departures from a historical arrivals view and a historical departures view. There was a problem with the reports listing all arrivals that had departures and all arrivals without departures, and vice-versa for the departures table. The above combined views pulls all of that information. Or is there any other possiblities or issues I need to check before re-writing the views.

 

[kenhamady]

Have you confirmed that the view still runs OK in Oracle? How long does it take? Sounds like the new archive routine may have caused a problem. Crystal wouldn't have changed. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)