Absolument : c'est pour ça que nous avons besoin de savoir ce qu'avait prévu l'optimiseur de requêtes, et de le comparer à la façon dont ça s'est réellement exécuté
Absolument : c'est pour ça que nous avons besoin de savoir ce qu'avait prévu l'optimiseur de requêtes, et de le comparer à la façon dont ça s'est réellement exécuté
C'est bien gentil cette discussion mais ça en est ou maintenant ? Merci
Mon avatar ? Ce n'est rien, c'est juste la tête que je fais lorsque je vois un code complètement frappa dingue !...
Désolé, mais j'ai pas eu le temps de m'en occuper, d'autant que j'ai changé de machine entre temps (donc il faut que je reinstalle les différents outils)
Mais ça reste quand même bizarre comme comportement
Framework php sécurisé et simple à prendre en main avec générateur web http://mkframework.com/ (hebergé sur developpez.com)
Mes cours/tutoriaux
Oui j'explique pas trop mais en effet, seul le schéma du plan d'exécution peut nous éclairer. Derrière le cas du TOP x dont personnellement je l'utilise pour des requêtes à chaud ça peut, éventuellement, être utile dans des cas spécifiques et optimiser le temps d'exécution si nous comprenons le mécanisme.
Mon avatar ? Ce n'est rien, c'est juste la tête que je fais lorsque je vois un code complètement frappa dingue !...
Bizarre, pas vraiment : le moteur SQL s'appuie sur des statistiques pour trouver le meilleur plan possible. Et les statistiques... ce sont des statistiques !
La réalité est parfois très différente que ce qu'indiquent les statistiques !
Prenons un exemple :
1/ créons une table T1, avec un clef primaire et une colonne num, contenant les nombre de 1 à 10 000 :
2/ créons une table T2, référençant la table T1, avec une ligne pour les num de 5000 à 10 000 :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10 CREATE TABLE T1( i INT NOT NULL PRIMARY KEY IDENTITY ,num INT NOT NULL ) GO INSERT INTO T1(num) SELECT TOP(10000) ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM sys.objects A, sys.objects B, sys.objects C
3/ créons un index sur T1(num)
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12 CREATE TABLE T2 ( j INT NOT NULL PRIMARY KEY IDENTITY ,t1_i INT NOT NULL FOREIGN KEY REFERENCES t1(i) ) GO INSERT INTO T2(t1_i) SELECT i FROM T1 WHERE num > 5000
4/ exécutons une requête avec TOP(1), ordonnée selon l'index :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 CREATE INDEX IX_T1_num ON T1(num)
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6 SELECT TOP(1) t1.i FROM T1 INNER JOIN T2 ON T2.T1_i = T1.i ORDER BY T1.num5/ puis la même requête avec une variable :Table 'T2'. Nombre d'analyses 1, lectures logiques 110003, ...
Table 'T1'. Nombre d'analyses 1, lectures logiques 9, ...
(1 ligne(s) affectée(s))
SQL Server \endash Temps d'exécution :
, Temps UC = 2792 ms, temps écoulé = 2910 ms.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9 DECLARE @x INT = 1; SELECT TOP(@x) t1.i FROM T1 INNER JOIN T2 ON T2.T1_i = T1.i ORDER BY T1.numQue s'est-il passé ?Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0,...
Table 'T1'. Nombre d'analyses 1, lectures logiques 16, lectures ...
Table 'T2'. Nombre d'analyses 1, lectures logiques 22, lectures ...
(1 ligne(s) affectée(s))
SQL Server \endash Temps d'exécution :
, Temps UC = 16 ms, temps écoulé = 52 ms.
Pour la requête TOP(1), SQL server utilise l'index. D'après les statistiques, la jointure entre T1 et T2 donnera un résultat pour une ligne sur deux de T1. L'utilisation de l'index semble donc en effet intéressante, malgré la boucle imbriquée qu'elle engendre pour trouver la correspondance dans T2.
Mais dans la pratique, s'est en fait contre performant, car SQL Server doit traiter les 5001 premières lignes de l'index sur T1 avant de trouver une ligne correspondante dans T2. On est très loin des deux lignes qui avaient été estimées.
Mais alors pourquoi la requête avec TOP(@x) est-elle plus rapide ?
Lors de la compilation de la requête, la valeur de @x n'est pas connue de SQL Server. Il construit donc un plan de requête générique, pour lequel il estime arbitrairement que @x vaudra 100. SQL Server estime donc qu'il lui faudrait traiter 200 lignes de l'index s'il utilisait le même type de plan que pour la première requête. Il estime alors que la boucle imbriquée serait trop couteuse (ce qui se passe d'ailleurs en réalité dans la requête 1) et préfére effectuer directement la jointure complète, puis un tri afin de ne prendre que les 100 premières lignes (une seule au final lors de l’exécution puisque @x vaut en fait 1).
Ici, on ne peut donc pas vraiment dire que le plan généré pour la requête top(1) soit mauvais en soit, c'est juste la répartition des données qui engendre ce phénomène. D'ailleurs, si on met des données non pas pour les 5000 dernières valeurs de num, mais pour les valeurs paires afin d'avoir une répartition plus homogène, la requête avec TOP(1) devient en effet plus efficace que la requête avec TOP(@x) :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 DELETE FROM T2 INSERT INTO T2(t1_i) SELECT i FROM T1 WHERE num%2 = 0
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6 SELECT TOP(1) t1.i FROM T1 INNER JOIN T2 ON T2.T1_i = T1.i ORDER BY T1.numPour conclure, l'origine de ce comportement vient du fait que même si vos requêtes sont strictement équivalentes au moment de l'éxécution quand @x vaut 1, elles donnent lieux chacune à la génération d'un plan d’exécution propre, et que dans le cas du plan avec @x, SQL server lui attribue par défaut la valeur 100. Si vous le forcez à estimer que @x = 1, en ajoutant l'option OPTION(OPTIMIZE FOR(@x=1)) à la fin de votre requête, vous devriez retomber sur le même plan d’exécution que pour la requête TOP(1) et donc retrouver la même lenteur... ce qui n'est certes pas le but recherchéTable 'T2'. Nombre d'analyses 1, lectures logiques 27,...
Table 'T1'. Nombre d'analyses 1, lectures logiques 2,...
(1 ligne(s) affectée(s))
SQL Server \endash Temps d'exécution :
, Temps UC = 0 ms, temps écoulé = 1 ms.
Merci beaucoup pour cette réponse, elle est pertinente même si embêtante
Framework php sécurisé et simple à prendre en main avec générateur web http://mkframework.com/ (hebergé sur developpez.com)
Mes cours/tutoriaux
Ce n'est pas forcément embêtant.
Si votre "problème" est bien similaire a celui-ci (ce que vos plans d'éxécution pourraient confirmer), alors il existe plusieurs solutions pour le contourner, dont certaines pourraient être même très avantageuses.
Par exemple, si vous savez que cette requête sera exécutée souvent, et que vous voulez de bonnes performances, vous pouvez créer une vue et l'indexer :
SQL server dispoe alors d'un nouvel index, qu'il ne se privera pas d'utiliser dans la requête TOP(1) :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12 CREATE VIEW V_T1T2 WITH SCHEMABINDING AS SELECT T1.i, T1.num FROM dbo.T1 INNER JOIN dbo.T2 ON T2.T1_i = T1.i GO CREATE UNIQUE CLUSTERED INDEX IX_VT1T2 ON dbo.V_T1T2(num,i) GO
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6 SELECT TOP(1) t1.i FROM T1 INNER JOIN T2 ON T2.T1_i = T1.i ORDER BY T1.numBien sûr, si T1 et/ou T2 est mise à jour très régulièrement, cette vue indexée pourrait aussi avoir un impact négatif... Il faut donc bien connaitre le contexte avant de choisir la solution la plus adaptée.Table 'V_T1T2'. Nombre d'analyses 1, lectures logiques 2, ...
(1 ligne(s) affectée(s))
SQL Server \endash Temps d'exécution :
, Temps UC = 0 ms, temps écoulé = 0 ms.
EDIT : Avec une édition entreprise, SQL server saura utiliser l'index de la vue même si la requête ne fait pas explicitement référence à la vue. En revanche, il me semble qu'avec une édition express, il faudra modifier la requête... mais le résultat sera le même !
Bonsoir,
Juste une petite question.
C’est bien vrai ! Mais à l’instar de DB2 for z/OS, y at-il des tables du catalogue relationnel où certaines colonnes (dédiées aux statistiques) peuvent être mises à jour manuellement ?
Du temps (bien lointain) où je prototypais les performances des requêtes, ça me rendait d’immenses services, puisqu’avant que ne commencent les développements applicatifs, je pouvais valoriser ces colonnes, simuler des volumétries de N millions de lignes pour des tables en réalité vides, et connaître ainsi de façon anticipée la stratégie de l’optimiseur pour les requêtes.
Merci,
François
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
Hello François,C’est bien vrai ! Mais à l’instar de DB2 for z/OS, y at-il des tables du catalogue relationnel où certaines colonnes (dédiées aux statistiques) peuvent être mises à jour manuellement ?
Du temps (bien lointain) où je prototypais les performances des requêtes, ça me rendait d’immenses services, puisqu’avant que ne commencent les développements applicatifs, je pouvais valoriser ces colonnes, simuler des volumétries de N millions de lignes pour des tables en réalité vides, et connaître ainsi de façon anticipée la stratégie de l’optimiseur pour les requêtes.
Oui il est possible de valoriser certaines colonnes sans même avoir de données représentatives en jouant avec les statistiques de SQL Server. Pour cela il faut utiliser UPDATE STATISTICS et les options ROWCOUNT et PAGECOUNT.
++
Bonjour David,
Merci à toi.
Il faudra que j'aille faire mumuse avec ça.
A la prochaine !
(a) Faites simple, mais pas plus simple ! (A. Einstein)
(b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
=> La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)
__________________________________
Bases de données relationnelles et normalisation : de la première à la sixième forme normale
Modéliser les données avec MySQL Workbench
Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager