Bonsoir niKgir et Fabien,
Envoyé par
niKgir
C'est le mystère total...
J’ai consulté madame Irma qui voit tout, entend tout et sait tout. De notre entrevue j’ai tiré quelques informations.
Reprenons votre requête :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| SELECT MagisterV1.*
FROM PNF_REF_PR, MagisterV1, PNF_Ref_PR AS PNF_REF_PR_1
WHERE (
(
([MagisterV1]![Lieu_Depart]=([PNF_REF_PR]![Code_TT0020_Localite] & "-" & [PNF_REF_PR]![Code_TT0020_chantier]))
or
(([MagisterV1]![Lieu_Depart]&"-00")=([PNF_REF_PR]![Code_TT0020_Localite] & "-" & [PNF_REF_PR]![Code_TT0020_chantier]))
or
(([MagisterV1]![Lieu_Depart]&"-BV")=([PNF_REF_PR]![Code_TT0020_Localite] & "-" & [PNF_REF_PR]![Code_TT0020_chantier]))
)
AND
(
([MagisterV1]![Lieu_Arrivee]=([PNF_REF_PR_1]![Code_TT0020_Localite] & "-" & [PNF_REF_PR_1]![Code_TT0020_chantier]))
or
(([MagisterV1]![Lieu_Arrivee]&"-00")=([PNF_REF_PR_1]![Code_TT0020_Localite] & "-" & [PNF_REF_PR_1]![Code_TT0020_chantier]))
or
(([MagisterV1]![Lieu_Arrivee]&"-BV")=([PNF_REF_PR_1]![Code_TT0020_Localite] & "-" & [PNF_REF_PR_1]![Code_TT0020_chantier]))
)
); |
Je n’ai pas capté toutes vos concaténations de chaînes, mais une chose est sûre : vous avez droit à un magnifique double produit cartésien :
PNF_REF_PR X MagisterV1 X PNF_REF_PR
Car les « OR » empêchent manifestement de transformer un produit en jointure ou autre opération pouvant normalement être rendue performante.
En l’occurrence, le SGBD balaie complètement une 1re table, par exemple MagisterV1 et pour chaque ligne lue, balaie complètement la 2e table, à savoir la table PNF_REF_PR, pour vérifier si les conditions figurant dans la requêtes sont satisfaites.
Supposons que la table MagisterV1 contienne 30000 lignes et que la table PNF_REF_PR en contienne 10000 (quel est en fait le nombre réel de ces lignes ?), le nombre de comparaisons, disons d’accès logiques, est donc de l’ordre de :
30000 X 10000 = 3.10^8
Ce qui n’est pas négligeable... Mais il y a encore un tour de manège à effectuer, auquel participent le résultat produit et à nouveau PNF_REF_PR. Supposons que le résultat produit soit de 1000 lignes. Après ce tour supplémentaire, le nombre total d’accès logiques est de l’ordre de :
30000 X 10000 X 1000 X 10000 = 3.10^15, si je compte bien...
Ou, si le moteur « optimise » :
30000 X 10000 X 10000 = 3.10^12...
Un tel nombre de lectures de lignes se traduit par un certain temps, plus qu’il n’en faut au fût d’un canon pour se refroidir (ayant été chef de char Patton je peux en parler...)
Dans cette affaire, pour arriver à des temps raisonnables, il est impératif de :
1) Faire jouer les index plein pot ;
2) Transformer le produit en somme.
Comme je n’ai pas tout compris de votre requête, j’en propose une un peu du même genre (voir tout en bas).
Créons d’abord un jeu de tables :
TABLE T1
1
2
3
4
5
6
7
8
9
10
11
| CREATE TABLE T1
(
K1 INT NOT NULL,
A1 CHAR(4) NOT NULL,
A2 CHAR(4) NOT NULL,
A3 CHAR(4) NOT NULL,
B1 CHAR(4) NOT NULL,
B2 CHAR(4) NOT NULL,
B3 CHAR(4) NOT NULL,
CONSTRAINT T1_PK PRIMARY KEY (K1)
) ; |
TABLE T2
1
2
3
4
5
6
7
| CREATE TABLE T2
(
K2 INT NOT NULL,
X CHAR(4) NOT NULL,
Y CHAR(4) NOT NULL,
CONSTRAINT T2_PK PRIMARY KEY (K2)
) ; |
Pour comprendre un peu le comportement des moteurs relationnels, considérons la modeste requête suivante :
1
2
| SELECT T1.*
FROM T1 INNER JOIN T2 ON T1.A1 = T2.X ; |
Les colonnes participant à la jointure sont A1 et X. Si ni l’une ni l’autre ne sont indexées, le SGBD va là aussi balayer complètement une des deux tables et pour chaque ligne lue, balayer complètement l’autre table pour produire un résultat qui vérifie la condition de jointure. Coût de l’opération (en reprenant les volumétries précédentes) :
30000 X 10000 accès logiques...
A nouveau ça n’est pas bien fameux... Supposons maintenant que les colonnes A1 et X soient indexées, respectivement par des index T1_A1_AK et T2_X_AK. Supposons encore que le SGBD commence par exploiter la table T1 : en fait, il ne balaie pas la table, mais l’index T1_A1_AK, et pour chaque valeur lue V, le SGBD cherche cette valeur V dans l’index T2_X_AK, mais cette fois-ci par accès direct, il n’y a plus de balayage complet, seulement deux ou trois lectures au plus dans l’index, on a mis le turbo. Coût de l’opération (en étant pessimiste, c'est-à-dire en misant sur 3 plutôt que 2 accès) :
30000 X 3 accès logiques.
Ce à quoi il faut ajouter un accès à la table, afin de récupérer les valeurs des autres colonnes (en effet on a codé SELECT T1.*) :
30000 X 4 accès logiques.
On a quand même changé d’échelle ! Si l’on observe que l’on n’a besoin en fait que de récupérer la clé K1, alors pour éviter cet accès supplémentaire à la table, les index seront créés au moyen des instructions suivantes :
1
2
| CREATE UNIQUE INDEX T1_A1_AK ON T1 (A1, K1) ;
CREATE UNIQUE INDEX T2_X_AK ON T2 (X) ; |
Et le SELECT sera réécrit ainsi :
1
2
| SELECT T1.K1
FROM T1 INNER JOIN T2 ON T1.A1 = T2.X ; |
A cette occasion, il faut observer que la taille des enregistrements index étant réduite à pas grand-chose, le nombre d’accès physiques sera beaucoup plus réduit que celui des accès logiques.
=>
Merci de nous communiquer la durée de ce modeste SELECT.
Il est clair que toutes les colonnes participant au SELECT ci-dessous seront soumises au même régime d’indexation, sinon on repart pour des durées infinies :
1
2
3
4
5
6
7
| CREATE UNIQUE INDEX T1_A2_AK ON T1 (A2, K1) ;
CREATE UNIQUE INDEX T1_A3_AK ON T1 (A3, K1) ;
CREATE UNIQUE INDEX T1_B1_AK ON T1 (B1, K1) ;
CREATE UNIQUE INDEX T1_B2_AK ON T1 (B2, K1) ;
CREATE UNIQUE INDEX T1_B3_AK ON T1 (B3, K1) ;
CREATE UNIQUE INDEX T2_X_AK ON T2 (X, K2) ;
CREATE UNIQUE INDEX T2_Y_AK ON T2 (Y, K2) ; |
Ces index pénalisant les opérations de mise à jour (eux aussi doivent être mis à jour...), une fois le traitement terminé, on aura intérêt à effectuer le DROP des index qui ne serviront pas pour les autres traitements.
En attendant, il s’agit maintenant de transformer le produit en somme. Les « OR » étant les empêcheurs de tourner en rond, on les remplace par des UNION, en fait des UNION ALL pour gagner encore du temps.
Voici une requête simulant plus ou moins la vôtre est la suivante :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| SELECT DISTINCT S1.K1
FROM
(
SELECT K1 FROM T1 INNER JOIN T2 ON T1.A1 = T2.X
UNION ALL
SELECT K1 FROM T1 INNER JOIN T2 ON T1.A2 = T2.X
UNION ALL
SELECT K1 FROM T1 INNER JOIN T2 ON T1.A3 = T2.X
) AS S1
INNER JOIN
(
SELECT K1 FROM T1 INNER JOIN T2 ON T1.B1 = T2.Y
UNION ALL
SELECT K1 FROM T1 INNER JOIN T2 ON T1.B2 = T2.Y
UNION ALL
SELECT K1 FROM T1 INNER JOIN T2 ON T1.B3 = T2.Y
) AS S2
ON S1.K1 = S2.K1 ; |
Du fait de la présence de UNION ALL, Un SGBD normalement constitué concatène les résultats des SELECT élémentaires, d’où l’effet de somme.
Ainsi, le nombre d’accès logiques pour le du 1er bloc :
1
2
3
4
5
| SELECT K1 FROM T1 INNER JOIN T2 ON T1.A1 = T2.X
UNION ALL
SELECT K1 FROM T1 INNER JOIN T2 ON T1.A2 = T2.X
UNION ALL
SELECT K1 FROM T1 INNER JOIN T2 ON T1.A3 = T2.X |
Est de l’ordre de :
30000 X 3 + 30000 X 3 + 30000 X 3
Même chose en ce qui concerne le 2e bloc :
1
2
3
4
5
| SELECT K1 FROM T1 INNER JOIN T2 ON T1.B1 = T2.Y
UNION ALL
SELECT K1 FROM T1 INNER JOIN T2 ON T1.B2 = T2.Y
UNION ALL
SELECT K1 FROM T1 INNER JOIN T2 ON T1.B3 = T2.Y |
Soit en tout, à la louche (on est loin des 3.10^12 ou des 3.10^15...) :
54 x 10^4
Mais, comme on l’a évoqué, avec un nombre d’accès physiques beaucoup plus réduit du fait qu’on récupère seulement les valeurs de la clé K1 au lieu de l’ensemble des lignes.
A ceci, il faut bien sûr ajouter la consommation résultant de la jointure des deux blocs.
=>
Dites-nous si on arrive à des temps de traitement raisonnables (et quels sont-ils) :
1) Pour le 1er paquet, celui qui précède l’INNER JOIN,
2) Pour le 2e paquet, celui qui suit l’INNER JOIN,
3) pour la jointure complète.
Sinon, on trouvera bien des astuces pour y parvenir.
Tout SGBD relationnel propose une instruction EXPLAIN permettant de savoir objectivement comment les choses se passent quant à l’utilisation des index et des stratégies mises en œuvre par l’optimiseur pour accéder aux données. Avec ACCES, il existe quelque chose qui s’appelle SHOWPLAN, mais pour ma part je n’ai pas eu l’occasion de m’en servir, peut-être Fabien a-t-il un avis ?
N.B. Au besoin, la toute 1re ligne de la requête, à savoir SELECT DISTINCT S1.K1 doit pouvoir, sans grand risque, être remplacée par SELECT DISTINCT S1.*, puisque la sélection n’ est effectuée qu’en dernier lieu.
Plantage requête classique
Répondre avec citation
)
Partager