Table temporaire ou CTE : Quelle est la différence ?

SQL vous offre deux options pour traiter efficacement les requêtes complexes : les CTE et les tables temporaires. Lisez la suite pour peser le pour et le contre des CTE et des tables temporaires.

En SQL (Structured Query Language), une expression de table commune (CTE) vous permet de définir une sous-requête et de lui attribuer un nom afin qu'elle puisse être utilisée dans une requête principale. Une table temporaire est une table de base de données que vous créez explicitement, comme n'importe quelle autre table, mais qui a la particularité de disparaître lorsque vous fermez la connexion à la base de données dans laquelle elle a été créée. Oui, les CTE et les tables temporaires ont des points communs. Mais le débat entre les ETC et les tables temporaires ne s'arrête pas là.

Pour bien maîtriser ce sujet, vous pouvez suivre notre coursRequêtes récursives . Vous apprendrez à utiliser les CTE pour traiter des structures de données complexes telles que les arbres et les graphes. Avec ses 114 exercices, ce cours vous guidera vers la maîtrise des requêtes SQL les plus complexes. Il propose également un quiz final pour tester les connaissances acquises.

Les CTE et les tables temporaires ont des objectifs communs. Toutes deux génèrent des résultats intermédiaires pour une requête sans laisser d'objets permanents dans la base de données, ce qui permet d'économiser de l'espace de stockage. Mais il existe des différences importantes entre ces deux types de tables, et il est donc utile de savoir quand utiliser l'une ou l'autre. Commençons par examiner la syntaxe de l'ETC.

Syntaxe de l'ETC

Nous verrons ci-dessous un cas d'utilisation où un ETC SQL est préférable à une requête récursive.

Un CTE SQL commence par une clause WITH, suivie d'un nom et de la définition de sa sous-requête. Ensuite, une instruction SQL ordinaire (généralement SELECT) interroge les résultats de l'ETC comme s'il s'agissait d'une table ordinaire. Dans sa forme la plus simple, un CTE ressemble à ceci :

WITH cte_name AS (
  cte_query_definition
)
SELECT * 
FROM cte_name;

Pour plus de détails sur la syntaxe des CTE, lisez Qu'est-ce qu'une expression de table commune en SQL.

Syntaxe des tables temporaires

Pour les tables temporaires, la syntaxe varie légèrement en fonction de votre système de gestion de base de données relationnelle (SGBDR). Le tableau suivant montre comment créer des tables temporaires dans les SGBDR les plus courants :

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

-- Local temp table:
CREATE TABLE #MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

-- Global temp table:
CREATE TABLE ##MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50)
)
-- data is kept until the end 
-- of the current transaction
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50))
-- data is kept until the end 
-- of the current session
ON COMMIT PRESERVE ROWS;

Pour en savoir plus sur la création de toutes sortes de tables en SQL, suivez notre cours Les bases de la création de tables en SQL. Ses 80 exercices vous aideront à maîtriser la commande CREATE TABLE et toutes ses options. Vous commencerez par créer une table dans sa forme la plus basique, puis vous passerez à la définition des clés primaires et étrangères, des colonnes NULL/NOT NULL et à la modification de la structure des tables existantes.

Maintenant que nous avons vu la syntaxe des CTE et des tables temporaires, examinons le fonctionnement de chacune d'entre elles

Résoudre le même problème avec des CTE et des tables temporaires

Prenons un exemple de problème qui peut être résolu à l'aide d'un ETC et d'une table temporaire.

Vous disposez de deux tables : une table détaillée Sales et une table Countries table contenant des informations spécifiques à un pays. Vous souhaitez répertorier les totaux des ventes par pays ainsi que des informations détaillées sur les pays. Pour ce faire, vous pouvez utiliser une expression de table commune ou une table temporaire. Nous allons explorer ces deux méthodes pour voir comment elles peuvent être utilisées efficacement pour cette tâche.

La solution basée sur une expression de table commune utilise une expression de table commune appelée CountryTotals avec une sous-requête qui calcule les totaux groupés par CountryCode. Elle comporte également une requête principale qui combine ces totaux avec les données de chaque pays :

WITH CountryTotals (CountryCode, TotalSales) AS (
  SELECT 
    CountryCode, 
    SUM(Sales) AS TotalSales
  FROM Sales
  GROUP BY CountryCode
)
SELECT 
  c.*, 
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
  ON ct.CountryCode = C.CountryCode;

