Hello,
I wrote a large query on an huge database. In a normal editor it runs under 7 sec to fetch all records.
If I run it with a TOracleQuery it take up to 45 sec to return. I only replaced some WHERE-condition with Variable which i fill by the programe. The type of this variables are DateTime.
In some other cases we had a performance-leck from 6 sec to 1 hour!!
Do you have any clou to speed up this query? The query you can find below.
Thanks a lot
SELECT
d.prod_info_id,
d.prod_info_name,
d.typ_kennung,
d.prodinfo_typ,
d.element_id,
d.element_kennung,
trunc(f.zeit,'dd') as datum,
sum(f.wert) as wert,
f.mat_id,
m.kurz_bez,
f.dimension,
a.kza_aggregation_id,
nk.materialnomenklatur
FROM
zo_kza_pi z,
fakten_prod f,
doku_pi_lovtyp d,
kza_aggregation a,
vw_materialnomenklatur nk,
material m
WHERE z.prod_info_id = f.prod_info_id
and f.prod_info_id = d.prod_info_id
and a.kza_aggregation_id = z.kza_aggregation_id
and f.mat_id = m.mat_id
and f.mat_id = nk.mat_id (+)
and f.prod_info_id = nk.prod_info_id (+)
and f.prod_info_id in (select mf.prod_info_id from massenfluss mf where mf.q_werkstrukturelement_id = a.werkstrukturelement_id )
and d.typ_kennung =2
and f.gueltig = 1
and f.zeit_anfang_ext between :Anfang and :Ende
and a.kza_aggregation_id = :kza_aggregation_id
group by
d.prod_info_id,
d.prod_info_name,
d.typ_kennung,
d.prodinfo_typ,
d.element_id,
d.element_kennung,
trunc(f.zeit,'dd'),
f.mat_id,
m.kurz_bez,
f.dimension,
a.kza_aggregation_id,
nk.materialnomenklatur
order by 1
