Exécution lente d'une réquette

[Saif_24]

Salut, j'utilise Oracle comme une BD, ma requette est ci dessous:

select client_non_paye,nbj_client_non_paye,fort_non_paye,nbj_for_non_paye,
prix_pd,article ,cumulcredit,nom_credit,pay_client, client_ca,annee1, client1,annee2,client2,annee3,client3,
Janv1_ca,produit1,Fev1_ca,produit12,Mars1_ca,produit13,Avr1_ca,produit14,Mai1_ca,produit15,
Juin1_ca,produit16,Juill1_ca,produit17,Aout1_ca,produit18,Sept1_ca,produit19,Oct1_ca ,produit110 ,
Nov1_ca,produit111,Dec1_ca,produit112,ca1,som1,ann1,som2,ann2,som3,ann3,produit_fini,matiere_premiere,
date_stockage,date_livraison,nbj_stock_mp,date_production,date_fin_stock
from

(select distinct(cbp.name) as client_non_paye ,round(( sysdate - dateinvoiced)) as nbj_client_non_paye
from c_invoice ci
inner join c_bpartner cbp on cbp.c_bpartner_id = ci.c_bpartner_id
where cbp.isactive='Y'
and cbp.name not in
(select distinct(cbp.name) as nom_client
from c_invoice ci
inner join c_payment cp on ci.c_invoice_id =cp.c_invoice_id
inner join c_bpartner cbp on cbp.c_bpartner_id = ci.c_bpartner_id
where cbp.isactive='Y'
and ci.isactive='Y'
and cbp.iscustomer = 'Y'
)
and cbp.isactive='Y'
and ci.isactive='Y'
and cbp.iscustomer= 'Y'
)
,
(select distinct(cbp.name) as fort_non_paye ,round(( sysdate - dateinvoiced)) as nbj_for_non_paye
from c_invoice ci
inner join c_bpartner cbp on cbp.c_bpartner_id = ci.c_bpartner_id
where cbp.isactive='Y'
and cbp.name not in
(select distinct(cbp.name) nom_fornisseur
from c_invoice ci
inner join c_payment cp on ci.c_invoice_id =cp.c_invoice_id
inner join c_bpartner cbp on cbp.c_bpartner_id = ci.c_bpartner_id
where cbp.isactive='Y'
and ci.isactive='Y'
and cbp.isvendor = 'Y'
)
and cbp.isactive='Y'
and ci.isactive='Y'
and cbp.isvendor= 'Y')
,

(select sum( qtyentered * priceactual ) as prix_pd, mp.name as article from c_invoiceline ci
inner join m_product mp on mp.m_product_id = ci.m_product_id
inner join c_invoice cn on cn.c_invoice_id = ci.c_invoice_id
inner join c_bpartner cbp on cbp.c_bpartner_id = cn.c_bpartner_id
where ci.isactive='Y'
and cbp.iscustomer = 'Y'
and mp.ispurchased = 'N'
group by mp.name)
,

(
SELECT cv.cumulcredit , cbp.name nom_credit FROM CreditVendor cv
inner join c_bpartner cbp on cbp.c_bpartner_id = cv.c_bpartner_id
where cbp.iscustomer ='Y'
and cbp.isactive = 'Y'
--order by cbp.name
)

,
(
SELECT cbp.name as pay_client, grandtotal as client_ca
FROM C_CA_V ca
inner join c_bpartner cbp on cbp.c_bpartner_id = ca.c_bpartner_id
where cbp.iscustomer ='Y'
and cbp.isactive = 'Y'
and ca.isactive= 'Y'
order by cbp.name),


