Qui sont les plus performants, CTE ou tables temporaires?
Qui sont les plus performants, CTE
ou Temporary Tables
?
- Question connexe: dba.stackexchange.com/q/13112
- Les utilisateurs peuvent trouver des informations de fond (non liées à la performance) dans le à l'Aide d'Expressions de Table Communes sur la page technet.microsoft.com.
Vous devez vous connecter pour publier un commentaire.
Je dirais qu'ils sont des concepts différents, mais pas trop différent-à-dire "la craie et le fromage".
D'une table temporaire est bon pour ré-utiliser ou d'effectuer de multiples de traitement de passe sur un jeu de données.
Une expression de table commune peut être utilisée pour répéter ou pour tout simplement améliorer la lisibilité.
Et, comme une vue ou en ligne, fonction à valeur de table peut également être traitée comme une macro être développés dans la requête principale
D'une table temporaire est une autre table avec des règles autour de la portée
Je l'ai stocké procs où j'utilise les deux (et les variables de table trop)
cte vs temporary tables
donc, à mon humble avis, cette réponse doit mettre en évidence les inconvénients de la CTE est mieux. TL;DR de la réponse: une expression de table commune ne doit jamais être utilisé pour la performance.. Je suis d'accord avec cette citation que j'ai vécu les inconvénients de la CTE est.Il dépend.
Tout d'abord
Ce qui est une Expression de Table Commune?
(Non récursif) CTE est traité de façon très similaire aux autres constructions, qui peut également être utilisé comme inline expressions de table dans SQL Server. Dérivé de tables, de Vues, et inline fonctions à valeur de table. Notez que tandis BOL dit qu'une expression de table commune "peut être considéré comme temporaire de l'ensemble de résultats" c'est une pure logique de description. Le plus souvent, ce n'est pas materlialized dans son propre droit.
Ce qu'est une table temporaire?
C'est une collection de lignes stockées sur des pages de données dans la base de données tempdb. Les pages de données peuvent résider partiellement ou entièrement dans la mémoire. En outre, la table temporaire peut être indexé et des colonnes de statistiques.
Des Données De Test
Exemple 1
Avis dans le plan ci-dessus il n'y a aucune mention de CTE1. Il a juste accède à la base de tables directement et est traité de la même façon que
Réécriture en matérialisant le CTE dans une table temporaire intermédiaire ici serait très contre-productif.
Matérialisant le CTE définition de
Impliquerait la copie sur 8 go de données dans une table temporaire puis il y a encore les frais généraux de sélection de trop.
Exemple 2
L'exemple ci-dessus prend environ 4 minutes sur ma machine.
Seulement 15 lignes de la de 1 000 000 généré de façon aléatoire les valeurs correspondent le prédicat, mais le cher analyse de la table qui se passe de 16 fois pour localiser ces.
Ce serait un bon candidat pour la concrétisation de ce résultat intermédiaire. L'équivalent de la table temporaire de réécriture a pris 25 secondes.
Intermédiaire matérialisation d'une partie de la requête dans une table temporaire peut parfois être utile, même si elle n'est évaluée qu'une fois - quand il permet au reste de la requête afin d'être recompilé profitant de statistiques sur les résultats matérialisé. Un exemple de cette approche est dans le SQL Chat de l'article Quand À Briser Des Requêtes Complexes.
Dans certaines circonstances, SQL Server utilise une bobine à cache un résultat intermédiaire, par exemple, d'un CTE, et éviter d'avoir à ré-évaluer ce sous-arbre. Cette question est abordée dans l' (émigré) élément de connexion Fournir une indication de la force intermédiaire de la réalisation des expressions de table communes ou les tables dérivées. Toutefois, aucune statistique n'est créé sur ce sujet et même si le nombre de rangées en file d'attente était d'être extrêmement différents des estimations n'est pas possible pour l'avancement de l'exécution du plan d'adapter dynamiquement en réponse (au moins dans les versions actuelles. Adaptative des Plans de Requête peut devenir possible dans le futur).
CTE, a ses utilise - lorsque les données de la CTE est petit et il y a de fortes raisons de lisibilité de l'amélioration de l'affaire en récursif tables. Cependant, ses performances sont certainement pas mieux que les variables de table et quand on est face à de très grandes tables, tables temporaires significativement surpasser CTE. C'est parce que vous ne pouvez pas définir des indices sur une CTE et quand vous avez la grande quantité de données qui nécessite de la jointure avec une autre table (CTE est tout simplement comme une macro). Si vous êtes de rejoindre plusieurs tables avec des millions de lignes d'enregistrements dans chaque, CTE va effectuer beaucoup plus de mal que les tables temporaires.
Tables temporaires sont toujours sur le disque - tant et aussi longtemps que votre CTE peut être conservé dans la mémoire, il serait le plus susceptible d'être plus rapide (comme une variable de table, trop).
Mais là encore, si le chargement des données de votre CTE (ou température variable de table) devient trop grande, il va être stocké sur le disque, aussi, donc il n'y a pas grand avantage.
En général, je préfère un CTE sur une table temp depuis qu'il est parti après que je l'ai utilisé. Je n'ai pas besoin de penser à les laisser tomber explicitement ou quoi que ce soit.
Donc, pas de réponse claire à la fin, mais personnellement, je préfère CTE sur des tables temporaires.
CTE de ne pas prendre tout l'espace physique. C'est juste un jeu de résultats, nous pouvons utiliser la rejoindre.
Tables temporaires sont temporaires. Nous pouvons créer des index, des contraintes que comme des tables normales pour cela nous avons besoin de définir toutes les variables.
De la table temporaire est portée uniquement à l'intérieur de la session.
EX:
Ouvrir deux fenêtre de requête SQL
Exécuter cette requête dans la première fenêtre
puis exécutez la requête ci-dessous dans la deuxième fenêtre, vous pouvez trouver la différence.
Ainsi la requête j'ai été affecté à optimiser a été écrit avec deux expressions de table communes dans SQL server. Elle a été prise 28sec.
J'ai passé deux minutes de la conversion à des tables temporaires et la requête a pris 3 secondes
J'ai ajouté un index de la table temporaire sur le terrain, il a été rejoint sur et eu 2 secondes
Trois minutes de travail et de son fonctionnement à 12x plus rapide en supprimant les CTE. Personnellement, je ne vais pas utiliser d'expressions de table communes jamais elles sont plus difficiles à déboguer ainsi.
Le fou, c'est les expressions de table communes ont tous deux été utilisée une fois seulement et encore de mettre un index sur eux s'est avéré être 50% plus rapide.
J'ai utilisé les deux, mais dans le massif des procédures complexes ont toujours trouvé des tables temporaires préférable de travailler avec et plus méthodique. D'expressions de table communes ont leurs usages mais généralement avec des données de petite taille.
Par exemple, j'ai créé sprocs que de revenir avec les résultats de calculs dans 15 secondes encore convertir ce code dans un CTE et l'ont vu courir de plus de 8 minutes pour atteindre les mêmes résultats.
La fin de la partie, mais...
Le contexte où je travaille est très limitée, le soutien de certains produits de fournisseurs et de fournir des "valeur ajoutée" des services comme la création de rapports. En raison de la politique et des limites des contrats, je ne suis pas habituellement permis le luxe de la table séparée/espace de données et/ou de la capacité à créer du code permanent [il fait un peu mieux, en fonction de l'application].
OIE, je ne peut pas développent généralement une procédure stockée ou de l'Udf ou de tables temporaires, etc. Je suis assez d'avoir à tout faire grâce à MON interface de l'application (Rapports Crystal - ajouter/lien tables, jeu où l'une des clauses de w/CR, etc.). Un PETIT grâce salvatrice est que le Cristal me permet d'utiliser les COMMANDES (ainsi que les Expressions SQL). Certaines choses qui ne sont pas efficaces dans le cadre d'ajouter ou de lier les tables de la capacité peut être fait par la définition d'une Commande SQL. J'ai utiliser des expressions de table communes à travers et ont obtenu de très bons résultats "à distance". D'expressions de table communes également aider w/rapport d'entretien, ne nécessitant qu'un code soit développé, remis à un administrateur de base de compiler, de chiffrer, le transfert, l'installer, puis d'exiger plusieurs niveaux de tests. Je peux faire d'expressions de table communes par le biais de l'interface locale.
Le côté à l'aide d'expressions de table communes w/CR est, chaque rapport est séparé. Chaque CTE doit être maintenu pour chaque rapport. Où je peux faire SPs et de l'Udf, je peux créer quelque chose qui peut être utilisé par plusieurs rapports, ne nécessitant que la liaison à la SP et passage de paramètres, comme si vous travailliez sur une table ordinaire. CR n'est pas vraiment bon à la manipulation des paramètres des Commandes SQL, de sorte que l'aspect de la CR/CTE aspect peut être dépourvues. Dans ces cas, j'ai l'habitude de tenter de définir la CTE de retour suffisamment de données (mais pas TOUTES les données), puis utilisez la sélection de l'enregistrement des capacités dans le CR de la tranche et dés.
... Donc mon vote est pour les expressions de table communes (jusqu'à ce que je reçois mon espace de données).
Une utilisation où j'ai trouvé la CTE est excellé performance sage était l'endroit où je devais rejoindre une relativement complexe Requête sur un quelques tables qui avait quelques millions de lignes chacune.
J'ai utilisé la CTE d'abord sélectionner le sous-ensemble en fonction des colonnes indexées à la première coupe de ces tables à quelques milliers de lignes pertinentes de chaque, puis rejoint le CTE pour ma requête principale. De cette façon exponentielle réduit le temps d'exécution de ma requête.
Tandis que les résultats pour le CCE ne sont pas mis en cache et les variables de table pourrait avoir été un meilleur choix, je voulais juste essayer et trouver l'ajustement du scénario ci-dessus.
C'est vraiment une question ouverte, et tout dépend de son utilisation et le type de la table temporaire (variable de Table ou table traditionnelle).
Traditionnel temp table stocke les données dans le temp DB, ce qui ralentit les tables temporaires; cependant les variables de table ne.
Je viens de tester cette - fois CTE et non-CTE (où la requête a été tapé, pour chaque instance de l'union), les deux ont eu ~31 secondes. CTE fait le code beaucoup plus lisible si - de couper vers le bas à partir de 241 à 130 lignes qui est très agréable. La table temporaire sur l'autre côté de couper vers le bas à 132 lignes, et a pris CINQ SECONDES pour s'exécuter. Pas de blague. l'ensemble de ces essais a été mis en cache les requêtes ont tous été exécuté plusieurs fois avant.
De mon expérience dans SQL Server,j'ai trouvé l'un des scénarios où les CTE ont surpassé de la table temporaire
J'avais besoin d'utiliser un DataSet(~100000) à partir d'une Requête complexe, juste une FOIS dans ma Procédure stockée.
De la table temporaire a été provoquant une surcharge sur SQL où mon intervention a été
l'exécution est lente(comme les Tables temporaires sont de véritables matérialisé tables
exister dans la base de données tempdb et Persistent pendant la vie de ma procédure en cours)
D'autre part, avec CTE, CTE Persistent jusqu'à la suivante
exécution d'une requête. Ainsi, la CTE est une pratique structure en mémoire limitée
La portée. D'expressions de table communes de ne pas utiliser la base de données tempdb par défaut.
C'est un scénario où les expressions de table communes peuvent vraiment aider à simplifier votre code et de Surperformer de la Table temporaire.
J'avais Utilisé 2 expressions de table communes, quelque chose comme