[SQL Server] TempDB : comparaison des performances entre les tables temporaires et les tables variables

Ce billet offre une série de comparatifs entre les tables temporaires et les tables variables (ou variables tables) au niveau des performances de leurs traitements. Nous nous focaliserons surtout sur les temps de réponse des insertions, suppressions, modifications et sélections de lignes de données.


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. Vous pouvez également, en complément, jeter un coup d’œil ici et .

Contexte

Les tests seront réalisés sur SQL Server 2012 tournant sur une machine locale dotée d’une RAM de 8 Go, de processeurs Intel i7 quadri-cœur et cadencé à 2,3 Ghz. Et la base de données tempDB est placée sur un disque SSD qui favorise les performances en écritures.

Les tests seront effectués suivant 4 scénarii :

  • Insertion de données (avec INSERT) : ligne par ligne ; en bloc (en l’occurrence, à partir de tout le contenu de la table permanente TabTest ; voir plus loin).
  • Suppression de toutes les données (avec DELETE).
  • Modification de données (avec UPDATE)
    : ensemble de lignes spécifiques ; en bloc (en l’occurrence, tout le contenu de la table temporaire/variable).
  • Sélection de données de tous les enregistrements d’une table (avec SELECT).

Les tables qui seront testées seront :

  • Une table temporaire locale sans clé primaire #TabTemp.
  • Une table variable sans clé primaire #TabVar.
  • Une table temporaire locale avec clé primaire (index en cluster) #TabTemp_PK.
  • Une table variable avec clé primaire (et index en cluster créé implicitement) #TabVar_PK.

Dans certains de nos tests, une table permanente TabTest de 500 000 enregistrements (par défaut, et à créer au sein d’une base de test) sera utilisée pour alimenter nos tables temporaires et variables. Voici son script de création et de remplissage de données :

 /** Création d'une table permanente avec des données **/
CREATE TABLE TestTab (TestTab_ID INT PRIMARY KEY CLUSTERED IDENTITY (1,1),TestTab_Name VARCHAR(128))
GO
SET NOCOUNT ON
DECLARE @i INT
SET @i=500000
WHILE @i>0
 BEGIN
    INSERT INTO TestTab
      VALUES ('Test')
        SET @i=@i-1
 END
GO
--SELECT COUNT(*) FROM TestTab
--GO

Entrée dans le vif du sujet…

Tests d’insertion…

… ligne par ligne

Après avoir créé nos objets temporaires (tables temporaires et tables variables), testons l’insertion de données sur…

  • Une table temporaire locale en heap (i.e., sans index en cluster) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
CREATE TABLE #TabTemp (TabTemp_ID INT IDENTITY (1,1), TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0,@time DATETIME2=GETDATE()
WHILE (@rows<500)
 BEGIN
    INSERT INTO #TabTemp (TabTemp_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
