Plusieurs liens sur une même table : problème de performance

**yservant** · 22/02/2013, 15h19

Bonjour,

Je travaille sur une requête très simple: afficher les champs d'un fichier articles y compris les champs statistiques et ses libellés.

Les libellés des champs stats sont dans une autre table: je dois donc lire la table statistiques autant de fois qu'il y a de champs de stats dans le fichier article. En l’occurrence: 6

Nombre de liens (lecture) et performance:
- 4 liens --> 11 s;
- 5 liens --> 2 min
- 6 liens --> time out (10 min)

Est-ce qu'il y a une façon d'optimiser ma requête ?

En vous remerciant

**mnitu** · 22/02/2013, 15h23

Utiliser des jointures.

**Rei Ichido** · 22/02/2013, 15h35

Et fournir la structure des tables !

**yservant** · 22/02/2013, 16h48

Merci beaucoup pour vos réponses:

--> J'ai fait la requête avec des liaisons.
--> La structure de la clef (ci dessous):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_1 ON
	(APRODDTA___F4101_.IMSRP1 = SUBSTR(PRODCTL_F0005_1.DRKY,-3) AND	PRODCTL_F0005_1.DRSY = '41' AND 	PRODCTL_F0005_1.DRRT = 'S1')
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_2 ON
	(APRODDTA___F4101_.IMSRP2 = SUBSTR(PRODCTL_F0005_2.DRKY,-3) AND	PRODCTL_F0005_2.DRSY = '41' AND 	PRODCTL_F0005_2.DRRT = 'S2')
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_3 ON
	(APRODDTA___F4101_.IMSRP3 = SUBSTR(PRODCTL_F0005_3.DRKY,-3) AND	PRODCTL_F0005_3.DRSY = '41' AND 	PRODCTL_F0005_3.DRRT = 'S3')
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_4 ON
	(APRODDTA___F4101_.IMPRP1 = SUBSTR(PRODCTL_F0005_4.DRKY,-3) AND	PRODCTL_F0005_4.DRSY = '41' AND 	PRODCTL_F0005_4.DRRT = 'P1')
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_5 ON
	(APRODDTA___F4101_.IMPRP2 = SUBSTR(PRODCTL_F0005_5.DRKY,-3) AND	PRODCTL_F0005_5.DRSY = '41' AND 	PRODCTL_F0005_5.DRRT = 'P2')
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_6 ON
	(APRODDTA___F4101_.IMPRP3 = SUBSTR(PRODCTL_F0005_6.DRKY,-3) AND	PRODCTL_F0005_6.DRSY = '41' AND 	PRODCTL_F0005_6.DRRT = 'P3')

**skuatamad** · 22/02/2013, 17h10

Avec un CASE (ou un max(case...)) ça devrait être possible avec une seule jointure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
select ...,
       case when PRODCTL_F0005_1.DRRT = 'S1' then ... end as s1_...
       case when PRODCTL_F0005_1.DRRT = 'S2' then ... end as s2_...
       ...
  from APRODDTA___F4101_
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_1 ON APRODDTA___F4101_.IMSRP1 = SUBSTR(PRODCTL_F0005_1.DRKY,-3)
where PRODCTL_F0005_1.DRSY = '41' 
  AND PRODCTL_F0005_1.DRRT in ('S1','S2','S3','P1','P2','P3')

Par ailleurs il sera peut être intéressant de créer un index de fonction sur SUBSTR(PRODCTL.F0005,-3) peut être même couplé à DRSY.

**Rei Ichido** · 22/02/2013, 17h12

Si on pouvait avoir la requête complète, ça aiderait à proposer quelque chose. C'est difficile de proposer quoi que ce soit, on ne sait vraiment pas grand chose. Une idée des volumétries en jeu ? Avec un peu de répartition si possible.

Un peu au pif au vu du peu d'informations, on pourrait tenter quelque chose du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
CREATE INDEX IDX_TEST_PERF ON PRODCTL.F0005 (   SUBSTR(DRKY,-3) , DRSY, DRRT) )

