Optimisation de requête

Version imprimable

08/02/2010, 11h18
Hobbi1

Optimisation de requête
Bonjour,

Sur le projet, il y a 3 tables reliées par une association, la 3e table servant à définir le type de l'association
J'ai une requête entre 1 de ces tables et l'association. Comme on sait que type d'association on veut, on s'arrête à l'association mais on l'interroge plusieurs fois, pour chaque type que l'on veut.
Avec un ex ca sera peut être plus clair
Code:

1 2 3 4 5 6 SELECT FROM table t, asso a1, asso a2 ... WHERE t.code = a1.code AND a1.type = 'TYPE1' AND t.code = a2.code AND a2.type = 'TYPE2'
Ce qui fait donc interroger la table d'association plusieurs fois.
Il faut que tous les types soient ramenés sur la même ligne et ne pas générer une ligne par type.

Y a-t-il un moyen de n'interroger la table d'association qu'une seule fois tout en ayant les infos sur la même ligne ce qui me permettrait surement d'optimiser la requête.

Merci de votre aide.
08/02/2010, 11h55
faraudch

Bonjour,
Peux tu mettre un exemple de résultat que tu souhaites obtenir, ainsi que la structure de tes tables.
Cordialement.

Il y a les tables : DOSS ( ID_DOSS VARCHAR2 ...)
SOCIETE (ID_ENT NUMBER ..)
TYPE (ID_TYPE ...)
Et la table d'association : CONCERNER (ID_DOSS, ID_ENT, ID_TYPE)
Pour un dossier, il peut y avoir plusieurs sociétés et les sociétés sont présentes sur plusieurs dossiers , tout ca avec des types différents

Pour un dossier, si je fais :
Code:

1 2 3 4 5 6 7 8 9 SELECT ID_ENT, ID_TYPE FROM CONCERNER a, DOSS d WHERE a.ID_DOSS = d.ID_DOSS AND ID_DOSS=1 AND ID_TYPE IN ('GEST','BENEF') ID_ENT ID_TYPE ------- -------- 1 BENEF 2 GEST
Moi je veux comme résultat
Code:

1 2 3 4 ID_ENT_BENEF ID_TYPE ID_ENT_GEST ID_TYPE ------------- -------- ------------- -------- 1 BENEF 2 GEST
Donc pour ce faire je fais
Code:

1 2 3 4 5 6 SELECT ID_ENT, ID_TYPE FROM CONCERNER a_gest, CONCERNER a_benef, DOSS d WHERE ID_DOSS=1 AND a_gest.ID_DOSS = d.ID_DOSS AND a_gest.ID_TYPE = 'GEST' AND a_benef.ID_DOSS = d.ID_DOSS AND a_benef.ID_TYPE = 'BENEF'
Ca me donne bien le résultat que je veux mais ca oblige à interroger 2 fois la table d'association et ca me gêne
Je voudrais savoir s'il existe une méthode pour éviter ca

Tu peux essayer ça :

Code:

1
2
3
4
5
6
7
8
 
Select ID_DOSS ,
(case when  ID_TYPE = 'BENEF' then ID_ENT_BENEF),
(case when  ID_TYPE = 'BENEF' then ID_TYPE),
(case when  ID_TYPE = 'GEST' then ID_ENT_GEST),
(case when  ID_TYPE = 'GEST' then ID_TYPE)
from DOSS d join CONCERNER a_gest
on d.ID_DOSS = a_gest.ID_DOSS ;

Je n'ai pas testé !
Bon courage.

Tu peux passer de

Code:

1
2
3
4
5
6
SELECT a_gest.ID_ENT, a_gest.ID_TYPE,  a_benef.ID_ENT, a_benef.ID_TYPE, d.ID_DOSS FROM CONCERNER a_gest, CONCERNER a_benef, DOSS d
WHERE ID_DOSS=1
AND a_gest.ID_DOSS = d.ID_DOSS
AND a_gest.ID_TYPE = 'GEST'
AND a_benef.ID_DOSS = d.ID_DOSS
AND a_benef.ID_TYPE = 'BENEF'

Code:

