Discussion :

[gudul]

Bonjour.

Sous FireBird 1.5.2, je fais une requete assez complexe avec plusieurs jointures. Cette requète va durée tres longtemps(plusieurs minutes) mais si j'enlève une contrainte d'intégrité sur une clef étrangère contenue dans ma requète, elle ne met que qq secondes.

Qu'est ce qui peut expliquer ce phénomène et comment faire si je veux garder ma contrainte ?

[Barbibulle]

Il peut arriver que le SGBD choisissent la mauvaise méthode d'accès aux données.

Classiquement un accès via un index est plus rapide. Mais ce n'est pas toujours vrai. Notamment lorsqu'on dois parcourir toute une table, et en récupérer des données.

Le problème est d'autant plus difficile que une méthode valable à un un moment peut ne plus l'être quelque temps apres. Car c'est intimement lié aux données (quantitées de données).

Vous pouvez cependant lui forcer une stratégie d'accés. (Sans devoir supprimer votre foreign key ).

Il suffit de lui indiquer le plan a utiliser. (Instruction PLAN a mettre dans votre ordre SQL)

Pour vous faciliter la tache regardez le plan utilisé par votre requete lorsque vous avez supprimé votre clé étrangère et recopiez le. Remettez votre clé étrangère et incorporez la ligne plan dans votre ordre SQL.
(Sous la plupart des utilitaires de développement le plan d'une requete est restituée, pour justement permettre l'optimisation de certaines requetes.)
L'autre solution est d'essayer de construire sa requete différement afin 'd'influencer' le moteur du SGBD à choisir la bonne stratégie...

[gudul]

Le problème est que la requête est créée dynamiquement par l'utilisateur suivant ce qu'il coche et sélectionne. Il est donc difficile de créer et ajouter un Plan pour une selection précise.

J'ai testé 2 requetes. une en précisant une valeur, l'autre sans.
Le serveur crée un plan en indexant sur des clefs primaires dans le second cas et dans le premier cas sur des clefs etrangères.
En supprimant cette clef etrangère, le plan reprend sur la clef primaire, ce qui améliore le temps de la requête.

[gudul]

Voici la requête pour illustrer. Elle est créée suivant des cases à cocher et des sélections de l'utilisateur (requêteur perso).

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
SELECT 
SUM 
(ETPPROD.N_QTEMPETPPROD/ ETPCONT.F_NBCPSANTCPSE/ CONTVGETP.N_NBRALVEOLECONTVG) as QTE,
ETPCONT.S_IDETP,
ETPPROD.N_NOSEMAINE,
ETPPROD.N_ANNEEETPPROD,
ETPPROD.D_ETPPROD,
ETPPROD.S_SUIVIETPPROD,
CONTFINR.N_IDTYPCONTVG AS IDTYPCONTFINR,
PF_R.N_IDCONTVG AS IDCONTFINR,
ARTVG_R.N_IDFAMILLE,
ARTVG_R.N_IDGENRE,
ARTVG_R.N_IDVARIETE,
 ETPPROD.S_STATUTPRETPPROD
FROM PLANDISPOPROD P1
    JOIN ETPPROD E1 ON E1.N_IDPLANDISPOPROD = P1.N_IDPLANDISPOPROD
    JOIN ETPCONT ON ETPPROD.N_IDETPCONT = ETPCONT.N_IDETPCONT
    join CONTVG CONTVGETP ON CONTVG.N_IDCONTVG = ETPCONT.N_IDCONTVG
    JOIN PF PF_R ON PF.N_IDPF = PLANDISPOPROD.N_IDPF_R
    JOIN TR_PFARTVG TR_PFARTVG_R ON TR_PFARTVG.N_IDPF = PLANDISPOPROD.N_IDPF_R
    JOIN ARTVG ARTVG_R ON ARTVG.N_IDARTVG = TR_PFARTVG_R.N_IDARTVG
    JOIN VARIETE ON VARIETE.N_IDVARIETE = ARTVG_R.N_IDVARIETE
    JOIN CONTVG CONTFINR ON CONTVG.N_IDCONTVG = PF_R.N_IDCONTVG
where 
 (
 (
 (D_ETPPROD 
  between '07/25/2005' and '02/13/2006'
 )
 )
 )
  and
  ( 
(etpcont.s_idetp  ='BOU')
 or 
(etpcont.s_idetp  ='REP')
)
  and
  (  
  (artvg_R.N_IDFamille = 3)
  )
  and 
  (  
  (artvg_R.N_IDGenre = 103)
  )
and 
  (  
  (contfinR.N_IDTypcontVG = 13)
  )  and EtpProd.S_StatutPREtpProd = 'R'
group by
  etpcont.s_idetp,
  N_NoSemaine, N_ANNEEetpprod, d_etpprod,
  contfinR.N_IDTypcontVG,
PF_R.N_IDcontVG,
  artvg_R.N_IDFamille,
  artvg_R.N_IDGenre,
  artvg_R.N_IDVariete,
ETPPROD.S_SUIVIETPPROD,
  EtpProd.S_StatutPREtpProd

avec le plan créé

Code :

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

PLAN SORT (JOIN (E1 NATURAL,ETPCONT INDEX (PK_ETPCONT),CONTVGETP INDEX (PK_CONTVG),CONTFINR INDEX (FK_CONTVG_ASSOCIATI_TYPCONTV),PF_R INDEX (FK_PF_53_CONTVG),P1 INDEX (PK_PLANDISPOPROD),TR_PFARTVG_R INDEX (FK_TR_PFART_TR_PFARTV_PF),ARTVG_R INDEX (PK_ARTVG),VARIETE INDEX (PK_VARIETE)))

temps d'execution = 1mn44

la même sans

Code :

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

1
2
3
4
  and 
  (  
  (contfinR.N_IDTypcontVG = 13)
  )

plan créé

Code :

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

PLAN SORT (JOIN (E1 NATURAL,ETPCONT INDEX (PK_ETPCONT),CONTVGETP INDEX (PK_CONTVG),P1 INDEX (PK_PLANDISPOPROD),PF_R INDEX (PK_PF),CONTFINR INDEX (PK_CONTVG),TR_PFARTVG_R INDEX (FK_TR_PFART_TR_PFARTV_PF),ARTVG_R INDEX (PK_ARTVG),VARIETE INDEX (PK_VARIETE)))

tps d'execution 771 ms

en mettant le plan 2 dans la requête 1
tps d'execution 1s 220ms

en suppimant la clef étrangère FK_PF_53_CONTVG
temps d'execution 1s 670ms

La meilleure solution pour moi est donc de supprimer la clef étrangère avec les risques que cela comporte.