Tables temporaires et variables de type table, débat en cours…

En résumé notre étude a montré entre autres que les variables de types tables sont pratiques car elles peuvent être créées au sein de fonctions utilisateurs, par contre en terme de performances pures, les tables temporaires s’avéraient plus puissantes de par la possibilité de leur ajouter index et statistiques de distribution.

Qu’en est-il maintenant, alors que nous pouvons créer des index sur les variables tables grâce à cette nouvelle syntaxe ?

Les variables table indexées

Allons-y : créons notre première variable table indexée :

DECLARE @VarTable_IDX TABLE
(
Col1 int,
Col2 Varchar(255),
INDEX IDX_NCL_Col1Col2 NONCLUSTERED(col1)
)

Super ! Pas d’erreur : notre table et son index associés sont bien créés. Voyons voir maintenant ce qu’il en est des performances. Nous allons comparer les performances entre deux variables table, l’une étant indexée, l’autre non : voir listing 2.

• Création d’une variable table indexé

DECLARE @VarTable_IDX TABLE
(
Col1 int,
Col2 Varchar(255),
INDEX IDX_NCL_Col1 NONCLUSTERED(col1)
)

• Création de la même variable table, mais sans l’index

DECLARE @VarTable TABLE
(
Col1 int,
Col2 Varchar(255)
)

• Ajout de 1000 lignes dans les deux tables

DECLARE @i INT = 0
WHILE @i < 1000
BEGIN
INSERT INTO @VarTable_IDX VALUES(@i, replicate(‘a’, 255))
INSERT INTO @VarTable VALUES(@i, replicate(‘b’, 255))
SET @i=@i+1
END

• Comparaison des perfs entre les deux : select sur la colonne indexée

set statistics IO ON
set statistics TIME ON
SELECT * FROM @VarTable_IDX where col1>= 25 and col1 <= 35
SELECT * FROM @VarTable where col1>= 25 and col1 <= 35
set statistics IO OFF
set statistics TIME OFF

Et voici le résultat :

Table ‘#A989C52B’. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table ‘#AA7DE964’. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Aucune différence ! Et si l’on compare les plans d’exécution, ce sont les mêmes dans les deux cas : voir listing 3.

(((IMG7604)))

Nous avons un scan de table pour les deux requêtes, avec un nombre de lignes estimé à 1. Et oui ! S’il est effectivement possible de créer des index sur notre variable table, le moteur ne crée ni ne maintient aucune statistique de distribution dessus, car les statistiques sont créées au moment de la compilation, le moteur n’a aucune idée du nombre de lignes contenu dans la variable table à ce moment-là !

D’où une estimation toujours à 1.

Mais je n’ai pas dit mon dernier mot. Faisons le même test, mais avec un index couvrant sur notre variable table :

• Création d’une variable table indexée

DECLARE @VarTable_IDX TABLE
(
Col1 int,
Col2 Varchar(255),
INDEX IDX_NCL_Col1Col2 NONCLUSTERED(col1, col2)
)

Voici le résultat :

Table ‘#B8CC08BB’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table ‘#B9C02CF4’. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

4 logical read pour la variable table indexée contre 35 pour la non indexée.

Et le plan d’exécution de notre variable table indexée a changé : nous sommes passés sur un index Seek :

(((IMG7605)))

Effectivement, dans le cadre d’un index couvrant, le moteur privilégiera le parcours par l’index plutôt que le scan de la table, économisant ainsi des lectures.

Autrement dit, si l’on veut tirer avantages d’un index sur une variable table, il faut que cet index soit couvrant, autrement dit, contienne l’ensemble des colonnes de la requête sous-jacente. Sachant que l’on ne peut ajouter de clause INCLUDE sur ce type d’index, la question du coût de l’index jouera dans la décision de son implémentation.