SelectEtoile : Base de connaissance SGBD

SQL 2014 : INLINE INDEX 1/2

1

 

SQL 2014 est arrivé avec son lot de nouveauté, dont la possibilité de créer des index "en ligne", c'est ce que je vais vous présenter dans ce post.

Présentation du concept :

L’idée est fort simple : nous avons dorénavant la possibilité de créer des index directement dans la commande CREATE TABLE.
Par exemple, l’instruction suivante créée une table nommée table_1 avec un index clustered sur la colonne 1 col1 et un index non clustered sur les colonnes 2 et 3 col2 et col3 :

 

CREATE TABLE Table_1
(
    Col1 int INDEX IDX_NCL_Col1 CLUSTERED (Col1),
    Col2 varchar(255) INDEX IDX_NCL_Col2Col3 NONCLUSTERED (Col2, Col3),
    Col3 int
)

 Vérification :

Nos index sont bien créés.

Petit point sur la syntaxe : la création de l'index peut se faire nous l'avons vu au niveau de la colonne indexée, ou encore après la déclaration des colonnes.
On aurais donc également pu créer la table précédente comme suit :

CREATE TABLE Table_1
(
    Col1 int,
    Col2 varchar(255),
    Col3 int,
    INDEX IDX_NCL_Col1 CLUSTERED (Col1),
    INDEX IDX_NCL_Col2Col3 NONCLUSTERED (Col2, Col3)
)

 Les deux écritures sont équivalentes.

OK, c'est bien mais à mon sens, l'intérêt de cette feature n'est pas là. C'est plutôt sur les changements qu'elle induit lorsqu'on l'utilise sur les objets temporaires @table ou #table qu'il faut regarder.

Les variables temporaires indexées:

Nous pouvons dorénavant grâce à cette syntaxe créer des variables table indexées, chose qui était alors impossible (si ce n'est en créant une PK, qui implicitement créée un index cluster sur les colonnes concernées --> pas très souple)


Voila ce que cela donne :

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

 

Cool, mais cela va-t-il réellement influer sur les perfs ? Comparons les performances entre une variable table indexées et une variable table non indexée :

--Création d'une variable table indexée
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 rapide des perfs entre les deux par un 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 les plans d’exécution sont les mêmes dans les deux cas : un simple table scan :



On remarque tout de suite que le nombre de lignes estimé est à 1. Hé oui, le bon vieux talon d'Achille des variables tables : le moteur les évalue au moment de l'exécution et non pas au moment de la compilation, et donc, est incapable d'estimer le nombre de lignes qu'elle va contenir. D'où une estimation systématique à 1.
Malgré la possibilité de créer des index dessus, le moteur de maintient ni ne créé de statistiques de distribution sur les variables table.
Aucun intérêt de leur ajouter des index dans ce cas! Vraiment ? Pas si sur ! Reprenons notre exemple, et tachons cette fois de créer un index couvrant :

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

Comme tout à l'heure, je rajoute des lignes dedans et compare un select avec la même variable table mais sans index. Cette fois, il semble bien que l'index nous serve !


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 reads pour la variable table indexée contre 35 pour la non indexée, et le plan a changé, nous avons dorénavant un index seek :




Dans le cadre d'un index couvrant, le moteur privilégiera le parcours de l'arbre d'index plutôt que le scan de la table, d'où l'optimisation.

Je vous parlerai dans un prochain post de l'impact sur les tables temporaires #Table...

O.


Ajouter un Commentaire


Code de sécurité
Rafraîchir