1
2
3
4
5
6
7
8
9
10
SELECT
'GEST', max(decode(a.ID_TYPE, 'GEST', a_gest.ID_ENT))
,'BENEF', max(decode(a.ID_TYPE, 'BENEF', a_gest.ID_ENT))
,d.ID_DOSS
FROM CONCERNER a, DOSS d
WHERE ID_DOSS=1
AND a.ID_DOSS = d.ID_DOSS
AND a.ID_TYPE in ('GEST', 'BENEF')
group by
'GEST', 'BENEF', d.ID_DOSS

mais de là à dire que ce sera plus performant...

Quels sont tes problèmes de performances ?

08/02/2010, 14h27
Hobbi1

Bah en fait ca ne donne pas ce que je veux.

Il n'y a pas encore d'alertes de perf mais quand j'ai vu la requête avec la table d'association qui grossit énormément et qui est interrogé plusieurs fois, j'essaye de prendre les devants.

Qu'est-ce qui ne convient pas dans la requête de nuke_y ?
À la syntaxe ANSI près j'aurai présenté la même idée :

Code:

1
2
3
4
5
6
7
8
9
10
  SELECT d.ID_DOSS,
         max(case a.ID_TYPE when 'GEST'  then a_gest.ID_ENT end) as ID_ENT_GEST ,
         max(case a.ID_TYPE when 'BENEF' then a_gest.ID_ENT end) as ID_ENT_BENEF
    FROM CONCERNER a
         INNER JOIN DOSS d
           ON d.ID_DOSS = a.ID_DOSS
   WHERE d.ID_DOSS = 1
     AND a.ID_TYPE IN ('GEST', 'BENEF')
GROUP BY d.ID_DOSS
ORDER BY d.ID_DOSS ASC;

08/02/2010, 15h23
Hobbi1

La requête de faraudch ne donne pas ce que je veux
Celle de nuke_y donne bien ce que je veux mais comme nuke_y le laissait entendre, les perf ne sont pas meilleures
Le plan d'exécution est mieux mais à l'exécution ca met autant de temps
08/02/2010, 15h41
Waldar

Vous avez le droit de partager les dits temps et plans d'exécution !
La requête n'a l'air de ne traiter qu'un dossier : à savoir si c'est votre vraie requête ou si c'est pour l'exemple.
08/02/2010, 15h53
nuke_y

Mais c'est quoi "autant de temps". Quelles sont les durées et les volumes mis en jeu ?

Je pense que l'optimisation ne viendra pas de la requête mais de la modélisation et/ou du tuning.
08/02/2010, 16h21
Hobbi1

J'ai très simplifiée la requête pour l'exemple. Je n'ai gardé que la partie qui me chagrinait.

La table d'association est en fait interrogée 5 fois dans la requête et elle contient 2,3 millions de lignes.

Pour l'explain plan, j'ai essayé de le copier depuis Toad mais ca devient indigeste sans la présentation de Toad
08/02/2010, 16h25
Waldar

Sous TOAD 9.6, en faisant un clic droit dans la partie de l'explain plan je peux faire Copy to Clipboard, la présentation est gardée tant que vous collez le résultat dans une balise code.

