Advertisement
Loading

Rappel : débat entre tables temporaires et variables de type table

https://www.flickr.com/photos/110751683@N02/13334080323/

Souvenez-vous : j’avais écrit un article qui comparait les variables de type table (@table) avec les tables temporaires (#table). (www.itpro.fr/a/table-or-table).

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.

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 :

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.

123
Olivier Maître Olivier Maître - Administrateur de base de données
Olivier Maître est certifié MCITP SQL server database administrator et MCTS SQL Server Database Development.
 
Sur iTPro.fr, nous vous aidons à tirer le meilleur profit de vos environnements IT. Découvrez les analyses, les chroniques et plus de 4280 dossiers experts , profitez d’un savoir technologique unique et de ressources stratégiques exclusives pour vous accompagner dans le choix, la gestion et l’optimisation de vos environnements IT Professionnels. Bénéficiez d’une richesse éditoriale incomparable et vivez toute l’actualité IT professionnelle sur twitter avec #iTProFR
 
Comment sécuriser les accès et contrer la compromission d’identité ?Comment sécuriser les accès et contrer la compromission d’identité ?La compromission d’identité est un vecteur d’attaque conséquent auquel les organisations sont confrontées, et avec les applications Cloud, mobiles et Web, la surface d’attaque ne fait qu’augmenter. Découvrez en détail les technologies leader pour sécuriser les identités et les accès.Téléchargez le livre blanc

Ressources Informatiques

Comment répondre aux défis de la compromission d’identité ? La mobilité, la consumérisation et le BYOD impliquent de nouveaux défis de sécurité pour les directions IT. Découvrez comment sécuriser les accès…
   RSA | 4 pages
Découvrez le livre blanc
Les enjeux de l’authentification forte pour la sécurité du SI La mobilité, la consumérisation et le BYOD impliquent de nouveaux défis de sécurité pour les directions IT. Découvrez comment sécuriser les accès…
   RSA | 2 pages
Découvrez le livre blanc
SMART DSI : la nouvelle revue du décideur IT Conseil et expertise IT, bénéficiez des analyses, des chroniques et des dossiers de la nouvelle revue informatique SMART DSI pour conduire la transformation…
   SMART DSI | 52 pages
Découvrez la revue SMART DSI
Libérez le Big Data et le Machine Learning Nous sommes à la veille d'un changement spectaculaire où les machines seront capables de dépasser les êtres humains dans leur capacité à prendre…
   Comsoft | 12 pages
Découvrez l’article technique
BYOD : Guide des meilleures pratiques en entreprise Smartphones, tablettes, PC ultra-mobiles ont libéré les utilisateurs et transformé les interactions entre les collaborateurs. Encore faut-il que l’entre¬prise…
   HPE Aruba | 4 pages
Découvrez votre Guide de Stratégie Mobile
Optimiser la consommation énergétique du data center La gestion et la supervision des infrastructures IT n’ira qu’en s’aggravant tant que les entreprises n’adopteront pas une vraie gestion dynamique…
   Top 5 Décideur IT | 4 pages
Découvrez votre livre blanc
 

Informatique ProfessionnelleActualités, chroniques et dossiers IT experts

David Lachari David Lachari Expert Virtualisation

Nicolas Milbrand Nicolas Milbrand Consultant Microsoft indépendant

Fabrice Barbin Fabrice Barbin Microsoft MVP

Vidéos Informatiques

Comment mettre en place une stratégie mobile performante ?Smartphones, tablettes, PC ultra-mobiles ont libéré les utilisateurs et transformé…Par Itpro

Hyperconvergence réussie avec les solutions Dell VxRaill'hyperconvergence intéresse et séduit les DSI par son approche intégrée et…Par Itpro

Windows 10 Professionnel : 5 Innovations pour les CollaborateursRépondre aux enjeux de mobilité des utilisateurs est un besoin réel, centré…Par Itpro

Répondre aux défis de l'hyper convergence ?Découvrez en vidéo motion les 5 atouts majeurs du partenariat DELL EMC et Misco-inmac…Par Itpro

Les Assises de la Sécurité 2015 : Yves Rochereau - Check PointCheck Point est une société spécialisée dans l'univers de la sécurité informatique…Par Itpro

Conseil & Expertise IT

Bénéficiez des analyses, des chroniques et des dossiers
de la nouvelle revue informatique SMART DSI pour conduire
la transformation numérique de votre entreprise.

Abonnez-vous à la revue SMART DSI