Discussion :

[Immobilis]

Salut,

Dans cet article SQLpro promeut les JOINTURE entre les tables. C'est effectivement très pratique. Toutefois, j'ai pu constater dans le cas d'utilisation de requetes assez complexes (avec des sous-requêtes) que la réponse était beaucoup plus rapide en filtrant qu'en utlisant des jointures.

Comment expliquer ce phénomène?

A+

[SQLpro]

Par une mauvaise indexation.

Avez vous indexé TOUTES les clefs étrangères de vos tables ?

Avez vous indexé les principales expressions les plus fréquemment filtrées ?

A quelques exceptions près les jointures devraient être plus performantes dans bien des cas...

A +

[fsmrel]

j'ai pu constater dans le cas d'utilisation de requetes assez complexes (avec des sous-requêtes) que la réponse était beaucoup plus rapide en filtrant qu'en utlisant des jointures.

Comment expliquer ce phénomène?

Votre constat est trop général...

Merci de fournir quelques exemples illustrant vos propos.

En passant, n'oubliez pas d'étudier l'instruction SET SHOWPLAN qui vous permettra de comprendre comment procède un optimiseur de SGBD relationnel quant à sa stratégie d'exécution des requêtes.

[seminoque]

J'ai pu constater un problème similaire en voulant récupérer des informations dans de nombreuses tables.

Dans un premier temps j'ai essayé d'obtenir le résultat avec un seul SELECT mais les performances étaient catastrophique et le plan d'exécution retenu par l'optimiseur était mauvais malgré les index, clés et stats existantes. Le seul moyen d'améliorer le résultat était de forcer l'ordre d'accès aux tables et l'utilisation d'index .

J'ai ensuite écrit une première requête avec quelques tables, stocké le résultat dans une table temporaire, puis écrit une deuxième requête utilisant la table temporaire et quelques tables en plus, stocké le résultat dans une deuxième table temporaire etc... (ce qui ressemble aux sous-requêtes que tu décris) et l'optimiseur choisissait les bons index.

En fait quand on soumet une requête à SQL Server, l'optimiseur analyse les différentes manières de l'exécuter (dans quel ordre accèder aux tables, quel index utiliser etc.) et associe à chacune d'elle un coût. Finalement, il retient le plan d'exécution le moins coûteux.
Le problème c'est qu'avec une requête complexe le nombre de plan d'exécutions possible peut être élevé. L'optimiseur ne les évalue pas tous et peut retenir alors un plan non optimal. Le fait d'écrire des sous-requête (ou d'utiliser des tables temporaires) permet à l'optimiseur de traiter plusieurs requêtes simples plutôt qu'une seule compliquée.

D'ailleurs il me semble que dans la documentation de SQL Server il est conseillé d'éviter plus de trois ou quatre jointures dans une requête.

http://www.bingokaz.com

[SQLpro]

D'ailleurs il me semble que dans la documentation de SQL Server il est conseillé d'éviter plus de trois ou quatre jointures dans une requête.

je serais curieux que vous puissiez me trouver cela !!! Étant un expert SQL Server, je n'ai jamais lu pareille chose...

Le problème c'est qu'avec une requête complexe le nombre de plan d'exécutions possible peut être élevé.

OUI

L'optimiseur ne les évalue pas tous et peut retenir alors un plan non optimal.

Rarement. Les optimiseurs utilisent des algorithmes de backtracking ce qui fait qu'heureusement tous les plans ne sont pas évalués, mais uniquement ceux dont le coût relatif à la branche est optimal...

Le seul moyen d'améliorer le résultat était de forcer l'ordre d'accès aux tables et l'utilisation d'index .

Je met sérieusement en doute votre jugement... Apportez des exemples concrets !
la plupart du temps il s'agit d'index mal foutus et rarement maintenu...
Par exemple des index multi colonnes inutilisables et une maintenance des index jamais opérée !

Lisez les articles que j'ai écrit sur l'indexation :
http://sqlpro.developpez.com/optimisation/indexation/
L'optimisation :
http://sqlpro.developpez.com/optimisation/
et la maintenance des index :
http://sqlpro.developpez.com/optimis...ntenanceIndex/

Pour ma part, faisant de l'audit SQL Server depuis maintenant une dizaine d'années, j'ai TOUJOURS constaté le contraire !

A +

[fsmrel]

Bonjour,


Je ne suis pas spécialiste ès SQL Server, mais toutes choses égales par ailleurs, si l’on applique à DB2 le verdict de SQLpro, je confirme. Comme le sous-entend celui-ci, pour juger il faut démontrer et donc commencer par présenter des cas concrets complets, en sorte que l’on puisse analyser les structures logiques (tables et vues), l’organisation physique (index), la qualité des requêtes, la pertinence de la collecte des statistiques, l’état de désorganisation des objets physiques, etc. A défaut, on en restera à des bavardages oiseux et à des jugements de valeur sans fondements objectifs, émotionnels, voire dangereux.

[seminoque]

