Utilisation des variables de type table ou des tables temporaires, et performances

Publié le par

Voilà deux types de tables souvent utilisées mais mal maîtrisées, surtout au regard des performances qu’elles offrent, qui ne sont pas très bonnes.
Voyons pourquoi …

SQL Server permet de stocker dans des variables table ou des tables temporaires (locales à la session ou globales à l’instance) des données.

Pour déclarer une variable table, il suffit d’écrire, par exemple :

1
2
3
4
5
6
7
---------------------------------
-- Nicolas SOUQUET - 13/06/2009 -
---------------------------------
DECLARE @Tb TABLE
(
  uneColonne INT
)

=> Avantages : comme toute variable, elle est détruite dès la fin de la session utilisateur
=> Inconvénients : elle n’est pas indexable comme une table, et aucune statistique n’est maintenue sur les colonnes.

Pour créer une table temporaire locale à la session, il suffit d’écrire, par exemple :

1
2
3
4
5
6
7
---------------------------------
-- Nicolas SOUQUET - 13/06/2009 -
---------------------------------
CREATE TABLE #Tb
(
  uneColonne VARCHAR(50)
)

et pour créer une une table temporaire globale à l’instance SQL Server, il suffit d’écrire, par exemple :

1
2
3
4
CREATE TABLE ##Tb
(
  uneColonne VARCHAR(50)
)

=> Avantages : des statistiques sont maintenues sur les colonnes, qui sont de plus indexables
=> Inconvénients : il est préférable de supprimer la table à la fin de la session, même si elle est automatiquement détruite lors de la fin de celle-ci.

Si vous pensiez que ces tables sont stockées uniquement dans la RAM du serveur, vous avez fait fausse route, car comme toute table, le moteur de base de données se doit de les persister.
Néanmoins les pages que contiennent ces tables demeurent dans le cache de données.

Alors, où sont persistées ces tables ?

Voici le code commenté :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
---------------------------------
-- Nicolas SOUQUET - 13/06/2009 -
---------------------------------
 
DECLARE @Tb  TABLE
(
  uneColonne INT
)
 
-- Récupération de la structure de la variable table
SELECT T.name AS nomTable,
    C.name AS nomColonne
FROM TempDB.sys.columns AS C
JOIN TempDB.sys.tables AS T ON C.object_id = T.object_id

Comme vous pouvez le voir, SQL Server n’attribue pas le nom de votre table, mais une suite d’hexadécimaux.
Cela permet de garantir que deux sessions qui ont des variables tables qui portent le même nom n’utilisent pas les mêmes données.

En effet si nous exécutons :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE @Tb1 TABLE  
(  
  uneColonne1 INT  
)  
 
DECLARE @Tb2 TABLE  
(  
  uneColonne2 INT  
)  
 
SELECT T.name AS nomTable,  
    C.name AS nomColonne  
FROM TempDB.sys.columns AS C  
JOIN TempDB.sys.tables AS T ON C.object_id = T.object_id  
 
WAITFOR DELAY '00:00:05'

Nous obtenons :

Et si nous tentons d’afficher la structure de ces tables, dans l’explorateur d’objets, on obtient le message d’erreur suivant :

Il en va quelque peut différement pour les tables temporaires locales à la session.
Si nous exécutons :

1
2
3
4
5
6
7
8
9
10
11
12
---------------------------------
-- Nicolas SOUQUET - 13/06/2009 -
---------------------------------
CREATE TABLE #TEST
(
  uneColonne INT
)
 
SELECT T.name AS nomTable,
    C.name AS nomColonne
FROM TempDB.sys.columns AS C
JOIN TempDB.sys.tables AS T ON C.object_id = T.object_id

Nous obtenons :

nomTable			nomColonne
------------------------------------------
#TEST___...___000000000003	uneColonne

où les points de suspension remplacent un grand nombre d’underscores.

Un « numéro » est ainsi attribué à chaque table temporaire locale créée, pour les mêmes raisons que vous la variable table :
cela permet de garantir que deux sessions qui ont des tables tmeporaires locales qui portent le même nom n’utilisent pas les mêmes données.

Enfin, voyons ce qui se passe pour une table temporaire globale :

1
2
3
4
5
6
7
8
9
10
11
12
---------------------------------
-- Nicolas SOUQUET - 13/06/2009 -
---------------------------------
CREATE TABLE ##TEST
(
  uneColonne INT
)
 
SELECT T.name AS nomTable,
    C.name AS nomColonne
FROM TempDB.sys.columns AS C
JOIN TempDB.sys.tables AS T ON C.object_id = T.object_id

Nous obtenons le résultat suivant :

Là, le nom de la table est celui que l’on a donné dans la requête. Il est dès lors impossible de créer une table temporaire globale portant le même nom : ouvrons une autre session et exécutons :

1
2
3
4
5
6
7
---------------------------------
-- Nicolas SOUQUET - 13/06/2009 -
---------------------------------
CREATE TABLE ##TEST
(
  uneColonne INT
)

La console de SSMS affiche alors :

Msg 2714, Niveau 16, État 6, Ligne 1
Il existe déjà un objet nommé ‘##TEST’ dans la base de données.

Passons maintenant aux performances : pourquoi le fait d’utiliser une variable table ou une table temporaire est-il contre-performant ?

=> Les variables table et les tables temporaires étant des tables, elles doivent être persistées pour respecter l’ACIDité de la transaction qui les requiert. Dès lors on duplique des données qui existent déjà …

=> Les variables table et les tables temporaires sont stockées dans TempDB, qui joue un rôle majeur dans le fonctionnement de SQL Server, notamment pour les travaux de jointure et d’aggrégation, les magasins de version (SQL Server 2005 et ultérieur), et de nombreuses tâches que le moteur de base de données effectue en arrière plan

=> En plus des opérations que le moteur de bases de données effectue en RAM, il doit effectuer des opérations sur disque pour persister les données (enregistrement des données dans les pages de TempDB et dans le fichier du journal des transactions). Un accès disque étant en moyenne 1000 fois plus lent qu’un accès en RAM, …

=> En peuplant une variable table ou une table temporaire, on oblige le moteur de base de données à stocker deux fois en RAM les données, ce qui rend donc le cache de données moins performant, surtout dans le cas où ce type de table a un cardinal élevé. Imaginez ce que cela peut faire si plusieurs utilisateurs exécutent très souvent une procédure stockée qui requiert une variable table …

=> Le moteur de base de données ne maintient pas de statistiques de colonne pour les variable table; en conséquence, un plan de requête efficace ne peut pas être trouvé.

ElSuket

Laisser un commentaire