DECLARE @TabVar TABLE (TabVar_ID INT IDENTITY (1,1), TabVar_Name VARCHAR(128))
DECLARE @rows INT=0,@time DATETIME2=GETDATE()
WHILE (@rows<500)
 BEGIN
    INSERT INTO @TabVar (TabVar_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table temporaire locale avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
CREATE TABLE #TabTemp_PK (TabTemp_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0,@time DATETIME2=GETDATE()
WHILE (@rows<500)
 BEGIN
    INSERT INTO #TabTemp_PK (TabTemp_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
DECLARE @TabVar_PK TABLE (TabVar_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabVar_Name VARCHAR(128))
DECLARE @rows INT=0,@time DATETIME2=GETDATE()
WHILE (@rows<500)
 BEGIN
    INSERT INTO @TabVar_PK (TabVar_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO

Les différents scripts permettent l’insertion de 500 enregistrements, mais peuvent être modifiés pour en insérer plus ou moins. Il est recommandé de ne pas faire de tests sur un serveur de production.

… en bloc

Après avoir créé nos objets temporaires (tables temporaires et tables variables), testons l’insertion de données sur…

  • Une table temporaire locale en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Insertion en bloc sur une table temporaire locale en heap **/
CREATE TABLE #TabTemp (TabTemp_ID INT IDENTITY (1,1), TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0,@time DATETIME2=GETDATE()
INSERT INTO #TabTemp
  SELECT TestTab_Name FROM TestTab
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Insertion en bloc sur une table variable en heap **/
DECLARE @TabVar TABLE (TabVar_ID INT IDENTITY (1,1), TabVar_Name VARCHAR(128))
DECLARE @rows INT=0,@time DATETIME2=GETDATE()
INSERT INTO @TabVar (TabTemp_Name)
  SELECT TabVar_Name FROM TestTab
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table temporaire locale avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Insertion en bloc sur une table temporaire locale avec PK **/
CREATE TABLE #TabTemp_PK (TabTemp_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0,@time DATETIME2=GETDATE()
INSERT INTO #TabTemp_PK
  SELECT TestTab_Name FROM TestTab
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Insertion en bloc sur une table variable avec PK **/
DECLARE @TabVar_PK TABLE (TabVar_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabVar_Name VARCHAR(128))
DECLARE @rows INT=0,@time DATETIME2=GETDATE()
INSERT INTO @TabVar_PK (TabVar_Name)
  SELECT TestTab_Name FROM TestTab
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO

Les différents scripts permettent l’insertion de 500 enregistrements, mais peuvent être modifiés pour en insérer plus ou moins. Il est recommandé de ne pas faire de tests sur un serveur de production.

Tests de suppression de toutes les lignes d’une table

Testons la suppression de toutes les lignes de données sur…

  • Une table temporaire locale en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Suppression en bloc de la totalité d’une table temporaire en heap **/
CREATE TABLE #TabTemp (TabTemp_ID INT IDENTITY (1,1), TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0
WHILE (@rows<500)
 BEGIN
    INSERT INTO #TabTemp (TabTemp_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
DECLARE @time DATETIME2=GETDATE()
DELETE FROM #TabTemp
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Suppression en bloc de la totalité d’une table variable en heap **/
DECLARE @TabVar TABLE (TabVar_ID INT IDENTITY (1,1), TabVar_Name VARCHAR(128))
DECLARE @rows INT=0,@time2 DATETIME2=GETDATE()
WHILE (@rows<500)
 BEGIN
    INSERT INTO @TabVar (TabVar_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
DECLARE @time DATETIME2=GETDATE()
DELETE FROM @TabVar
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table temporaire locale avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Suppression en bloc de la totalité d’une table temporaire avec PK **/
CREATE TABLE #TabTemp_PK (TabTemp_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0
WHILE (@rows<500)
 BEGIN
    INSERT INTO #TabTemp_PK (TabTemp_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
DECLARE @time DATETIME2=GETDATE()
DELETE FROM #TabTemp_PK
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Suppression en bloc de la totalité d’une table variable avec PK **/
DECLARE @TabVar_PK TABLE (TabVar_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabVar_Name VARCHAR(128))
DECLARE @rows INT=0
WHILE (@rows<500)
 BEGIN
    INSERT INTO @TabVar_PK (TabVar_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
DECLARE @time DATETIME2=GETDATE()
DELETE FROM @TabVar_PK
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO

Les différents scripts permettent la suppression d’ « un coup » de tous les enregistrements de chaque table. Par défaut, 500 enregistrements sont traités. Il est recommandé de ne pas faire ce type de test sur un serveur de production.

Tests de mises-à-jour

… d’un ensemble de lignes spécifiques (filtre sur ID)

Reprenons notre table permanente TestTab contenant 500 000 enregistrements, puis testons la modification d’un ensemble de données sur…

  • Une table temporaire locale en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Mise-à-jour d'un ensemble de lignes d'une table temporaire locale en heap **/
CREATE TABLE #TabTemp (TabTemp_ID INT IDENTITY (1,1), TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0
INSERT INTO #TabTemp
  SELECT TestTab_Name FROM TestTab
DECLARE @time DATETIME2=GETDATE()
UPDATE #TabTemp SET TabTemp_Name='Updated_Test' WHERE TabTemp_ID BETWEEN 1 AND 500
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Mise-à-jour d’un ensemble de lignes d’une table variable en heap **/
DECLARE @TabVar TABLE (TabVar_ID INT IDENTITY (1,1), TabVar_Name VARCHAR(128))
DECLARE @rows INT=0
INSERT INTO @TabVar (TabVar_Name)
  SELECT TestTab_Name FROM TestTab
DECLARE @time DATETIME2=GETDATE()
UPDATE @TabVar SET TabVar_Name='Updated_Test' WHERE TabVar_ID BETWEEN 1 AND 500
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table temporaire locale avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Mise-à-jour d'un ensemble de lignes d'une table temporaire locale en heap **/
CREATE TABLE #TabTemp_PK (TabTemp_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0
INSERT INTO #TabTemp_PK
  SELECT TestTab_Name FROM TestTab
DECLARE @time DATETIME2=GETDATE()
UPDATE #TabTemp SET TabTemp_Name='Updated_Test' WHERE TabTemp_ID BETWEEN 1 AND 500
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Mise-à-jour d’un ensemble de lignes d’une table variable en heap **/
DECLARE @TabVar_PK TABLE (TabVar_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabVar_Name VARCHAR(128))
DECLARE @rows INT=0
INSERT INTO @TabVar_PK (TabVar_Name)
  SELECT TestTab_Name FROM TestTab
DECLARE @time DATETIME2=GETDATE()
UPDATE @TabVar_PK SET TabVar_Name='Updated_Test' WHERE TabVar_ID BETWEEN 1 AND 500
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO

Les différents scripts permettent, par défaut, la modification d’un ensemble de 500 enregistrements, mais peuvent être édités pour en modifier plus ou moins. Il est recommandé de ne pas faire de tests sur un serveur de production.

… en bloc de la totalité d’une table

Reprenons notre table permanente TestTab contenant 500 000 enregistrements, et créée (dans une base de test) dans la section Tests d’insertion… d’un bloc de lignes, puis testons la modification d’une ligne d’enregistrement sur…

  • Une table temporaire locale en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Mise-à-jour de la totalité des lignes d'une table temporaire locale en heap **/
CREATE TABLE #TabTemp (TabTemp_ID INT IDENTITY (1,1), TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0
INSERT INTO #TabTemp
  SELECT TestTab_Name FROM TestTab
DECLARE @time DATETIME2=GETDATE()
UPDATE #TabTemp SET TabTemp_Name='Updated_Test' WHERE TabTemp_ID IS NOT NULL
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Mise-à-jour de la totalité des lignes d’une table variable en heap **/
DECLARE @TabVar TABLE (TabVar_ID INT IDENTITY (1,1), TabVar_Name VARCHAR(128))
DECLARE @rows INT=0
INSERT INTO @TabVar (TabVar_Name)
  SELECT TestTab_Name FROM TestTab
DECLARE @time DATETIME2=GETDATE()
UPDATE @TabVar SET TabVar_Name='Updated_Test' WHERE TabVar_ID IS NOT NULL
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table temporaire locale avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Mise-à-jour de la totalité des lignes d'une table temporaire locale en heap **/
CREATE TABLE #TabTemp_PK (TabTemp_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0
INSERT INTO #TabTemp_PK
  SELECT TestTab_Name FROM TestTab
DECLARE @time DATETIME2=GETDATE()
UPDATE #TabTemp_PK SET TabTemp_Name='Updated_Test' WHERE TabTemp_ID IS NOT NULL
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
/** Suppression de la totalité des lignes d’une table variable en heap **/
DECLARE @TabVar_PK TABLE (TabVar_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabVar_Name VARCHAR(128))
DECLARE @rows INT=0
INSERT INTO @TabVar_PK (TabVar_Name)
  SELECT TestTab_Name FROM TestTab
DECLARE @time DATETIME2=GETDATE()
UPDATE @TabVar_PK SET TabVar_Name='Updated_Test' WHERE TabVar_ID IS NOT NULL
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO

Les différents scripts permettent la modification d’un seul enregistrement de chaque table. Les enregistrements sont insérés à partir de la table permanente de test TestTab qui contient 500 000 lignes. Il est recommandé de ne pas faire de tests sur un serveur de production.

Tests de sélection de la totalité des lignes d’une table

Après avoir créé nos objets temporaires (table temporaire et table variable), testons la sélection de données sur…

  • Une table temporaire locale en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
CREATE TABLE #TabTemp (TabTemp_ID INT IDENTITY (1,1), TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0
WHILE (@rows<500)
 BEGIN
    INSERT INTO #TabTemp (TabTemp_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
DECLARE @time DATETIME2=GETDATE()
SELECT * FROM #TabTemp
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable en heap :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
DECLARE @TabVar TABLE (TabTemp_ID INT IDENTITY (1,1), TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0,@time2 DATETIME2=GETDATE()
WHILE (@rows<500)
 BEGIN
    INSERT INTO @TabVar (TabTemp_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
DECLARE @time DATETIME2=GETDATE()
SELECT * FROM @TabVar
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table temporaire locale avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
CREATE TABLE #TabTemp_PK(TabTemp_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0
WHILE (@rows<500)
 BEGIN
    INSERT INTO #TabTemp_PK (TabTemp_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
DECLARE @time DATETIME2=GETDATE()
SELECT * FROM #TabTemp_PK
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO
  • Une table variable avec clé primaire (index en cluster créé implicitement) :
/* DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE; */
DECLARE @TabVar_PK TABLE(TabTemp_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED, TabTemp_Name VARCHAR(128))
DECLARE @rows INT=0
WHILE (@rows<500)
 BEGIN
    INSERT INTO @TabVar_PK (TabTemp_Name)
      VALUES ('Test')
        SET @rows = @rows + 1
 END
DECLARE @time DATETIME2=GETDATE()
SELECT * FROM @TabVar_PK
SELECT RTRIM(CAST(DATEDIFF(MS, @time, GETDATE()) AS CHAR(10))) AS 'Duration (ms)'
GO

Par défaut, les différents scripts ci-dessus affichent 500 enregistrements (préalablement insérés). Seule l’opération de sélection de chaque script est mesurée. Il est recommandé de ne pas faire de tests sur un serveur de production.

Résultats des tests

  • Tests d’insertion :
    • Ligne par ligne :

      Le tableau ci-dessous présente, en fonction du nombre de lignes, les temps de réponse moyens (en millisecondes, et après une dizaine d’exécutions) de l’insertion de données au sein d’une table temporaire (avec ou sans clé primaire) et d’une table variable (avec ou sans clé primaire) :


    Les résultats indiquent que :

    • Les tables variables sont généralement plus rapides que les tables temporaires pour ce qui est des opérations d’insertion de données ligne par ligne. Les tables variables sont, en effet, peu gourmandes en logs et n’affectent pas le comportement de transactions utilisateurs.
    • Les tables temporaires/variables en heap sont plus rapides que celles avec une clé primaire (ou index en cluster créé implicitement) dans le cas de l’insertion de données ligne par ligne. En effet, dans le cas d’une table temporaire/variable avec un index en cluster, SQL Server est obligé de réaliser une opération supplémentaire de maintenance qui impactera les temps de réponse : la mise-à-jour dudit index.
    • En bloc :

      Le tableau ci-dessous présente les temps de réponse moyens (en millisecondes, et après une dizaine d’exécutions) de l’insertion de données en bloc au sein d’une table temporaire (avec ou sans clé primaire) et d’une table variable (avec ou sans clé primaire) pour 500 000 lignes :


    Les résultats indiquent que :

    • L’insertion de données en bloc est plus rapide que dans le cas de l’insertion d’un certain nombre d’enregistrements ligne par ligne. Si l’on compare nos résultats au tableau relatif à l’insertion ligne par ligne, on peut noter que les temps de réponse de l’insertion en bloc de 500 000 lignes est comparable aux temps de réponse de l’insertion ligne par ligne d’une dizaine de milliers d’enregistrements.
    • Les tables variables restent plus rapides que les tables temporaires lorsqu’il s’agit d’insertions en bloc.
    • Les tables temporaires/variables en heap sont plus rapides que celles avec une clé primaire (ou index en cluster créé implicitement) dans le cas de l’insertion en bloc, pour les mêmes raisons que pour les insertions ligne par ligne.
  • Tests de suppression de la totalité des lignes d’une table :

    Le tableau ci-dessous présente, en fonction du nombre de lignes, les temps de réponse moyens (en millisecondes, et après une dizaine d’exécutions) de la suppression de toutes les données d’une table temporaire (avec ou sans clé primaire) et d’une table variable (avec ou sans clé primaire) :


    On peut noter que les tables temporaires sont plus rapides que les tables variables lorsqu’il s’agit de traiter la suppression en bloc de toutes les lignes. Par ailleurs, si vous utilisez une table temporaire et que vous souhaitez la « vider », vous pouvez utiliser TRUNCATE qui opère plus vite que DELETE vu qu’elle rend les pages de données allouées directement réutilisables, et nécessite moins de verrous.

  • Tests de modification…
    • … de l’ensemble de lignes spécifiques (filtre sur ID) :

    Le tableau ci-dessous présente, en fonction du nombre de lignes, les temps de réponse moyens (en millisecondes, et après une dizaine d’exécutions) de la modification d’un ensemble de données spécifiques (filtrées sur ID) au sein d’une table temporaire (avec ou sans clé primaire) et d’une table variable (avec ou sans clé primaire) :

    <

    Les résultats indiquent, grosso-modo, que plus le volume de données à modifier est élevé, plus les tables temporaires sont mieux adaptées que les tables variables.

    • … en bloc de la totalité d’une table :

    Le tableau ci-dessous présente, en fonction du nombre de lignes, les temps de réponse moyens (en millisecondes, et après une dizaine d’exécutions) de la modification de toutes les données d’une table temporaire (avec ou sans clé primaire) et d’une table variable (avec ou sans clé primaire) :

    Les résultats indiquent, là aussi, que plus le volume de données à modifier est élevé, plus les tables temporaires sont mieux adaptées que les tables variables.

  • Tests de sélection de la totalité des enregistrements d’une table :

    Le tableau ci-dessous présente, en fonction du nombre de lignes, les temps de réponse moyens (en millisecondes, et après une dizaine d’exécutions) de la sélection de toutes les lignes d’une table temporaire (avec ou sans clé primaire) et d’une table variable (avec ou sans clé primaire) :

    Voici ce que l’on peut retenir des résultats ci-dessus :

    • Plus il y a d’enregistrements à afficher, plus les opérations de sélection sur les tables temporaires sont rapides comparées aux tables variables.
    • Les indexes de clé primaire jouent un rôle non-négligeable dans les performances des requêtes de sélection. Et cela devient plus significatif dès qu’il y a un très grand nombre de lignes à traiter.

En conclusion…

Au vu de nos résultats de tests, nous pouvons déduire que le traitement des tables temporaires est généralement plus rapide que pour les tables variables, dès lors qu’on a affaire à un gros volume de données à traiter. Toutefois, les opérations d’insertion sont particulièrement plus rapides pour les tables variables.

Il est important de tout de même noter que plusieurs facteurs peuvent influer sur les performances des requêtes utilisant des tables temporaires ou variables (et donc sur les tests), à savoir :

  • La qualité du matériel (RAM, stockage, CPU,…).
  • La configuration de l’instance SQL Server (MAXDOP, etc…) et de la base tempDB (voir ici).
  • Le type de requêtes utilisées (i.e., procédures stockées,…), ce qui peut influencer la réutilisation des objets temporaires en cache (voir ici). Les tests réalisés sont basés sur des requêtes ad hoc (batches), or les procédures stockées sont généralement plus performantes car contribuent à la réutilisation des objets temporaires en cache.
  • Les conditions de traitement des requêtes (i.e., clause WHERE).
  • La complexité des requêtes (jointures, agrégats, etc…).
  • L’indexation. Dans nos tests, par exemple, aucun index non-cluster n’a été utilisé, sachant qu’ils peuvent doper les performances de requêtes renvoyant des résultats filtrés (i.e., avec condition WHERE).
  • L’état du cache des données et des requêtes (dans notre cas précis, il n’y a pas eu de nettoyage de cache(s) à chaque réexécution de requêtes).
  • La charge d’activité sur la machine.
  • La volumétrie des tables traitées.
  • Etc…
Même si, pour paraphraser le billet [SQL Server] TempDB : tables temporaires vs tables variables
(section Conseils d’utilisation et En conclusion…)
ou les recommandations officielles de Microsoft, l’idée générale est de privilégier les tables temporaires quand le niveau de complexité d’une requête est élevé et/ou la quantité de données à traiter conséquente, la meilleure façon de choisir le type de table temporaire ou variable à utiliser est de multiplier une série de tests techniques.

Pour aller plus loin…

Si vous êtes curieux, vous pouvez approfondir les tests avec des indexes non-clusters (sur la colonne TabTemp_Name, par exemple), des requêtes plus complexes impliquant des jointures de tables temporaires/variables larges ou non,… Voire comparer les statistiques d’exécution (nombre de lectures logiques/physiques, nombre d’écritures logiques/physiques,…).

Publicités

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