Vous trouverez de nombreux autres exemples d'expressions de table courantes dans ces CTE expliqués avec des exemples.

Pour obtenir le même résultat en utilisant une table temporaire, vous devez d'abord créer la table temporaire avec les colonnes CountryCode et TotalSales. L'exemple de code suivant utilise la syntaxe MySQL pour ce faire :

CREATE TEMPORARY TABLE CountryTotals (
  CountryCode char(3), 
  TotalSales decimal(15, 2)
);

Les colonnes qui définissent la structure de la table temporaire sont les mêmes que celles incluses dans la définition de l'ETC ci-dessus. Pour créer la table temporaire, le type de données de chaque colonne doit correspondre à celui renvoyé par la colonne correspondante de la requête que vous utiliserez pour la remplir de données. Cette requête est présentée ci-dessous :

Notez que le SELECT placé après le INSERT est similaire à celui qui définit la sous-requête dans l'ETC d'origine.

Maintenant que vous avez les totaux des ventes par pays dans la table temporaire, vous pouvez la joindre à la table Countries pour obtenir le résultat final :

SELECT
  c.*,
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
  ON ct.CountryCode = C.CountryCode;

Cette requête est également identique à la requête principale de l'ETC (celle qui se trouve en dehors de la clause WITH ), car la table temporaire porte le même nom que l'ETC.

Il est important de noter qu'il n'est pas toujours possible de remplacer les tables temporaires par des CTE. C'est possible lorsque les lignes de la table temporaire proviennent d'une seule SELECT. Mais si les données sont modifiées par UPDATE, DELETE ou d'autres commandes INSERT après avoir été chargées dans la table temporaire, vous ne pourrez pas remplacer la table temporaire par un CTE.

Quand utiliser un CTE plutôt qu'une table temporaire ?

Même dans les situations où un CTE peut être remplacé par une table temporaire (ou vice versa), ce n'est pas toujours une bonne pratique. Les cas d'utilisation des ETC et ceux des tables temporaires sont différents ; il existe des situations dans lesquelles il est préférable d'utiliser un ETC et d'autres dans lesquelles il est préférable d'utiliser une table temporaire.

Examinons les cas d'utilisation des CTE et des tables temporaires, en commençant par les CTE.

Cas d'utilisation des CTE

Les CTE ne génèrent pas d'objets persistants dans la base de données. En d'autres termes, l'ETC ne vit que le temps de l'exécution de la requête qui le contient. Une fois l'exécution de la requête terminée, le CTE disparaît sans laisser de trace. En outre, le CTE et la requête qui le consomme sont contenus dans la même instruction SQL. Ces qualités rendent le CTE préférable pour améliorer la lisibilité et l'organisation des scripts SQL, c'est-à-dire pour rendre les requêtes plus faciles à comprendre pour les humains.

Petits ensembles de données