Voici donc le plan de la requête

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
SELECT STATEMENT  ALL_ROWSCost: 46,161  Bytes: 47,860,297  Cardinality: 132,577  												
	4 NESTED LOOPS  Cost: 4  Bytes: 48  Cardinality: 1  											
		2 TABLE ACCESS BY INDEX ROWID TABLE ORC.FRM_DON_MONVAL Cost: 4  Bytes: 23  Cardinality: 1  										
			1 INDEX RANGE SCAN INDEX ORC.FK_FRM_DON_MONVAL_ORC_DOSSIER Cost: 3  Cardinality: 1  									
		3 INDEX UNIQUE SCAN INDEX (UNIQUE) ORC.UK_REF_DEF_VAL_POSS Cost: 0  Bytes: 25  Cardinality: 1  										
	26 SORT ORDER BY  Cost: 46,161  Bytes: 47,860,297  Cardinality: 132,577  											
		25 HASH JOIN  Cost: 35,927  Bytes: 47,860,297  Cardinality: 132,577  										
			5 TABLE ACCESS FULL TABLE ORC.ASS_CONCR_ES Cost: 5,472  Bytes: 15,926,580  Cardinality: 353,924  									
			24 HASH JOIN  Cost: 28,125  Bytes: 27,846,236  Cardinality: 88,121  									
				22 HASH JOIN  Cost: 20,906  Bytes: 15,873,283  Cardinality: 58,573  								
					20 HASH JOIN  Cost: 14,034  Bytes: 8,798,632  Cardinality: 38,932  							
						18 NESTED LOOPS OUTER  Cost: 7,363  Bytes: 4,683,918  Cardinality: 25,878  						
							16 NESTED LOOPS OUTER  Cost: 7,363  Bytes: 4,632,162  Cardinality: 25,878  					
								14 HASH JOIN  Cost: 7,363  Bytes: 4,580,406  Cardinality: 25,878  				
									6 TABLE ACCESS FULL TABLE ORC.REF_GROUP_PROC Cost: 2  Bytes: 140  Cardinality: 10  			
									13 HASH JOIN  Cost: 7,360  Bytes: 4,218,114  Cardinality: 25,878  			
										7 TABLE ACCESS FULL TABLE ORC.CAT_PROCEDURE Cost: 2  Bytes: 500  Cardinality: 25  		
										12 HASH JOIN  Cost: 7,357  Bytes: 3,700,554  Cardinality: 25,878  		
											10 HASH JOIN  Cost: 1,881  Bytes: 1,685,600  Cardinality: 17,200  	
												8 TABLE ACCESS FULL TABLE ORC.CAT_VERS_PROC Cost: 7  Bytes: 325  Cardinality: 13  
												9 TABLE ACCESS FULL TABLE ORC.ORC_DOSSIER Cost: 1,874  Bytes: 2,209,929  Cardinality: 30,273  
											11 TABLE ACCESS FULL TABLE ORC.ASS_CONCR_ES Cost: 5,472  Bytes: 15,926,580  Cardinality: 353,924  	
								15 INDEX UNIQUE SCAN INDEX (UNIQUE) ORC.PK_REF_COMPLEXITE Cost: 0  Bytes: 2  Cardinality: 1  				
							17 INDEX UNIQUE SCAN INDEX (UNIQUE) ORC.PK_REF_URGENCE Cost: 0  Bytes: 2  Cardinality: 1  					
						19 TABLE ACCESS FULL TABLE ORC.ASS_CONCR_ES Cost: 5,472  Bytes: 15,926,580  Cardinality: 353,924  						
					21 TABLE ACCESS FULL TABLE ORC.ASS_CONCR_ES Cost: 5,472  Bytes: 15,926,580  Cardinality: 353,924  							
				23 TABLE ACCESS FULL TABLE ORC.ASS_CONCR_ES Cost: 5,472  Bytes: 15,926,580  Cardinality: 353,924

09/02/2010, 09h14
mnitu

Ca ne sera mal maintenant d'avoir aussi la requête qui n'a rien à voir avec votre exemple de départ.

