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 :
Code:
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 |
2/ créons une table T2, référençant la table T1, avec une ligne pour les num de 5000 à 10 000 :
Code:
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 |
3/ créons un index sur T1(num)
Code:
1
2
|
CREATE INDEX IX_T1_num ON T1(num) |
4/ exécutons une requête avec TOP(1), ordonnée selon l'index :
Code:
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.num |
Citation:
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.
5/ puis la même requête avec une variable :
Code:
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.num |
Citation:
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.
Que s'est-il passé ?
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:
1
2
3
4
5
6
7
|
DELETE FROM T2
INSERT INTO T2(t1_i)
SELECT i
FROM T1
WHERE num%2 = 0 |
Code:
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.num |
Citation:
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.
Pour 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é :)
