Lesquels sont plus performants, CTE o Temporary Tables ?
Les tableaux temporaires permettent également d'utiliser des index et même des statistiques, qui sont parfois nécessaires, alors qu'un CTE ne le permet pas.
Réponses
Trop de publicités?Je dirais qu'il s'agit de concepts différents, mais pas trop différents pour qu'on puisse parler de "craie et fromage".
-
Une table temporaire est utile pour la réutilisation ou pour effectuer plusieurs passages de traitement sur un ensemble de données.
-
Un CTE peut être utilisé soit pour effectuer une récursion, soit pour améliorer simplement la lisibilité.
Et, comme une vue ou un tableau en ligne, la fonction valorisée peut également être traitée comme une macro à développer dans la requête principale. -
Une table temporaire est une autre table dont la portée est soumise à certaines règles.
J'ai des procédures stockées où j'utilise les deux (ainsi que des variables de table).
Je pense que cette réponse ne met pas assez en évidence le fait que le CTE peut conduire à des performances terribles. Je me réfère habituellement à cette réponse sur dba.stackexchange. Votre question apparaît en deuxième position dans mon moteur de recherche si je recherche cte vs temporary tables Donc, à mon avis, cette réponse doit mieux souligner les inconvénients des CTE. Résumé de la réponse liée : un CTE ne doit jamais être utilisé pour les performances. . Je suis d'accord avec cette citation car j'ai fait l'expérience des inconvénients de l'ETC.
CSW
Points
281
Le CTE a son utilité - lorsque les données dans le CTE sont petites et qu'il y a une forte amélioration de la lisibilité comme dans le cas des tableaux récursifs. Cependant, ses performances ne sont certainement pas meilleures que celles des variables de table et, lorsque l'on a affaire à de très grandes tables, les tables temporaires sont nettement plus performantes que les CTE. Cela s'explique par le fait que vous ne pouvez pas définir d'indices sur un CTE et que lorsque vous avez une grande quantité de données qui nécessitent une jonction avec une autre table (le CTE est simplement comme une macro). Si vous joignez plusieurs tables contenant chacune des millions de lignes d'enregistrement, l'outil CTE sera nettement moins performant que les tables temporaires.
Les CTE sont également plus lents car les résultats ne sont pas mis en cache. Ainsi, chaque fois que vous utilisez le CTE, il réexécute la requête, le plan et tout le reste.
Et le moteur de BD peut choisir de réexécuter la requête non seulement pour chaque référence, mais pour chaque rangée de la requête du consommateur, en tant que sous-requête corrélée... vous devez toujours faire attention à cela si ce n'est pas souhaité.
marc_s
Points
321990
Les tables temporaires sont toujours sur le disque. Par conséquent, tant que votre CTE peut être conservé en mémoire, il sera très probablement plus rapide (comme une variable de table, également).
Mais là encore, si la charge de données de votre CTE (ou de la variable de la table temporaire) devient trop importante, elle sera également stockée sur le disque, ce qui ne présente pas de gros avantages.
En général, je préfère un CTE à une table temporaire puisqu'elle disparaît après que je l'ai utilisée. Je n'ai pas besoin de penser à l'abandonner explicitement ou autre.
Il n'y a donc pas de réponse claire au final, mais personnellement, je préférerais les CTE aux tables temporaires.
Dans le cas de SQLite et PostgreSQL, les tables temporaires sont automatiquement abandonnés (généralement à la fin d'une session). Je ne connais pas d'autres SGBD.
Le CTE est comme une vue temporaire. A priori, les données ne sont pas stockées, donc rien ne peut être conservé en mémoire ou stocké sur le disque. Remarque importante : chaque fois que vous utilisez le CTE, la requête s'exécute à nouveau.
selvaraj
Points
333
Le CTE ne prendra pas d'espace physique. Il s'agit simplement d'un ensemble de résultats que nous pouvons utiliser pour joindre.
Les tables temporaires sont temporaires. Nous pouvons créer des index, des contraintes comme pour les tables normales, pour cela nous devons définir toutes les variables.
La portée de la table temporelle uniquement dans la session. EX : Ouvrir deux fenêtres de requêtes SQL
create table #temp(empid int,empname varchar)
insert into #temp
select 101,'xxx'
select * from #tempExécutez cette requête dans la première fenêtre puis exécutez la requête ci-dessous dans la seconde fenêtre, vous pourrez constater la différence.
select * from #temp
>> "C'est juste un ensemble de résultats que nous pouvons utiliser pour joindre." -> Ce n'est pas exact. Le CTE n'est pas un "ensemble de résultats" mais un code en ligne. Le moteur de requête du serveur SQL analyse le code CTE comme faisant partie du texte de la requête et construit un plan d'exécution en conséquence. L'idée que le CTE est en ligne est le grand avantage de l'utilisation du CTE, car il permet au serveur de créer un "plan d'exécution combiné".
purchas
Points
123
Une utilisation où j'ai trouvé que les CTE excellaient en termes de performances était celle où j'avais besoin de joindre une requête relativement complexe à quelques tables qui avaient quelques millions de lignes chacune.
J'ai utilisé le CTE pour sélectionner d'abord le sous-ensemble basé sur les colonnes indexées pour réduire ces tables à quelques milliers de lignes pertinentes chacune, puis j'ai joint le CTE à ma requête principale. Cela a réduit de façon exponentielle le temps d'exécution de ma requête.
Bien que les résultats du CTE ne soient pas mis en cache et que les variables de table auraient pu être un meilleur choix, je voulais vraiment les essayer et j'ai trouvé qu'elles correspondaient au scénario ci-dessus.
4 votes
Question connexe : dba.stackexchange.com/q/13112
0 votes
Les utilisateurs peuvent trouver des informations de base (ne concernant pas les performances) dans la rubrique Utilisation des expressions de tableau communes sur technet.microsoft.com.