Y a-t-il une différence de performance entre la variable CTE, la variable Sub-Query, la Variable Temporary Table ou la Variable Table?

dans cet excellent ainsi question , les différences entre CTE et sub-queries ont été discutées.

je voudrais demander spécifiquement:

dans quelles circonstances chacun des éléments suivants est-il plus efficace/plus rapide?

  • CTE
  • Sous-Requête
  • Table Temporaire
  • Variable De Table

traditionnellement, j'ai utilisé beaucoup de temp tables dans le développement de stored procedures - car ils semblent plus lisibles que beaucoup de sous-requêtes entrelacées.

Non-recursive CTE s encapsule ensembles de données très bien, et sont très lisibles, mais y a-t-il des circonstances spécifiques où l'on peut dire qu'ils seront toujours mieux? ou est-ce un cas de devoir toujours jouer avec les différentes options pour trouver la solution la plus efficace?


MODIFIER

on m'a dit récemment qu'en termes d'efficacité, les tableaux temporaires sont un bon premier choix car ils ont un histogramme associé, c'est-à-dire des statistiques.

143
demandé sur DineshDB 2012-06-23 16:36:14

4 réponses

SQL est un langage déclaratif, pas un langage procédural. C'est, de vous construire une instruction SQL pour décrire les résultats que vous voulez. Vous ne dites pas le moteur SQL comment pour faire le travail.

en règle générale, c'est une bonne idée de laisser le moteur SQL et l'optimiseur SQL trouver le meilleur plan de requête. Il ya beaucoup de personnes-années d'efforts qui vont dans le développement D'un moteur SQL, donc laissez les ingénieurs faire ce qu'ils savent faire.

bien sûr, il y a des situations où le plan de requête n'est pas optimal. Ensuite, vous voulez utiliser des indices de requête, restructurer la requête, mettre à jour les statistiques, utiliser des tables temporaires, ajouter des index, et ainsi de suite pour obtenir de meilleures performances.

comme pour votre question. La performance des CTE et des sous-ensembles devrait, en théorie, être la même puisque les deux fournissent la même information à l'optimiseur de requêtes. Une différence est qu'un CTE utilisé plus d'une fois pourrait être facilement identifié et calculé une fois. Les résultats peuvent ensuite être stockés et lus plusieurs fois. Malheureusement, SQL Server ne semble pas tirer avantage de cette méthode d'optimisation de base (vous pourriez appeler cette élimination de sous-query commune).

tables temporaires sont une question différente, parce que vous fournissez plus de conseils sur la façon dont la requête doit être exécutée. Une différence majeure est que l'optimiseur peut utiliser les statistiques de la table temporaire pour établir son plan de requête. Ce peut entraîner des gains de performances. En outre, si vous avez un CTE (subquery) compliqué qui est utilisé plus d'une fois, alors le stocker dans une table temporaire donnera souvent un coup de pouce de performance. La requête est exécutée qu'une seule fois.

La réponse à votre question est que vous devez jouer pour obtenir les résultats attendus, en particulier pour les requêtes complexes qui sont exécutés sur une base régulière. Dans un monde idéal, l'optimiseur de requêtes trouverait le chemin d'exécution parfait. Bien qu'il souvent, vous pourriez être en mesure de trouver un moyen d'obtenir de meilleures performances.

175
répondu Gordon Linoff 2012-06-23 13:32:05

il n'y a pas de règle. Je trouve les CTE plus lisibles, et les utiliser à moins que ils présentent un certain problème de performance, dans ce cas, j'examine le problème réel plutôt que de deviner que le CTE est le problème et essayer de le réécrire en utilisant une approche différente. Il y a habituellement plus à la question que la façon dont j'ai choisi de déclarer mes intentions avec la requête.