La voici :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SELECT date_cle__dte date_cle,
       es_gest.cod_ref_ent_struct__chr es_gestionnaire,
       es_niv3.cod_ref_ent_struct__chr es_trait_doss_niv3,
       vers_proc.cod_cat_procedure__chr id_proc_orc,
       d.cod_cat_statut_av__chr statut_avancement,
       es_niv2.cod_ref_ent_struct__chr es_trait_doss_niv2,
       es_benef.cod_ref_ent_struct__chr es_beneficiaire,
       d.id_orc_dossier__chr id_doss_orc,
       gp.cod_ref_activ_banq__chr id_ab_orc,
       es_niv1.cod_ref_ent_struct__chr es_trait_doss_niv1,
       d.date_creation__dte date_creat_doss,
       gp.cod_ref_group_proc__chr id_gp_orc,
       d.date_cloture__dte date_cloture_doss,
       code_motif_cloture__chr id_motif_cloture,
       TO_TIMESTAMP (TO_CHAR (SYSDATE, 'DD/MM/YY HH24:MI:SS'),
                     'DD/MM/YY HH24:MI:SS'
                    ) datecourante,
       complexitetable.cod_ref_complexite__chr complexite,
       urgencetable.cod_ref_urgence__chr urgence,
       (SELECT valposs.cod_valeur__chr
          FROM frm_don_monval monoval,
               ref_def_val_poss valposs
         WHERE d.id_orc_dossier__chr = monoval.id_orc_dossier__chr
           AND monoval.champs20__chr = valposs.cod_valeur__chr
           AND valposs.cod_donnee__chr = 'PPI_CONFORMITE_DOSSIER')
                                                                   conformite,
           d.INDIC_CRITICITE__BOL criticite
  FROM orc_dossier d,
       ass_concr_es es_gest,
       ass_concr_es es_niv3,
       cat_vers_proc vers_proc,
       ass_concr_es es_niv2,
       ass_concr_es es_benef,
       cat_procedure proc,
       ref_group_proc gp,
       ass_concr_es es_niv1,
       ref_urgence urgencetable,
       ref_complexite complexitetable
 WHERE  
   d.date_cloture__dte IS NULL 
   AND d.id_orc_dossier__chr = es_gest.id_orc_dossier__chr
   AND es_gest.cod_cat_rol_ent_st__chr = 'ES_GEST_DOSS'
   AND d.id_orc_dossier__chr = es_niv3.id_orc_dossier__chr
   AND es_niv3.cod_cat_rol_ent_st__chr = 'ES_TRAIT_NIV3'
   AND d.cod_cat_vers_proc__chr = vers_proc.cod_cat_vers_proc__chr
   AND vers_proc.cod_cat_procedure__chr = proc.cod_cat_procedure__chr
   AND proc.cod_ref_group_proc__chr = gp.cod_ref_group_proc__chr
   AND d.id_orc_dossier__chr = es_niv2.id_orc_dossier__chr
   AND es_niv2.cod_cat_rol_ent_st__chr = 'ES_TRAIT_NIV2'
   AND d.id_orc_dossier__chr = es_benef.id_orc_dossier__chr
   AND es_benef.cod_cat_rol_ent_st__chr = 'ES_BENEF_DOSS'
   AND d.id_orc_dossier__chr = es_niv1.id_orc_dossier__chr
   AND es_niv1.cod_cat_rol_ent_st__chr = 'ES_TRAIT_NIV1'
   AND vers_proc.est_proc_simplifiee__bol = 0
   AND d.cod_ref_etat_dossier__chr = 'EN_COURS'
   AND d.cod_ref_urgence__chr = urgencetable.cod_ref_urgence__chr(+)
   AND d.cod_ref_complexite__chr = complexitetable.cod_ref_complexite__chr(+)
order by d.id_orc_dossier__chr, es_gest.cod_ref_ent_struct__chr,es_benef.cod_ref_ent_struct__chr

Alors, je m'y colle.
Déjà sur la forme, il y a pas mal "d'erreur". Entre guillement car s'agissant de forme, ce ne sont pas de vraies erreurs à proprement parlé mais de petites choses qui font que la requête est moins lisible qu'elle pourrait l'être.

Il faut impérativement utiliser des alias de tables (ce que vous faites), mais il faut également limiter la taille de ces dits alias et si possible qu'ils soient parlant (acronyme, premières lettres).

Vous utilisez sysdate que vous convertissez en texte puis en timestamp.
Je vous invite à utiliser systimestamp !

Vous avez une requête scalaire !
Dans 99% des cas, c'est mal et un tueur de performances.
À priori, on peut s'en passer.

Il manque les jointures ANSI.
Surtout sur ce genre de requête, ça permet une vitesse de relecture sans commune mesure !