L'ordre des colonnes pourrait être modifié selon les volumétries. On pourrait aussi ajouter des colonnes à la fin pour éviter les accès à la table. Ou encore ré-écrire la requête en factorisant le parcours de la table, si on connaissait la structure et les contraintes de la table lues beaucoup de fois.

Mais pour ça il faut plus d'informations !

**yservant** · 22/02/2013, 17h12

Merci pour la réponse.
bonne idée le Case.

**Rei Ichido** · 22/02/2013, 17h13

Envoyé par skuatamad

Avec un CASE (ou un max(case...)) ça devrait être possible avec une seule jointure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
select ...,
       case when PRODCTL_F0005_1.DRRT = 'S1' then ... end as s1_...
       case when PRODCTL_F0005_1.DRRT = 'S2' then ... end as s2_...
       ...
  from APRODDTA___F4101_
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_1 ON APRODDTA___F4101_.IMSRP1 = SUBSTR(PRODCTL_F0005_1.DRKY,-3)
where PRODCTL_F0005_1.DRSY = '41' 
  AND PRODCTL_F0005_1.DRRT in ('S1','S2','S3','P1','P2','P3')

Par ailleurs il sera peut être intéressant de créer un index de fonction sur SUBSTR(PRODCTL.F0005,-3) peut être même couplé à DRSY.

Il faut faire plus que cela - un group by au minimum, car les jointures sont cumulatives (il faut donc une ligne avec s1, une ligne avec s2, etc.).

**yservant** · 22/02/2013, 17h26

La volumétrie :
-Table article (F4101): 2833 enregistrements;
-Table diverses (F0005): 40542 enregistrements;

La table des stat (F0005) est, comme souvent, une table fourre-tout, d'ou sa volumétrie délirante.

La requête complète n'est pas très compliquée; il s'agit d'avoir une liste articles complète sur laquelle s’appuieront d'autres requête (Je travaille avec MyReport)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SSELECT 
	APRODDTA___F4101_.IMLITM AS Article_ID, 
	APRODDTA___F4101_.IMDSC1 AS Designation, 
	APRODDTA___F4101_.IMSRP1 AS Stat_1, 
	PRODCTL_F0005_1.DRDL01 AS Stat_1_Lib, 
	APRODDTA___F4101_.IMSRP2 AS Stat_2, 
	PRODCTL_F0005_2.DRDL01 AS Stat_2_Lib, 
	APRODDTA___F4101_.IMSRP3 AS Stat_3, 
	PRODCTL_F0005_3.DRDL01 AS Stat_3_Lib, 
	APRODDTA___F4101_.IMPRP1 AS Stat_4, 
	PRODCTL_F0005_4.DRDL01 AS Stat_4_Lib, 
	APRODDTA___F4101_.IMPRP2 AS Stat_5, 
	PRODCTL_F0005_5.DRDL01 AS Stat_5_Lib, 
	APRODDTA___F4101_.IMPRP3 AS Stat_6, 
	PRODCTL_F0005_6.DRDL01 AS Stat_6_Lib, 
	APRODDTA___F4101_.IMUOM1 AS UOM
 
FROM 
	PRODDTA.F4101	APRODDTA___F4101_
 
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_1 ON
	(APRODDTA___F4101_.IMSRP1 = SUBSTR(PRODCTL_F0005_1.DRKY,-3) AND	PRODCTL_F0005_1.DRSY = '41' AND 	PRODCTL_F0005_1.DRRT = 'S1')
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_2 ON
	(APRODDTA___F4101_.IMSRP2 = SUBSTR(PRODCTL_F0005_2.DRKY,-3) AND	PRODCTL_F0005_2.DRSY = '41' AND 	PRODCTL_F0005_2.DRRT = 'S2')
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_3 ON
	(APRODDTA___F4101_.IMSRP3 = SUBSTR(PRODCTL_F0005_3.DRKY,-3) AND	PRODCTL_F0005_3.DRSY = '41' AND 	PRODCTL_F0005_3.DRRT = 'S3')
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_4 ON
	(APRODDTA___F4101_.IMPRP1 = SUBSTR(PRODCTL_F0005_4.DRKY,-3) AND	PRODCTL_F0005_4.DRSY = '41' AND 	PRODCTL_F0005_4.DRRT = 'P1')
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_5 ON
	(APRODDTA___F4101_.IMPRP2 = SUBSTR(PRODCTL_F0005_5.DRKY,-3) AND	PRODCTL_F0005_5.DRSY = '41' AND 	PRODCTL_F0005_5.DRRT = 'P2')
 