Lorsque le résultat d'une sous-requête ne comporte qu'un nombre relativement faible de lignes (c'est-à-dire 10 ou 20 000 lignes), l'ETC est le moyen idéal de l'implémenter. Comme il n'utilise pas de mémoire persistante, il ne génère pas d'opérations d'E/S sur disque. Les requêtes sont donc résolues beaucoup plus rapidement.

Vues internes

Vous ne pouvez pas créer, remplir de données ou interroger une table temporaire à l'intérieur d'une vue. En revanche, vous pouvez inclure un CTE dans une vue et l'utiliser partout comme s'il s'agissait d'une table en lecture seule.

Refonte

Lors de la refonte des requêtes de base de données, les CTE sont de précieux alliés. Dans les requêtes longues et complexes, les CTE permettent de définir des résultats intermédiaires (c'est-à-dire des sous-requêtes nommées) qui peuvent être réutilisés à plusieurs reprises dans la même requête.

En outre, la complexité d'une longue requête peut être progressivement réduite à l'aide d'ETC imbriqués, jusqu'à ce qu'elle soit simplifiée à l'aide d'une adresse SELECT de quelques lignes seulement.

Amélioration des performances des bases de données

Dans certains SGBDR (par exemple MS SQL Server), les ETC permettent de créer des plans d'exécution plus efficaces que les tables temporaires. Cela peut améliorer considérablement les performances de la base de données en réduisant les temps d'exécution des requêtes.

L'avantage obtenu en termes de performances dépend de l'optimiseur du SGBDR, ce qui signifie que l'utilisation des ETC ne se traduit pas toujours par de meilleures performances. Il s'agit toutefois d'un facteur à prendre en compte lors du choix entre un ETC et une table temporaire pour la gestion des sous-requêtes.

Récurrence

Les requêtes récursives constituent un domaine où l'utilisation des ETC est obligatoire. En SQL, un ETC récursif est capable de s'invoquer lui-même pour mettre en œuvre des processus récursifs, tels que la traversée de structures de données hiérarchiques. En d'autres termes, la récursivité nécessite des ETC.

L'autorisation de la récursivité en SQL évite de devoir utiliser d'autres langages de programmation, ce qui favorise une plus grande efficacité des requêtes qui doivent s'invoquer elles-mêmes. Pour en savoir plus, consultez notre article Qu'est-ce qu'un ETC récursif en SQL?

Maintenant que vous savez dans quels cas il est pratique d'utiliser un CTE, vous pouvez renforcer vos connaissances en révisant les questions d'entretien les plus courantes sur les CTE SQL et en vous entraînant avec ces exercices sur les CTE.

Cas d'utilisation des tables temporaires

Le principal avantage de l'ETC - ne pas stocker les données de manière persistante - peut être un inconvénient. Dans certains cas d'utilisation, il est préférable de stocker les données dans des tables (même temporaires). Examinons les situations les plus typiques.

Réutilisation d'ensembles de données

La réutilisation du code CTE est limitée à une seule requête. Les données stockées dans une table temporaire, en revanche, peuvent être utilisées de manière répétée dans différentes requêtes. La condition essentielle est que ces requêtes soient exécutées au sein de la même connexion à la base de données.

Processus ETL

Lorsque vous mettez en œuvre des processus ETL (Extract, Transform, Load) à l'aide de scripts SQL, vous devez souvent générer des résultats intermédiaires et appliquer des transformations successives à un seul ensemble de données. Ce n'est qu'une fois ces opérations terminées que vous pouvez enfin déverser les données dans des tables permanentes. Dans ces situations, il est très utile de conserver des tables temporaires tout au long du processus de transformation.

Grands ensembles de données

Lorsque vous travaillez sur des ensembles de données temporaires de grand volume (par exemple, des millions d'enregistrements), il est toujours préférable d'utiliser des tables temporaires plutôt que des CTE. Si vous essayez d'exécuter un CTE qui inclut une sous-requête de grand volume, il est très probable que le moteur de base de données essaiera de stocker les résultats temporaires dans la mémoire du serveur. Cela épuiserait la capacité disponible ou nécessiterait des ressources du système d'exploitation pour transférer la mémoire sur le disque. En bref, les performances de la base de données seraient médiocres. Par conséquent, si vous savez à l'avance que les résultats d'une sous-requête seront très volumineux, il est préférable d'utiliser des tables temporaires plutôt que des CTE.

Génération d'index

Comme toute autre table d'une base de données, les tables temporaires permettent la création d'index afin d'accélérer l'exécution des requêtes. Lorsque les résultats intermédiaires nécessitent un grand nombre de lignes (et doivent être utilisés à plusieurs reprises), il convient de les stocker dans une table temporaire dotée d'un ou de plusieurs index. La création des index peut prendre un certain temps, mais ce temps est largement récupéré si plusieurs requêtes doivent ensuite être exécutées sur le même ensemble de données.

En général, les colonnes utilisées dans JOINs, WHERE ou GROUP BY sont les meilleures candidates à l'indexation.

Conclusion du débat entre l'ETC et la table temporaire

Il n'est pas toujours facile de choisir entre les CTE et les tables temporaires. Nous avons abordé la syntaxe et les cas d'utilisation typiques de ces deux solutions.

N'oubliez pas de consulter notre cours Requêtes récursives qui vous apprendra à utiliser les expressions de table courantes. Ce cours s'adresse aux analystes de bases de données débutants, aux étudiants et aux développeurs désireux d'approfondir leurs connaissances en SQL. Pour suivre ce cours, il vous suffit de disposer d'un ordinateur avec une connexion Internet et d'un navigateur web.

Il est maintenant temps de déployer votre sagesse ! Si vous savez quand utiliser les CTE par rapport aux tables temporaires, vous obtiendrez toujours une efficacité maximale lorsque vous travaillerez avec des données temporaires.