Statistiques de distribution : clé de la performance

Et c’est vraiment un point majeur à garder à l’esprit lorsque l’on parle de performance. En effet, tout comme les variables déclarées localement, la variable de type table n’existe pas au moment de la  compilation. Le moteur ne peut donc pas définir de statistiques de distribution. Par conséquent, la cardinalité estimée d’une variable de type table sera toujours égale à 1, quel que soit le nombre de lignes réelles qu’elle contient. D’où un impact sur la performance.

Démonstration :
Soit une table Catalog qui contient 10 millions de lignes. Nous allons écrire une requête qui fait une simple jointure avec d’abord une table temporaire, puis nous remplacerons la table temporaire par une variable de type table pour ensuite comparer les plans et temps d’exécution.

Tout d’abord donc, la jointure avec la table temporaire, nommée #TemporayTable. Voici son plan d’exécution :

Procédons maintenant à la même requête avec une variable de type table @VariableTable (voir figure suivante). On notera au passage que le plan d’exécution concernant la table temporaire a été parallélisé, pas celui concernant la variable de type table. Cela est dû au fait que les variables de type table ne supportent pas les plan parallélisé.

Regardons maintenant de plus près les IO générés par les deux plans (obtenus grâce à la commande (SET STATISTICS IO ON).

• Avec la table temporaire.

Table ‘Catalog’. Scan count 28, logical reads 420460, physical reads 50, read-ahead reads 4192, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#temporaryTable. Scan count 25, logical reads 505308, physical reads 0, read-ahead reads 26, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

• Avec la variable de type table :

Table ‘Catalog’. Scan count 42014124, logical reads 116369249, physical reads 12011, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#7C4F7684’. Scan count 1, logical reads 505308, physical reads 1, read-ahead reads 505299, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Pour la table temporaire, du fait de statistiques à jour, l’optimiseur choisit une jointure de type Hash match, ce qui entraine 420 460 lectures de la table catalog. Les problèmes commencent lorsque l’on utilise une variable de type table qui n’a aucune statistique. Comme vu précédemment, le nombre de ligne estimé est systématiquement de 1 pour une variable de type table. L’optimiseur en déduit que la meilleure jointure sera un nesteed loop, du fait du faible nombre d’entrées d’une des deux tables.

Mais voilà : dans la réalité, la variable de type table contient 10 503 531 lignes. Au lieu de ne faire qu’une lecture de la table catalog comme le moteur l’avait estimé en se basant sur une cardinalité de 1 pour la variable de type table, l’exécution réelle génère 116 369 249 lectures logiques et finalement, une durée d’exécution dix fois plus importante.

Une dernière information concernant les statistiques : le seuil de recalcul des statistiques d’une table temporaire contient un seuil en plus de celui des tables classiques. En effet, pour ces dernières le seuil de recalcul est par défaut à 20 % de lignes modifiées + 500 lignes pour les tables de plus de 500 lignes, et à 500 pour les tables contenant 500 lignes ou moins. Pour les tables temporaires, nous avons un seuil en plus qui est de 6 pour les tables ayant 6 lignes ou moins.

Compilation

Une autre différence en termes de performances concerne la compilation lorsque ces tables sont utilisées dans des procédures stockées. Une variable de type table ne va jamais provoquer de process de recompilation. Une table temporaire quant à elle peut parfois générer un process de compilation systématique, à chaque appel. Par  exemple, j’ai expliqué préalablement qu’une table temporaire pouvait être utilisée dans le cadre de procédures stockées imbriquées. C’est un des cas qui va générer une compilation systématique.

Démonstration :

Soit la procédure stockée suivante, qui crée une table temporaire, puis qui appelle une deuxième procédure qui va utiliser cette table :

CREATE PROCEDURE [dbo].[Sp_call]
AS
BEGIN
CREATE TABLE #MyTemporaryTable (
[ID] int,
[creationdate] datetime,
[server] varchar(250),
[instance] varchar(250),
[base] varchar(250)
)
exec [dbo].[SP_TemporaryTable]
END
CREATE PROCEDURE [dbo].[SP_TemporaryTable]
AS
BEGIN
INSERT INTO #MyTemporaryTable SELECT top 10 * FROM
[dbo].[Catalog]
Select count(*) FROM #MyTemporaryTable
END

À l’aide du profiler, voyons ce qu’il se passe en termes de compilation si l’on appelle notre procédure sp_call en boucle : voir figure ci-dessous.

(((IMG6587)))

Comme vous pouvez le constater, nous avons une recompilation de l’instruction INSERT INTO à chacun des appels. Ce qui peut être très pénalisant pour la CPU entre autre, si nous rencontrons ce cas dans des procédures appelées plusieurs dizaines ou centaine de fois par seconde. L’article suivant énumère les différents cas dans lesquels les tables temporaires génèrent de la compilation. Je vous invite à le lire attentivement !