Je suis un peu déçu par les réponses de SQLPro. L'argument d'autorité, "je suis expert depuis X années", ne fait pas beaucoup avancer le débat. La question d'Immobilis étant générale je lui ai apporté une réponse générale. Enfin en renvoyant systématiquement vers les tutoriels, le forum perd beaucoup de son intérêt, même si je comprends que répondre sans arrêt aux mêmes questions, parfois mal formulées, est sans doute lassant.

Mais revenons sur le fond du sujet qui est bien plus intéressant que des disputes stériles.

La documentation SQL Server (2005) n'indique pas explicitement de limite de trois ou quatre tables jointes. Dans la documentation de référence sur la clause FROM il y a une limite à... 256 tables

(...) Vous pouvez utiliser jusqu'à 256 sources de table dans une instruction, bien que cette limite varie en fonction de la mémoire disponible et de la complexité des autres expressions constituant la requête. Les requêtes individuelles, pour leur part, n'acceptent pas toujours 256 sources de table.
(...)
Remarque :
Les performances des requêtes risquent de baisser si le nombre des tables référencées dans une requête est élevé. Les durées de compilation et d'optimisation sont également affectées par d'autres facteurs. Parmi ceux-ci figurent les index
(...)
L'ordre des sources de table derrière le mot clé FROM n'a aucune incidence sur l'ensemble de résultats retourné.
(...)

Mais je faisais en fait référence à cet autre partie de la documentation concernant la normalisation :

(...)
Lorsque la normalisation augmente, le nombre et la complexité des jointures nécessaires pour récupérer les données augmentent aussi. Or, des jointures relationnelles trop complexes entre des tables trop nombreuses peuvent avoir un effet néfaste sur les performances. Un niveau raisonnable de normalisation se traduit souvent par peu de requêtes exécutées à intervalles réguliers qui utilisent des jointures faisant intervenir plus de quatre tables.
(...)

Ce qui me semble être un conseil de bon sens, non ? Mais après la théorie, un peu de pratique.

Je n'ai malheureusement plus cette fameuse requête plus efficace lorsqu'elle est découpée en morceaux utilisant des tables temporaires que lorsqu'elle est envoyée d'un seul bloc à l'optimiseur. C'était il y a plusieurs années sur un SQL Server 2000. Il a donc fallu que je me creuse la tête pour trouver l'exemple suivant. J'espère qu'il se comportera de la même manière dans l'environnement de ceux qui voudront bien le tester.

4 tables avec des clés primaires de type entier (clustered) et des clés étrangères (non clustered). J'ai ajouté des colonnes pleines de "--------" pour remplir des pages de données. T1 est liée à T2, T3 à T4 et T2 est liée deux fois à T3 (certes on pourrait faire une table de lien N-N entre T2 et T3 mais passons...).

Voici le script d'initialisation :

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
CREATE TABLE T1 (T1_PK INT NOT NULL, T1_T2 INT NOT NULL, T1_V1 VARCHAR(10), T1_V2 VARCHAR(10))
CREATE TABLE T2 (T2_PK INT NOT NULL, T2_T31 INT NOT NULL, T2_T32 INT NOT NULL, T2_V1 VARCHAR(10), T2_V2 VARCHAR(10))
CREATE TABLE T3 (T3_PK INT NOT NULL, T3_T4 INT NOT NULL, T3_V VARCHAR(10))
CREATE TABLE T4 (T4_PK INT NOT NULL,T4_V INT NOT NULL)
 
DECLARE @N INT
--Nombre d'enregistrement dans les tables (attention ce nombre est multiplié par 200 pour remplir certaines tables !!!)
SET @N=100
 
DECLARE @V INT
SET @V=1
 
WHILE (@V<=@N)
BEGIN
	INSERT T4 (T4_PK,T4_V) VALUES (@V, @N-@V)
	SET @V=@V+1
END
 
SET @V=1
DECLARE @R INT
SET @R=1
 
WHILE (@V<=200*@N)
BEGIN
	IF @R>@N
	BEGIN
		SET @R=1
	END
 
	INSERT T3 (T3_PK, T3_T4, T3_V) VALUES (@V, @R, '----------')
 
	SET @R=@R+1
	SET @V=@V+1
END
 
SET @V=1
SET @R=1
 
WHILE (@V<=100*@N)
BEGIN
	IF @R>@N
	BEGIN
		SET @R=1
	END
 
	INSERT T2 (T2_PK, T2_T31, T2_T32, T2_V1, T2_V2) VALUES (@V,@V,@V+@N,'----------','----------')
 
	SET @R=@R+1
	SET @V=@V+1
END
 
SET @V=1
SET @R=1
 
WHILE (@V<=100*@N)
BEGIN
	IF @R>@N
	BEGIN
		SET @R=1
	END
 
	INSERT T1 (T1_PK, T1_T2, T1_V1, T1_V2) VALUES (@V,@V,'----------','----------')
 
	SET @R=@R+1
	SET @V=@V+1