(select * from
(select sum(TOTALLINES) as annee1, bp.name as client1 from c_invoice ci
inner join c_bpartner bp on bp.c_bpartner_id =ci.c_bpartner_id
where to_char(ci.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and ci.isactive='Y'
and bp.ISCUSTOMER='Y'
group by bp.name
),


(select sum(TOTALLINES) as annee2, bp.name as client2 from c_invoice ci
inner join c_bpartner bp on bp.c_bpartner_id =ci.c_bpartner_id
where to_char(ci.DATEINVOICED,'YYYY') = to_char(sysdate-365,'YYYY')
and ci.isactive='Y'
and bp.ISCUSTOMER='Y'
group by bp.name
),


(select sum(TOTALLINES) as annee3, bp.name as client3 from c_invoice ci
inner join c_bpartner bp on bp.c_bpartner_id =ci.c_bpartner_id
where to_char(ci.DATEINVOICED,'YYYY') = to_char(sysdate-(2*365),'YYYY')
and ci.isactive='Y'
and bp.ISCUSTOMER='Y'
group by bp.name
)
),

(select * from
(select sum(LINENETAMT) as Janv1_ca,mp.name as produit1 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '01'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
),


(select sum(LINENETAMT) as Fev1_ca,mp.name as produit12 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '02'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
),

(select sum(LINENETAMT) as Mars1_ca,mp.name as produit13 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '03'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
),


(select sum(LINENETAMT) as Avr1_ca,mp.name as produit14 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '04'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
),


(select sum(LINENETAMT) as Mai1_ca,mp.name as produit15 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '05'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
),


(select sum(LINENETAMT) as Juin1_ca,mp.name as produit16 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '06'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
),


(select sum(LINENETAMT) as Juill1_ca,mp.name as produit17 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '07'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
),

(select sum(LINENETAMT) as Aout1_ca,mp.name as produit18 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '08'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
),


(select sum(LINENETAMT) as Sept1_ca,mp.name as produit19 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '09'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
)
,
(select sum(LINENETAMT) as Oct1_ca ,mp.name as produit110 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '10'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
),


(select sum(LINENETAMT) as Nov1_ca,mp.name as produit111 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '11'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
),
(select sum(LINENETAMT) as Dec1_ca,mp.name as produit112 from c_invoiceLine cil
inner join m_product mp on mp.m_product_id = cil.m_product_id
inner join c_invoice fa on cil.c_invoice_id = fa.c_invoice_id
inner join c_bpartner bp on bp.c_bpartner_id =fa.c_bpartner_id
where
to_char(fa.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and
to_char(fa.DATEINVOICED,'MM') = '12'
and isactive='Y'
and bp.ISCUSTOMER='Y'
group by mp.name
)
),

(select * from
(select sum(PRICESTD) as ca1 from M_PRODUCTPRICE pr
inner join m_product mp on mp.m_product_id = pr.m_product_id
inner join M_STORAGE st on st.m_product_id= mp.m_product_id
inner join M_LOCATOR lo on lo.M_LOCATOR_ID = st.M_LOCATOR_ID
inner join M_WAREHOUSE wr on wr.M_WAREHOUSE_ID = lo.M_WAREHOUSE_ID
where
isactive='Y'
group by wr.name
)
),

(select * from
(select sum(TOTALLINES) as som1,to_char(sysdate ,'YYYY')as ann1 from c_invoice ci
inner join c_bpartner bp on bp.c_bpartner_id =ci.c_bpartner_id
where to_char(ci.DATEINVOICED,'YYYY') = to_char(sysdate,'YYYY')
and ci.isactive='Y'
and bp.ISCUSTOMER='Y'
),
(select sum(TOTALLINES) as som2,to_char(sysdate - 365 ,'YYYY')as ann2 from c_invoice ci
inner join c_bpartner bp on bp.c_bpartner_id =ci.c_bpartner_id
where to_char(ci.DATEINVOICED,'YYYY') = to_char(sysdate - 365,'YYYY')
and ci.isactive='Y'
and bp.ISCUSTOMER='Y'
),
(select sum(TOTALLINES) as som3,to_char(sysdate - (365 * 2),'YYYY')as ann3 from c_invoice ci
inner join c_bpartner bp on bp.c_bpartner_id =ci.c_bpartner_id
where to_char(ci.DATEINVOICED,'YYYY') = to_char(sysdate - (365 * 2),'YYYY')
and ci.isactive='Y'
and bp.ISCUSTOMER='Y'
)
),
(select produit_fini,matiere_premiere,
date_stockage,date_livraison,nbj_stock_mp,date_production,date_fin_stock from Stat_Commercial
order by sysdate - date_stockage)

Quand je l'exécute sous toad ca marche bien ( pendant 0,54 seconde), mais quand je l'éxecute sous ireport 3.7.5 ca tourne sans infinie
S'il vous plaît y a -t- ils des idées??
Merci d'avance

[JauB]

Bonjour,
A part de te dire que ta requête semble si longue, je ne vois pas quand est ce que tu fais les jointures entre tes différentes requêtes imbriquées !!
J'ai du mal à comprendre pourquoi Toad te retourne de bons résultats !