Il ya certainement des cas où vous pouvez démêler CTEs ou supprimer des sous-séries et les remplacer par une table #temp et réduire la durée. Cela peut être dû à diverses choses, telles que des statistiques périmées, l'incapacité d'obtenir des statistiques exactes (par exemple en se joignant à une fonction de valeur de table), le parallélisme, ou même l'incapacité de générer un plan optimal en raison de la complexité de la requête (dans ce cas, la briser peut donner à l'optimiseur une chance de lutte). Mais il y a aussi des cas où les e/s impliqués dans la création d'une table #temp peuvent l'emporter sur les autres aspects de performance peut rendre une forme de plan particulière en utilisant un CTE moins attrayant.

très honnêtement, il y a beaucoup trop de variables pour fournir une réponse "correcte" à votre question. Il n'y a pas de façon prévisible de savoir quand une requête peut basculer en faveur d'une approche ou d'une autre - il suffit de savoir que, en théorie, la même sémantique pour un CTE ou un seul subquery devrait exécuter la même chose exacte. Je pense que votre question serait plus utile si vous présentez certains cas où ce n'est pas vrai - il se peut que vous ayez découvert une limite dans l'optimiseur (ou découvert une limite connue), ou il se peut que vos requêtes ne soient pas sémantiquement équivalentes ou que l'on contienne un élément qui contrecarre l'optimisation.

donc je suggère d'écrire la requête d'une manière qui vous semble la plus naturelle, et ne dévier que lorsque vous découvrez un problème de performance réelle que l'optimiseur a. Personnellement, je les classe CTE, puis subquery, avec # temp table étant une dernière Resort.

54
répondu Aaron Bertrand 2012-06-23 15:55:53

#temp est materalized et CTE ne l'est pas.

CTE est juste syntaxe donc en théorie c'est juste une sous-requête. Il est exécuté. #temp est matérialisé. Donc un CTE coûteux dans une jointure qui est exécutée plusieurs fois peut être mieux dans un #temp. De l'autre côté, s'il s'agit d'une évaluation facile qui n'est pas exécutée mais qui ne vaut pas le coup de #temp.

il y a des gens dessus qui n'aiment pas la variable de table mais je les aime comme ils sont matérialisé et plus rapide à créer que #temp. Il y a des moments où l'optimiseur de requêtes fait mieux avec un #temp comparé à une variable de table.

la possibilité de créer un PK sur une variable #temp ou table donne à l'optimiseur de requête plus d'informations qu'un CTE (car vous ne pouvez pas déclarer un PK sur un CTE).

14
répondu paparazzo 2015-10-22 18:19:06

je pense Qu'il est toujours préférable d'utiliser une table # Temp plutôt qu'une table CTE sont:

  1. vous ne pouvez pas mettre une clé primaire sur un CTE de sorte que les données accessibles par le CTE devront parcourir chacun des index dans les tables du CTE plutôt que de simplement accéder au PK ou à L'Index sur la table de température.

  2. parce que vous ne pouvez pas ajouter des contraintes, des index et des clés primaires à un CTE ils sont plus enclin à des bugs rampant et de mauvaises données.


-onedaywhen yesterday

voici un exemple où les contraintes # table peuvent empêcher de mauvaises données ce qui n'est pas le cas dans CTE

DECLARE @BadData TABLE ( 
                       ThisID int
                     , ThatID int );
INSERT INTO @BadData
       ( ThisID
       , ThatID
       ) 
VALUES
       ( 1, 1 ),
       ( 1, 2 ),
       ( 2, 2 ),
       ( 1, 1 );

IF OBJECT_ID('tempdb..#This') IS NOT NULL
    DROP TABLE #This;
CREATE TABLE #This ( 
             ThisID int NOT NULL
           , ThatID int NOT NULL
                        UNIQUE(ThisID, ThatID) );
INSERT INTO #This
SELECT * FROM @BadData;
WITH This_CTE
     AS (SELECT *
           FROM @BadData)
     SELECT *
       FROM This_CTE;
9
répondu ShanksPranks 2016-07-28 20:31:09