[SQL Server] TempDB : tables temporaires vs tables variables

Ce billet offre un petit comparatif entre les tables temporaires et les tables variables (ou variables tables).

Pour connaître l’utilité et le fonctionnement de tempDB, les différents types d’objets temporaires et les éléments d’optimisation usuels, allez ici.

Présentation…

    … des tables temporaires

Les tables temporaires sont des objets créés par l’utilisateur dans tempDB et (généralement) destinés à fournir un espace de travail pour les résultats intermédiaires lors du traitement de données par une requête. Elles sont traitées comme des tables permanentes dans la mesure où il est possible de consulter leurs données via des instructions SELECT ou de les modifier via des INSERT, UPDATE et DELETE.
Il est également possible de créer des indexes et des statistiques au sein d’une table temporaire, tout comme il est possible de supprimer ou d’ajouter des colonnes.

Il existe 2 types de tables temporaires :

  • Les tables temporaires locales, créées avec le préfixe #. Leur portée se limite à la session de l’utilisateur qui les a créées. Cela signifie qu’elles sont seulement visibles de leur créateur. Pour être exact : quand plusieurs utilisateurs créent une table temporaire d’un même nom et exécutent simultanément des requêtes qui l’utilisent, il n’y aura pas de conflits d’utilisation de la table temporaire, chaque table temporaire locale étant un objet autonome (grâce à un numéro d’identifiant qui garantit son unicité) seulement référencé par son créateur.

    A noter qu’une table temporaire locale n’est détruite qu’à la déconnection de la session de l’utilisateur qui l’a créée. Voici un exemple simple de création d’une table temporaire locale :

CREATE TABLE #Tab (Tab_ID int NOT NULL,Tab_Name varchar(20))
GO
  • Les tables temporaires globales, créées avec le préfixe ##. Leur portée se limite à toutes les sessions de l’instance au sein de laquelle elles ont été créées. Cela signifie que la table temporaire globale est visible de toutes les sessions. Donc, si un utilisateur A crée une table temporaire globale ##Tab et qu’un autre utilisateur B tente de faire de même, un message d’erreur sera affiché pour ce dernier lui indiquant l’existence d’un objet nommé ##Tab.

    A noter qu’une table temporaire globale ne sera détruite que lorsque toutes les sessions qui la référencent seront déconnectées. Voici un exemple simple de création d’une table temporaire globale :

CREATE TABLE ##Tab (Tab_ID int NOT NULL,Tab_Name varchar(20))
GO

… des tables variables

Les tables variables sont des objets temporaires créés par l’utilisateur dans tempDB, et également souvent destiné à fournir un espace de travail temporaire pour des résultats intermédiaires générés par des requêtes. La portée d’une table variable se limite à la durée de la requête au sein de laquelle elle a été créée.

La création d’une table variable se fait en faisant appel à la clause DECLARE, et en préfixant le nom de ladite table par @. Voici un exemple de création d’une table variable :

DECLARE @Tab TABLE (Tab_ID int NOT NULL,Tab_Name varchar(20))
GO

Comparaisons

Le tableau ci-dessous donne quelques éléments de comparaison des tables temporaires et des tables variables :

Elément de comparaison Table temporaire Table variable Commentaires
Ecrit dans le journal des transactions de tempDB. Oui. Oui.
Autorise la troncature (TRUNCATE). Oui. Non.
Autorise la modification (ALTER TABLE). Oui. Non.
Peut être défini globalement. Oui. Non.
Autorise la création d’un index en cluster. Oui. Oui et non (voir commentaires). Pour les tables variables, les indexes ne peuvent être créés qu’implicitement (via l’ajout d’une contrainte d’unicité ou d’une clé primaire).
Autorise la création d’indexes non-cluster. Oui. Oui et non (voir commentaires). La création d’une contrainte d’unicité peut faire office d’index non-cluster pour la table variable.
Autorise la création de statistiques. Oui. Non.
Peut induire des recompilations. Oui. Non. Les tables temporaires peuvent être associées à des recompilations dans la mesure où elles autorisent des opérations de modification qui sont interdites pour les tables variables (i.e., ALTER TABLE, CREATE INDEX,…).
N’est seulement utilisée qu’en mémoire. Non. Non. Voir ici.
Peut être utilisée au sein d’une user-defined function. Non. Oui.
Peut accepter les SELECT INTO. Oui. Non.
Peut être concernée par le parallélisme. Oui. Non.
Peut provoquer des locks. Oui. Oui, mais légèrement.
Peut être affectée par une transaction utilisateur. Oui. Non (voir commentaires). Pour la table variable, les opérations sont traitées comme des transactions systèmes. Ainsi, un ROLLBACK orchestré par un utilisateur n’aura pas d’effet sur la table variable, contrairement à la table temporaire.

Conseils d’utilisation

En règle générale, l’utilisation de tables temporaires et de tables variables est à éviter dans la mesure du possible, pour des raisons de performances : augmentation des activités I/O durant leur utilisation, parasitage potentiel des performances de tempDB, double-RAM-caching (ce qui peut impacter la taille du buffer pool),…

Si d’aventure, l’utilisation d’une table temporaire ou d’une table variable est envisagée pour raisons diverses (i.e., pour réduire le nombre de lignes à joindre, pour maintenir un ensemble de résultats au sein d’une procédure stockée, pour remplacer l’utilisation de curseurs, pour agréger des données issues de différentes sources,…), voici quelques conseils essentiels :

  • Utilisez une table temporaire si :
    • Vous souhaitez traiter un gros volume de données. C’est le cas des sélections ou suppressions de masse, par exemple, surtout que dans le cas des suppressions de masse, l’utilisation de TRUNCATE (non supportée par les tables variables) est plus efficace que DELETE.
    • Vous souhaitez utiliser des indexes ne pouvant pas être implicitement créés au sein d’une table variable. C’est le cas des indexes de type INCLUDE, des filtered indexes,…).
    • Le plan d’exécution optimal d’une requête utilisant un objet temporaire varie beaucoup en fonction des données. En effet, les tables temporaires supportent la création de statistiques, ce qui permet de recompiler dynamiquement un plan d’exécution suivant les données à traiter. Pour de plus amples informations, vous pouvez jeter un coup d’œil ici.
  • Utilisez une table variable si :
    • Vous souhaitez traiter un petit nombre d’enregistrements.
    • Vous souhaitez que vos données temporaires ne soient pas affectées par un ROLLBACK transactionnel.
    • La source des données (une table permanente, par exemple) que l’on souhaite utiliser pour le remplissage d’un objet temporaire est sujette à de fortes requêtes de consultation (SELECT), l’utilisation d’une table variable peut servir à minimiser les blocages.
    • Les recompilations d’une requête utilisant une table temporaire impactent fréquemment les performances. Dans ce cas, testez l’utilisation d’une table variable à la place d’une table temporaire (à moins de réécrire votre requête en la scindant en batches plus petits, par exemple).

En conclusion…

Ce billet a permis de présenter les grandes différences entre les tables temporaires et les tables variables. Grosso-modo, les tables variables peuvent être envisagées dans de très nombreux cas de figure, sauf si vous êtes amenés à traiter un très grand nombre d’enregistrements qui amènent souvent l’optimiseur SQL à utiliser un plan d’exécution plus complexe. Dans tous les cas, réaliser une pile de tests afin d’arriver à un choix optimal est recommandé.

La partie Comparaisons pourra être mise à jour ultérieurement pour tenir en compte, au fur et à mesure,  de quelques éléments de démonstration qui seront publiés dans d’autres billets.

Publicités

2 commentaires sur “[SQL Server] TempDB : tables temporaires vs tables variables

  1. zouhaier dit :

    Bonjour,

    Pouvez-vous expliquer FileTables et External Tables ?

    Merci d’avance.

    Zouhaier

    • Bonsoir Zouhaier,

      Les FileTables sont une fonctionnalité (compatible SQL Server 2012 et au-delà) qui permet de stocker des fichiers ou répertoires au sein même d’une base de données SQL Server. Souvent utile pour les logiciels de gestion documentaire ou de contenus.
      L’idée est de permettre de ne pas avoir à stocker les fichiers au sein d’un système de gestion de fichiers externe. Et surtout d’offrir la possibilité de les requêter en T-SQL, par exemple, sans avoir à passer par une API.

      Concernant les External Tables, ce sont tout simplement de structures de données externes (généralemzent venant d’Hadoop ou Azure Blob Storage, dont les concepts ont été abordés dans ce blog) importées et stockées au sein de SQL Server (2016+), mais aussi SQL Azure, SQL DataWarehouse,… où elles peuvent être traitées avec des requêtes T-SQL.

      Bien à vous,
      M.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s