Add new comment

Table temporaire vs table variable

Submitted by st on

Dans cette article nous allons voir les différences entre les tables temporaires marquées par dièse (#Temp) et le variables de type table (@Temp).

Emplacement mémoire / disque

Les tables temporaires allouent l'espace dans la base de données (BDD) temporaire (tempdb) ainsi que variables peut utiliser la BDD temporaire ainsi que la mémoire vive.

Normalement, la variable commence allouer l’espace de disque depuis qu'elle dépasse la taille d'un extente qui égal à 8 pages (soit 64 Ko).

SET NOCOUNT ON
USE tempdb
DECLARE @V TABLE (Name nchar(4000)) -- une page environ pour une ligne
DECLARE @i int = 0
WHILE @i < 100 BEGIN
   IF @i % 10 = 0 OR @i BETWEEN 1 AND 9 OR @i = 99
      SELECT 
         @i AS lines_in_table_variable, 
         unallocated_extent_page_count, 
         user_object_reserved_page_count
      FROM sys.dm_db_file_space_usage
   INSERT INTO @V (Name)
      SELECT convert(nvarchar(4), @i) + replicate(N'V', 3996)
   SET @i = @i + 1
END

Résultats :

lines_in_table_variable unallocated_extent_page_count user_object_reserved_page_count
----------------------- ----------------------------- -------------------------------
0                       4144                          40
1                       4144                          40
2                       4144                          40
3                       4144                          40
4                       4144                          40
5                       4144                          40
6                       4144                          40
7                       4144                          40
8                       4144                          40
9                       4136                          48
^^^
10                      4136                          48
20                      4128                          56
30                      4120                          64
40                      4112                          72
50                      4096                          88
60                      4088                          96
70                      4080                          104
80                      4072                          112
90                      4056                          128
99                      4048                          136

Métadonnées

Lorsque vous déclarez une variable ou crée une tables temporaire, les informations sur les métadonnées sont bien placées dans les tables systèmes.

USE tempdb
DECLARE @V TABLE (Id int, Name nvarchar(100))
CREATE TABLE #T (Id int, Name nvarchar(100))
SELECT OBJECT_NAME(object_id) AS object_name, object_id, name 
FROM sys.columns WHERE object_id > 127 AND name IN ('Id', 'Name')
DROP TABLE #T

Résultats :

object_name              object_id   name
------------------------ ----------- -----
#0D7A0286                226099846   Id
#T__________00000000002D 242099903   Id
#0D7A0286                226099846   Name
#T__________00000000002D 242099903   Name

Transactions

Les tables temporaires sont inclus dans la transaction explicite ainsi que les variables sont dehors de celle-ci.

SET NOCOUNT ON
CREATE TABLE #T (Id int)
DECLARE @V TABLE (Id int)
 
INSERT INTO #T VALUES(1)
INSERT INTO @V VALUES(1)
 
BEGIN TRANSACTION
INSERT INTO #T VALUES(2)
INSERT INTO @V VALUES(2)
ROLLBACK
 
SELECT Id AS [Id #T] FROM #T
SELECT Id AS [Id @V] FROM @V

Résultats :

Id #T
-----------
1
 
Id @V
-----------
1
2

Indexation

Vous pouvez créer les index sur la table temporaires et non pas sur la variable; Par contre, vous pouvez déclarer une clé primaire ou une contrainte d'unicité lorsque vous déclarez la variable.

CREATE TABLE #T (Id int)
DECLARE @V TABLE (Id int)
 
CREATE INDEX IX1_TT ON #T(Id)
CREATE INDEX IX2_TT ON @V(Id)  -- !! Msg 102, Level 15, State 1, Line 5
                               -- !! Incorrect syntax near '@V'
GO
 
DECLARE @V TABLE (Id int PRIMARY KEY, Name nvarchar(100) UNIQUE)
GO

Visibilité

La variable n'est visible qu'en code d'une procédure stockée, mais elle peut être passée comme le paramètre READONLY (la déclaration d'un type utilisateurs est necessaire).

La table temporaire n’existe que pendent le temps d’exécution de la procédure stockée mais elle est visible pour tous les procédures appelées par celle qui crée la table. 

CREATE TYPE MyTableType AS TABLE ( Id int)
GO
 
CREATE PROCEDURE P1
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @V MyTableType
   CREATE TABLE #T (Id int)
 
   INSERT INTO @V (Id) VALUES (1)
   INSERT INTO #T (Id) VALUES (2)
 
   EXEC P2
   EXEC P3 @V
END
GO
 
CREATE PROCEDURE P2
AS
BEGIN
   SET NOCOUNT ON
   SELECT Id AS [Id #T] FROM #T
END
GO
 
CREATE PROCEDURE P3
   @V MyTableType READONLY
AS
BEGIN
   SET NOCOUNT ON
   SELECT Id AS [Id @V] FROM @V
END
GO
 
EXEC P1
GO
 
DROP PROCEDURE P1
DROP PROCEDURE P2
DROP PROCEDURE P3
GO
DROP TYPE MyTableType
GO

Résultats :

Id #T
-----------
2
 
Id @V
-----------
1

Egalement, la table temporaire peut être déclarée comme "globale" avec deux dièses (##Temp) et ensuit être visible pour toutes les connexions.

Questions de performance

Modifications de données

Comme les variable sont dehors de la transaction, les opérations d'insertion, de modifications ou de suppression des données seront un peu plus rapides.

CREATE PROCEDURE Test_TableTmp
   @InsertCount int
AS
BEGIN
   DECLARE @Start datetime = getdate();
   CREATE TABLE #T (Id int PRIMARY KEY, StrValue nvarchar(100))
   DECLARE @i int = 1
   WHILE @i <= @InsertCount BEGIN
      INSERT INTO #T (Id, StrValue)
         VALUES(@i, convert(nvarchar(100), rand() * @i))
      SET @i = @i + 1
   END
   SELECT @InsertCount AS LinesInserted, datediff(ms, @Start, getdate()) AS [ElapsedTime, msec]
END
GO
 
CREATE PROCEDURE Test_TableVar
   @InsertCount int
AS
BEGIN
   DECLARE @Start datetime = getdate();
   DECLARE @V TABLE (Id int PRIMARY KEY, StrValue nvarchar(100))
   DECLARE @i int = 1
   WHILE @i <= @InsertCount BEGIN
      INSERT INTO @V (Id, StrValue)
         VALUES(@i, convert(nvarchar(100), rand() * @i))
      SET @i = @i + 1
   END
   SELECT @InsertCount AS LinesInserted, datediff(ms, @Start, getdate()) AS [ElapsedTime, msec]
END
GO
 
DBCC DROPCLEANBUFFERS
GO
EXEC Test_TableTmp 100
EXEC Test_TableVar 100
EXEC Test_TableTmp 1000
EXEC Test_TableVar 1000
EXEC Test_TableTmp 10000
EXEC Test_TableVar 10000
EXEC Test_TableTmp 100000
EXEC Test_TableVar 100000
GO
DROP PROCEDURE Test_TableTmp
DROP PROCEDURE Test_TableVar
GO

Résultats :

Lignes insérées

Temps, msec
Table temporaire Variable
100 33 3
1 000 26 23
10 000 240 226
100 000 2426 2290

Sélection de données

Sachant que vous ne pouvez pas créer les index sur les variables, les opérations de sélection de données peuvent être beaucoup plus lentes de que le nombre de lignes s'augmente.

En plus, en cas d'une variable l’optimiseur des requêtes SQL Server n'est pas capable créer le plan pour le traitement parallèle ainsi que c'est possible pour les tables temporaires.

Ré-compilation

Les versions anciennes de SQL Server ont pu ré-compiler les procédures stockées qui utilisent les tables temporaires chaque fois on l'appelle. Ce problème n'est plus important depuis 2005 dans la majorité de cas.

USE AdventureWorks
GO
SET NOCOUNT ON
GO
CREATE PROCEDURE dbo.TestP1
   @LastName nvarchar(100),
   @Divider int
AS
BEGIN
   SET NOCOUNT ON
   CREATE TABLE #T (Id int, Name nvarchar(100), NameLen int)
 
   INSERT INTO #T (Id, Name, NameLen)
   SELECT C.ContactID, C.LastName, 0
      FROM Person.Contact C
      WHERE C.LastName LIKE @LastName
   UPDATE #T
   SET NameLen = len(Name)
   WHERE Id % @Divider <> 0
 
   EXEC dbo.TestP2
END
GO
 
CREATE PROCEDURE dbo.TestP2
AS
BEGIN
   SELECT COUNT(*) AS NbDeLignes FROM #T WHERE NameLen > 0
END
GO
 
SELECT object_name(objid) AS procedure_name, usecounts 
FROM sys.syscacheobjects WHERE objid IN (object_id('dbo.TestP1'), object_id('dbo.TestP2'))
 
EXEC dbo.TestP1 'Ada%', 2
SELECT object_name(objid) AS procedure_name, usecounts 
FROM sys.syscacheobjects WHERE objid IN (object_id('dbo.TestP1'), object_id('dbo.TestP2'))
 
EXEC dbo.TestP1 'Smi%', 4
SELECT object_name(objid) AS procedure_name, usecounts 
FROM sys.syscacheobjects WHERE objid IN (object_id('dbo.TestP1'), object_id('dbo.TestP2'))
GO
 
DROP PROCEDURE dbo.TestP1
DROP PROCEDURE dbo.TestP2
GO

Résultats :

procedure_name  usecounts
--------------- -----------
 
NbDeLignes
-----------
43
 
procedure_name  usecounts
--------------- -----------
TestP1          1
TestP2          1
 
NbDeLignes
-----------
80
 
procedure_name  usecounts
--------------- -----------
TestP1          2
TestP2          2

Conclusions

La variable de type table est une bonne solution pour le traitement des petites volumes (< 1000 lignes environ ou < 1 extent) de données dont la sélection par les critères n'est pas nécessaire. Le passage de paramètre de type table par variable est plus contrôlé et lisible.

Dans les autres cas il vaut mieux utiliser les tables temporaires.