Quelle est la différence entre un CTE et une table temporaire?


150

Quelle est la différence entre une expression de table commune (CTE) et une table temporaire?Et quand devrais-je utiliser l'un sur l'autre?

CTE

WITH cte (Column1, Column2, Column3)
AS
(
     SELECT Column1, Column2, Column3
     FROM SomeTable
)

SELECT * FROM cte

Table temporaire

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable
+3

Voir aussi [Quelle est la différence entre une table temporaire et une variable de table dans SQL Server?] (Http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table -variable-in-sql-server) 15 févr.. 122012-02-15 17:02:17

+1

En relation avec SO: [Quelles sont les tables les plus performantes, CTE ou temporaires?] (Http://stackoverflow.com/a/698634/27535) 16 févr.. 122012-02-16 07:52:38

178

C'est assez large, mais je vais vous donner une réponse aussi générale que possible.

CTE ...

  • Sont non indexables (mais peuvent utiliser des index existants sur des objets référencés)
  • Impossible d'avoir des contraintes
  • Sont essentiellement jetables VIEW s
  • Ne persiste que jusqu'à la prochaine requête
  • Peut être récursif
  • Ne pas avoir de statistiques dédiées (utilisez des statistiques sur les objets sous-jacents)

#Temp Tables ...

  • Sont des tables matérialisées réelles qui existent dans tempdb
  • Peut être indexé
  • Peut avoir des contraintes
  • Persister pour la vie de la connexion actuelle
  • Peut être référencé par d'autres requêtes ou sous-procédures
  • Avoir des statistiques dédiées générées par le moteur

En ce qui concerne le moment d'utiliser chacun, ils ont des cas d'utilisation très différents.Si vous avez un jeu de résultats très volumineux ou si vous avez besoin de vous y référer plusieurs fois, placez-le dans un tableau #temp .Si elle doit être récursive, jetable ou simplement pour simplifier quelque chose de manière logique, un CTE est préférable.

En outre, un CTE devraitne jamais être utilisé pour la performance.Vous n'allez presque jamais accélérer les choses en utilisant un CTE, parce que, encore une fois, ce n'est qu'une vue jetable.Vous pouvez faire des choses intéressantes avec eux, mais accélérer une requête n'en fait pas partie.


23

MODIFIER:

S'il vous plaît voir les commentaires de Martin ci-dessous:

Le CTE n'est pas matérialisé sous la forme d'une table en mémoire.C'est juste une façon d'encapsuler une définition de requête.Dans le cas du PO, ce sera en ligne et le même que faireSELECT Column1, Column2, Column3 FROM SomeTable.La plupart du temps, ils ne se matérialisent pas à l’avance, c’est pourquoi cela ne renvoie aucune ligne.WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X, vérifiez également les plans d'exécution.Bien qu'il soit parfois possible de pirater le plan pour obtenir une bobine.Un élément de connexion demande un indice pour cela.- Martin Smith 15 février 12 à 17:08


Réponse originale

CTE

Read more on MSDN

Un CTE crée la table utilisée en mémoire, mais n'est valide que pour la requête spécifique qui la suit.Lorsque vous utilisez la récursivité, cela peut être une structure efficace.

Vous pouvez également envisager d'utiliser une variable de table.Ceci est utilisécommeune table temporaire est utilisée et peut être utilisée plusieurs fois sans qu'il soit nécessaire de re-matérialiser chaque jointure.En outre, si vous devez conserver quelques enregistrements maintenant, ajoutez quelques enregistrements supplémentaires après la sélection suivante, ajoutez quelques enregistrements supplémentaires après une autre opération, puis renvoyez uniquement ces quelques enregistrements. Cette structure peut donc être pratique. pas besoin d'être abandonné après l'exécution.Surtout juste du sucre syntaxique.Toutefois, si vous maintenez un nombre de lignes faible, il ne se matérialise jamais sur le disque.VoirWhat's the difference between a temp table and table variable in SQL Server?pour plus de détails.

Table temporaire

Read more on MSDN - Scroll down about 40% of the way

Une table temporaire est littéralement une table créée sur le disque, mais dans une base de données spécifique pouvant être supprimée par tous.Il incombe à un bon développeur de détruire ces tables lorsqu'elles ne sont plus nécessaires, mais un administrateur de base de données peut également les effacer.

Les tables temporaires sont de deux types: locale et globale.En termes de MS SQL Server, vous utilisez un#tableNamedésignation pour local, et##tableNamedésignation pour global (notez l'utilisation d'un simple ou d'un double # comme caractéristique d'identification).

Notez qu'avec les tables temporaires, par opposition aux variables de table ou au CTE, vous pouvez appliquer des index, etc., car il s'agit légitimement de tables au sens normal du mot.


En général, j'utilisais des tables temporaires pour les requêtes plus longues ou plus longues, ainsi que des CTE ou des variables de table si j'avais déjà un petit jeu de données et si je voulais juste écrire rapidement un peu de code pour quelque chose de petit.L’expérience et les conseils d’autres personnes indiquent que vous devez utiliser des CTE pour lesquels vous ne recevez qu’un petit nombre de lignes.Si vous avez un grand nombre, vous bénéficierez probablement de la possibilité d'indexer sur la table temporaire.

+11

Le CTE n'est pas matérialisé sous la forme d'une table en mémoire.C'est juste une façon d'encapsuler une définition de requête.Dans le cas de l'OP, il sera en ligne et identique à juste faire 'SELECT Column1, Column2, Column3 FROM SomeTable' 15 févr.. 122012-02-15 16:55:26

+4

La plupart du temps, ils ne se matérialisent pas à l’avance. C’est pourquoi il ne renvoie aucune ligne 'WITH T (X) AS (SELECT NEWID()) SELECT * FROM T T1 JOIN T T2 ON T1.X = T2.X', vérifiez également les plans d'exécution.Bien qu'il soit parfois possible de [pirater le plan] (http://explainextended.com/2009/05/28/generating-xml-in-subqueries/) pour obtenir un spool.Il existe un [élément de connexion] (https://connect.microsoft.com/SQLServer/feedback/details/218968/provide-a-hint-to-force-intermediate-materialization-of-ctes-or-derivedivedttables) demander un indice pour cela. 15 févr.. 122012-02-15 17:08:33


12

Un CTE peut être appelé à plusieurs reprises dans une requête et est évalué chaque fois qu'il est référencé - ce processus peut être récursif.S'il est simplement référencé une fois, il se comporte alors comme une sous-requête, même si les CTE peuvent être paramétrés.

Une table temporaire est physiquement persistante et peut être indexée.En pratique, l'optimiseur de requête peut également conserver des résultats de jointure ou de sous-requête intermédiaires en coulisse, comme dans les opérations de spoule, il n'est donc pas strictement vrai que les résultats des CTE ne sont jamais conservés sur le disque.

Les variables de table IIRC (en revanche) sont toujours des structures en mémoire.

+4

Les CTE peuvent être paramétrés?Comment?De plus, les variables de table ne sont pas * toujours * des structures en mémoire.Voir [excellente réponse] de Martin (http://dba.stackexchange.com/a/16386/1192) à une question connexe. 30 sept.. 162016-09-30 06:14:36


10

La table temporaire est un objet réel dans tempdb, mais cte n’est qu’une sorte de wrapper autour d’une requête complexe pour simplifier la syntaxe d’organiser la récursivité en une étape.


12

Le accepted answer indique ici "un CTE ne doit jamais être utilisé pour la performance", mais cela pourrait induire en erreur.Dans le contexte des CTE par rapport aux tables temporaires, je viens de terminer la suppression d'une série de fichiers indésirables d'une suite de procédures stockées, car certains doofus ont pensé qu'il n'y avait que peu ou pas de temps système pour utiliser des tables temporaires.J'ai mis le paquet dans les CTE, à l' exception de ceux qui allaient légitimement être réutilisés tout au long du processus.J'ai gagné environ 20% de performance par tous les indicateurs.J'ai ensuite entrepris de supprimer tous les curseurs qui essayaient d'implémenter un traitement récursif.C'est là que j'ai vu le plus grand gain.J'ai fini par réduire les temps de réponse d'un facteur dix.

Les CTE et les tables temporaires ont des cas d'utilisation très différents.Je tiens simplement à souligner que, bien que n'étant pas une panacée, la compréhension et l'utilisation correcte des CTE peuvent conduire à des améliorations vraiment remarquables à la fois en termes de qualité/maintenabilité et de rapidité du code.Depuis que je les maîtrise, je considère les tables temporaires et les curseurs comme les grands maux du traitement SQL.Je peux me débrouiller très bien avec les variables de table et les CTE pour presque tout maintenant.Mon code est plus propre et plus rapide.

  0

Maintenant, soyons justes - les curseurs sont le * grand * mal;Les tables temporaires sont au pire un * moindre * mal.:-) C'est * vraiment * injuste de les mettre au même niveau que vous vous êtes vus. 22 janv.. 182018-01-22 20:50:17

  0

@RDFozz à droite, [l'enfer a 9 cercles comme nous le savons tous] (https://en.wikipedia.org/wiki/Inferno_ (Dante) #Nine_circles_of_Hell).Permet de mettre les tables temporaires au 2ème et les curseurs au ... 7ème?;) 22 janv.. 182018-01-22 21:52:08


6

La principale raison d'utiliser les CTE est d'accéder aux fonctions de la fenêtre telles que row_number() et autres.

Cela signifie que vous pouvez faire des choses comme obtenir la première ou la dernière ligne par groupe TRÈS TRÈS rapidement et efficacement - more efficiently than other means in most practical cases .

with reallyfastcte as (
select *, 
row_number() over (partition by groupingcolumn order by sortingcolumn) as rownum
from sometable
)
select *
from reallyfastcte
where rownum = 1;

Vous pouvez exécuter une requête similaire à celle décrite ci-dessus en utilisant une sous-requête corrélée ou en utilisant une sous-requête, mais le CTE sera plus rapide dans presque tous les scénarios.

De plus, les CTE peuvent vraiment aider à simplifier votre code.Cela peut entraîner des gains de performances car vous comprenez mieux la requête et pouvez introduire davantage de logique métier pour aider l'optimiseur à être plus sélectif.

De plus, les CTE peuvent augmenter les performances si vous comprenez votre logique métier et les parties de la requête à exécuter en premier. En règle générale, placez vos requêtes les plus sélectives en premier pour aboutir à des ensembles de résultats pouvant utiliser un index dans leur prochaine jointure, puis ajoutez le option(force order) indice de requête

Enfin, les CTE n'utilisent pas tempdb par défaut, ce qui vous permet de réduire les conflits sur ce goulot d'étranglement grâce à leur utilisation.

Les tables temporaires doivent être utilisées si vous devez interroger les données plusieurs fois, ou si vous mesurez vos requêtes et les découvrez en les insérant dans une table temporaire, puis en ajoutant un index pour améliorer vos performances.

  0

tous les bons points ... +1 30 janv.. 182018-01-30 13:05:25


5

Il semble y avoir un peu de négativité ici envers les CTE.

Si je comprends bien, le CTE est essentiellement une sorte de point de vue ad hoc.SQL est un langage à la fois déclaratif et basé sur un ensemble.Les CTE sont un excellent moyen de déclarer un ensemble!Ne pas être en mesure d'indexer un CTE est en fait une bonne chose car vous n'en avez pas besoin!C'est vraiment une sorte de sucre syntaxique pour rendre la requête plus facile à lire/écrire.Tout optimiseur décent déterminera le meilleur plan d’accès en utilisant des index sur les tables sous-jacentes.Cela signifie que vous pourriez accélérer efficacement votre requête CTE en suivant les conseils d'index sur les tables sous-jacentes.

De même, le fait que vous ayez défini un ensemble en tant que CTE ne signifie pas que toutes les lignes de cet ensemble doivent être traitées.En fonction de la requête, l'optimiseur peut traiter "juste assez" de lignes pour satisfaire la requête.Peut-être n’avez-vous besoin que des 20 premiers exemplaires de votre écran.Si vous avez construit une table temporaire, vous devez vraiment lire/écrire toutes ces lignes!

Sur cette base, je dirais que les CTE sont une excellente fonctionnalité de SQL et peuvent être utilisés partout où ils facilitent la lecture de la requête.Je ne penserais qu'à une table temporaire pour un traitement par lots qui aurait vraiment besoin de traiter chaque enregistrement.Même alors, après tout, ce n'est pas vraiment recommandé, car dans une table temporaire, la base de données est beaucoup plus difficile à vous aider à mettre en cache et à indexer.Il peut être préférable d’avoir une table permanente avec un champ PK unique pour votre transaction.

Je dois admettre que mon expérience concerne principalement DB2; je suppose donc que le travail de CTE fonctionne de manière similaire dans les deux produits.Je serai heureusement corrigé si les CTE sont inférieurs au serveur SQL.;)