END
 
ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (T1_PK)
ALTER TABLE T2 ADD CONSTRAINT PK_T2 PRIMARY KEY CLUSTERED (T2_PK)
ALTER TABLE T3 ADD CONSTRAINT PK_T3 PRIMARY KEY CLUSTERED (T3_PK)
ALTER TABLE T4 ADD CONSTRAINT PK_T4 PRIMARY KEY CLUSTERED (T4_PK)
 
ALTER TABLE T1 ADD CONSTRAINT FK_T1_T2 FOREIGN KEY (T1_T2) REFERENCES T2 (T2_PK)
ALTER TABLE T2 ADD CONSTRAINT FK_T2_T31 FOREIGN KEY (T2_T31) REFERENCES T3 (T3_PK)
ALTER TABLE T2 ADD CONSTRAINT FK_T2_T32 FOREIGN KEY (T2_T32) REFERENCES T3 (T3_PK)
ALTER TABLE T3 ADD CONSTRAINT FK_T3_T4 FOREIGN KEY (T3_T4) REFERENCES T4 (T4_PK)
 
CREATE NONCLUSTERED INDEX INC_T1_T2 ON T1 (T1_T2)
CREATE NONCLUSTERED INDEX INC_T2_T31 ON T2 (T2_T31)
CREATE NONCLUSTERED INDEX INC_T2_T32 ON T2 (T2_T32)
CREATE NONCLUSTERED INDEX INC_T3_T4 ON T3 (T3_T4)
CREATE NONCLUSTERED INDEX INC_T3_V ON T3 (T3_V)
CREATE NONCLUSTERED INDEX INC_T4_V ON T4 (T4_V)

Ensuite je compare le comportement des deux requêtes suivantes identiques par leur résultat (zéro lignes renvoyée) mais dans le premier cas je laisse faire l'optimiseur et dans le second je force l'ordre d'accès aux tables.

En les exécutant toutes les deux en même temps et en affichant le plan d'exécution la première coûte moins de 50% de l'ensemble et la seconde plus de 50%. Apparemment l'optimiseur est le meilleur. Mais en regardant les plans dans le détail on se rend compte que le plan choisi par l'optimiseur nécessite plus d'accès aux données

En affichant les statistiques d'accès aux pages de données (SET STATISTICS IO ON) on constate que le plan forcé est moins consommateur que celui de l'optimiseur.

Enfin en affichant les statistiques client et en éxécutant 10 fois de suite une requête puis 10 fois de suite l'autre, la moyenne de l'indicateur "durée totale d'exécution" est plus faible avec le plan forcé.

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
 
SELECT *
FROM T1
INNER JOIN T2 ON (T1_T2=T2_PK)
INNER JOIN T3 AS T31 ON (T2_T31=T31.T3_PK)
INNER JOIN T4 AS T41 ON (T31.T3_T4=T41.T4_PK)
INNER JOIN T3 AS T32 ON (T2_T32=T32.T3_PK)
INNER JOIN T4 AS T42 ON (T32.T3_T4=T42.T4_PK)
WHERE T41.T4_PK BETWEEN @N/2 AND @N
AND T42.T4_PK BETWEEN @N/10 AND @N/5
 
SELECT *
FROM T3 AS T32
INNER JOIN T2 ON (T2_T32=T32.T3_PK)
INNER JOIN T3 AS T31 ON (T2_T31=T31.T3_PK)
INNER JOIN T4 AS T41 ON (T31.T3_T4=T41.T4_PK)
INNER JOIN T4 AS T42 ON (T32.T3_T4=T42.T4_PK)
INNER JOIN T1 ON (T1_T2=T2_PK)
WHERE T41.T4_PK BETWEEN @N/2 AND @N
AND T42.T4_PK BETWEEN @N/10 AND @N/5
OPTION(FORCE ORDER)

Je n'ai pas cherché à comprendre les raisons de ce comportement Je ne suis pas sûr que ce soit lié au problème du nombre de plan d'exécution évalués par l'optimiseur que j'évoquais dans mon précédent message, les écarts de performances sont faible, et il s'agit d'un cas très particulier.

Mais ceci prouve que même si l'optimiseur est extrêmement performant, il n'est pas inutile de reécrire des requêtes (exercice très instructif) voire d'envisager des plans forcés (ce qui ne m'est en fait jamais arrivé en dehors de tests) mais après avoir bien modélisé sa base, indexé ses tables et mis à jour les stats comme l'a indiqué SQLpro.

________________________________
Seminoque, créateur de
http://www.bingokaz.com

[SQLpro]

Le nombre d'IO est un premier indice.... Il doit être affiné par le temps CPU. En effet si pour deux plans différents le nombre de pages est le même, les algorithmes utilisés pour la jointure, le tri, le groupage peuvent prendre plus ou moins de ressources.

Il est donc tout à fait possible d'avoir un plan moins couteux au final bin que lisant plus de page.

C'est le but même du travail de l'optimiseur.

Sachez que vous aurez difficilement le dernier mot avec celui-ci car c'est l'un des mieux foutus de tous les SGBDR du marché !

A +