Bonjour à tous,
je viens de passer une semaine à fignoler une requête qui me compare le chiffre d'affaire facturé et le chiffre d'affaire effectivement comptabilisé.....
Bon, cela c'est pour info. Mon pbm est que quand je la lance pour une journée donnée, elle me donne le résultat en 1 minute. Quand je la lance sur l'année, l'utilisation CPU de mon serveur monte à 100% et plus rien ne se passe 
.
Comment puis-je aborder ce pbm ?
Merci d'avanc à tous
Ma requête est la suivante :
select f.Mois, f.Facturé, c.Solde "Comptabilisé"
From
(
select fact.Mois, sum(nvl(lf.mnt_rem_amo,0)+nvl(lf.mnt_rem_amc,0)+nvl(lf.mnt_assure,0)) Facturé
from ligne_fact lf
,(select distinct to_char(ac.date_comptable,'Month') Mois,f.numero_facture, f.num_fact
from facture f, article_comptable ac, type_journal tj
where ac.fac_num_fact=f.num_fact
and ac.date_comptable between '01/01/2003' and '31/12/2003'
and tj.id = ac.tpjrn_id
and tj.code='VPC'
and f.type_fact <> 'AVO'
and f.num_fact not in
(select distinct f.num_fact
from facture f,facture av, article_comptable ac, type_journal tj
where ac.fac_num_fact=av.num_fact
and av.fac_num_fact=f.num_fact
and ac.date_comptable <= '31/12/2003'
and tj.id = ac.tpjrn_id
and tj.code='VPC'
and f.sit_fac='A')
) fact
where lf.fac_num_fact=fact.num_fact
and lf.cp_type in ('0','3')
group by fact.Mois
) F
,(select c.Mois, c.Crédit-d.Débit Solde
From
(
select cred.Mois, sum(cred.Crédit/coef.nbac) Crédit
from (
select distinct ac.fac_num_fact, count(ac.num_piece) nbac
from article_comptable ac, type_journal tj
where ac.tpjrn_id=tj.id
and ac.date_comptable between '01/01/2003' and '31/12/2003'
and tj.code='VPC'
group by ac.fac_num_fact
) coef,
(
select distinct to_char(ac.date_comptable,'Month') Mois, ac.fac_num_fact, sum(le.montant) Crédit
from ligne_ecriture le, article_comptable ac, type_journal tj, compte_ga cc
where ac.tpjrn_id=tj.id
and le.arcp_id=ac.id
and le.cptga_id_gen=cc.id
and ac.date_comptable between '01/01/2003' and '31/12/2003'
and tj.code='VPC'
and le.sens='C'
and cc.numero not like '4112%'
group by to_char(ac.date_comptable,'Month') , ac.fac_num_fact
) cred,
facture f
where coef.fac_num_fact=cred.fac_num_fact
and cred.fac_num_fact=f.num_fact
group by cred.Mois
)c
,(select deb.Mois, sum(deb.Débit/coef.nbac) Débit
from (
select distinct ac.fac_num_fact, count(ac.num_piece) nbac
from article_comptable ac, type_journal tj
where ac.tpjrn_id=tj.id
and ac.date_comptable between '01/01/2003' and '31/12/2003'
and tj.code='VPC'
group by ac.fac_num_fact
) coef,
(
select distinct to_char(ac.date_comptable,'Month') Mois, ac.fac_num_fact, sum(le.montant) Débit
from ligne_ecriture le, article_comptable ac, type_journal tj, compte_ga cc
where ac.tpjrn_id=tj.id
and le.arcp_id=ac.id
and le.cptga_id_gen=cc.id
and ac.date_comptable between '01/01/2003' and '31/12/2003'
and tj.code='VPC'
and le.sens='D'
and cc.numero not like '4112%'
group by to_char(ac.date_comptable,'Month') , ac.fac_num_fact
) deb,
facture f
where coef.fac_num_fact=deb.fac_num_fact
and deb.fac_num_fact=f.num_fact
group by deb.Mois )d
where d.Mois=c.Mois) C
where c.Mois=F.Mois
