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?

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.

#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).

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;