Et enfin, le pivot que nous avons mentionné plus haut peut également faire gagner des performances. J'ai réordonné quelques colonnes pour gagner en lisibilité.
Tout combiné, voici ce que devient votre requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
SELECT
    dos.date_cle__dte           as date_cle         ,
    cvp.cod_cat_cpredure__chr   as id_cpr_orc       ,
    dos.cod_cat_statut_av__chr  as statut_avancement,
    dos.id_orc_dossier__chr     as id_doss_orc      ,
    rgp.cod_ref_activ_banq__chr as id_ab_orc        ,
    dos.date_creation__dte      as date_creat_doss  ,
    rgp.cod_ref_group_cpr__chr  as id_gp_orc        ,
    dos.date_cloture__dte       as date_cloture_doss,
    code_motif_cloture__chr     as id_motif_cloture ,
    systimestamp                as datecourante     ,
    rco.cod_ref_complexite__chr as complexite       ,
    rug.cod_ref_urgence__chr    as urgence          ,
    vlp.cod_valeur__chr         as conformite       ,
    dos.INDIC_CRITICITE__BOL    as criticite        ,
    max(case aco.cod_cat_rol_ent_st__chr when 'esg_DOSS'      then aco.cod_ref_ent_struct__chr end) as esgionnaire       ,
    max(case aco.cod_cat_rol_ent_st__chr when 'esb_DOSS'      then aco.cod_ref_ent_struct__chr end) as esbiciaire        ,
    max(case aco.cod_cat_rol_ent_st__chr when 'ES_TRAIT_NIV1' then aco.cod_ref_ent_struct__chr end) as es_trait_doss_niv1,
    max(case aco.cod_cat_rol_ent_st__chr when 'ES_TRAIT_NIV2' then aco.cod_ref_ent_struct__chr end) as es_trait_doss_niv2,
    max(case aco.cod_cat_rol_ent_st__chr when 'ES_TRAIT_NIV3' then aco.cod_ref_ent_struct__chr end) as es_trait_doss_niv3
FROM
    orc_dossier dos
    INNER JOIN ass_concr_es aco
      ON aco.id_orc_dossier__chr     = dos.id_orc_dossier__chr
    INNER JOIN cat_vers_cpr cvp
      ON cvp.cod_cat_vers_cpr__chr   = dos.cod_cat_vers_cpr__chr
    INNER JOIN cat_cpredure cpr
      ON cpr.cod_cat_cpredure__chr   = cvp.cod_cat_cpredure__chr
    INNER JOIN ref_group_cpr rgp
      ON rgp.cod_ref_group_cpr__chr  = cpr.cod_ref_group_cpr__chr
    LEFT OUTER JOIN ref_urgence rug
      ON rug.cod_ref_urgence__chr    = dos.cod_ref_urgence__chr
    LEFT OUTER JOIN ref_complexite rco
      ON rco.cod_ref_complexite__chr = dos.cod_ref_complexite__chr
    LEFT OUTER JOIN frm_don_monval fdm
    INNER JOIN ref_def_val_poss vlp
      ON vlp.cod_valeur__chr         = fdm.champs20__chr
     AND vlp.cod_donnee__chr         = 'PPI_CONFORMITE_DOSSIER'
      ON fdm.id_orc_dossier__chr     = dos.id_orc_dossier__chr
WHERE  
    dos.date_cloture__dte IS NULL 
AND dos.cod_ref_etat_dossier__chr = 'EN_COURS'
AND cvp.est_cpr_simplifiee__bol = 0
AND aco.cod_cat_rol_ent_st__chr IN ('esg_DOSS', 'esb_DOSS', 'ES_TRAIT_NIV1', 'ES_TRAIT_NIV2', 'ES_TRAIT_NIV3')
GROUP BY
    dos.date_cle__dte          ,
    cvp.cod_cat_cpredure__chr  ,
    dos.cod_cat_statut_av__chr ,
    dos.id_orc_dossier__chr    ,
    rgp.cod_ref_activ_banq__chr,
    dos.date_creation__dte     ,
    rgp.cod_ref_group_cpr__chr ,
    dos.date_cloture__dte      ,
    code_motif_cloture__chr    ,
    rco.cod_ref_complexite__chr,
    rug.cod_ref_urgence__chr   ,
    vlp.cod_valeur__chr        ,
    dos.INDIC_CRITICITE__BOL
ORDER BY
    dos.id_orc_dossier__chr     asc,
    esg.cod_ref_ent_struct__chr asc,
    esb.cod_ref_ent_struct__chr asc;

A vous de faire l'explain plan de cette requête, de comparer les résultats et les temps d'exécution !