INNER JOIN
	PRODCTL.F0005	PRODCTL_F0005_6 ON
	(APRODDTA___F4101_.IMPRP3 = SUBSTR(PRODCTL_F0005_6.DRKY,-3) AND	PRODCTL_F0005_6.DRSY = '41' AND 	PRODCTL_F0005_6.DRRT = 'P3')

Pour finir: je suis contrôleur de gestion. i.e. je n'ai pas de droit pour écrire dans la base oracle et mes connaissance en SQL sont limitées.

**Rei Ichido** · 25/02/2013, 09h21

Les volumétries me semblent bien faibles pour déclencher ce genre de comportement ... d'après les préfixes, je vois que les schémas sont différents pour les deux tables. Est-ce que à tout hasard l'une des deux tables serait sur une base distante ? Avec un alias sur une table accédée par DataLink ? Ca pourrait expliquer des lenteurs telles que celles que vous signalez ...

Sinon, vous pouvez demander la création de l'index suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
CREATE INDEX IDX_TEST_PERF ON PRODCTL.F0005 (   DRSY, DRRT, SUBSTR(DRKY,-3) ,  DRDL01  ) )

Ou encore ré-écrire la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
WITH libelles AS
(
SELECT MAX ( CASE WHEN DRRT = 'S1' THEN DRDL01 END) AS Stat_1_Lib
          ,MAX ( CASE WHEN DRRT = 'S2' THEN DRDL01 END) AS Stat_2_Lib
          ,MAX ( CASE WHEN DRRT = 'S3' THEN DRDL01 END) AS Stat_3_Lib
          ,MAX ( CASE WHEN DRRT = 'S3' THEN DRDL01 END) AS Stat_3_Lib
          ,MAX ( CASE WHEN DRRT = 'P1' THEN DRDL01 END) AS Stat_4_Lib
          ,MAX ( CASE WHEN DRRT = 'P2' THEN DRDL01 END) AS Stat_5_Lib
          ,MAX ( CASE WHEN DRRT = 'P3' THEN DRDL01 END) AS Stat_6_Lib
         ,SUBSTR(DRKY,-3) AS DRKY_LAST_3
  FROM PRODCTL.PRODCTL_F0005
WHERE DRSY = '41' 
GROUP BY SUBSTR(DRKY,-3)
-- note : s'il manque un libelle, la ligne sera quand même retournée
-- si on veut rajouter le controle que tous les libellés soient présents 
-- il faut rajouter HAVING count(*) = 6
)
SELECT 
	APRODDTA___F4101_.IMLITM AS Article_ID, 
	APRODDTA___F4101_.IMDSC1 AS Designation, 
	APRODDTA___F4101_.IMSRP1 AS Stat_1, 
	libelles.Stat_1_Lib, 
	APRODDTA___F4101_.IMSRP2 AS Stat_2, 
	libelles.Stat_2_Lib, 
	APRODDTA___F4101_.IMSRP3 AS Stat_3, 
	libelles.Stat_3_Lib, 
	APRODDTA___F4101_.IMPRP1 AS Stat_4, 
	libelles.Stat_4_Lib, 
	APRODDTA___F4101_.IMPRP2 AS Stat_5, 
	libelles.Stat_5_Lib, 
	APRODDTA___F4101_.IMPRP3 AS Stat_6, 
	libelles.Stat_6_Lib, 
	APRODDTA___F4101_.IMUOM1 AS UOM
 
FROM 
	PRODDTA.F4101	APRODDTA___F4101_
 INNER JOIN libelles 
       ON libelles.DRKY_LAST_3 = APRODDTA___F4101_.IMSRP1

Note :

**yservant** · 04/03/2013, 10h28

Désolé pour ma réponse tardive, je reviens de congés

Je teste tout ça rapidement; merci !

Plusieurs liens sur une même table : problème de performance

SQL Oracle

Discussions similaires

Partager

Partager