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

Question

Dans cet excellent Question sur le SO , les différences entre CTE y sub-queries ont été discutées.

Je voudrais poser une question précise :

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

• CTE
• Sous-requête
• Tableau temporaire
• Tableau Variable

Traditionnellement, j'utilise beaucoup de temp tables en développant stored procedures - car elles semblent plus lisibles que de nombreuses sous-requêtes imbriquées les unes dans les autres.

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

---

EDITAR

On m'a récemment dit qu'en termes d'efficacité, les tables temporaires constituent un bon premier choix car elles sont associées à un histogramme, c'est-à-dire à des statistiques.

Answer 1

SQL est un langage déclaratif, pas un langage procédural. En d'autres termes, vous construisez une instruction SQL pour décrire les résultats que vous souhaitez obtenir. Vous ne dites pas au moteur SQL comment pour faire le travail.

En règle générale, il est préférable de laisser le moteur et l'optimiseur SQL trouver le meilleur plan de requête. Le développement d'un moteur SQL représente de nombreuses années-personnes d'efforts, alors laissez les ingénieurs faire ce qu'ils savent faire.

Bien entendu, il existe des situations où le plan d'interrogation n'est pas optimal. Vous souhaitez alors utiliser des astuces de requête, restructurer la requête, mettre à jour les statistiques, utiliser des tables temporaires, ajouter des index, etc. pour obtenir de meilleures performances.

Pour ce qui est de votre question. Les performances des ETC et des sous-requêtes devraient, en théorie, être les mêmes puisque les deux fournissent les mêmes informations à l'optimiseur de requêtes. La différence réside dans le fait qu'un ETC utilisé plusieurs fois peut être facilement identifié et calculé une seule fois. Les résultats pourraient alors être stockés et lus plusieurs fois. Malheureusement, SQL Server ne semble pas tirer parti de cette méthode d'optimisation de base (que l'on pourrait appeler l'élimination des sous-requêtes).

Les tables temporaires sont une question différente, car vous fournissez davantage d'indications sur la manière 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 d'interrogation. Cela peut se traduire par des gains de performance. En outre, si vous avez un CTE (sous-requête) compliqué qui est utilisé plus d'une fois, le fait de le stocker dans une table temporaire permet souvent d'améliorer les performances. La requête n'est exécutée qu'une seule fois.

La réponse à votre question est qu'il faut jouer un peu pour obtenir les performances que vous attendez, en particulier pour les requêtes complexes qui sont exécutées régulièrement. Dans un monde idéal, l'optimiseur de requêtes trouverait le chemin d'exécution parfait. Même si c'est souvent le cas, vous pouvez peut-être trouver un moyen d'améliorer les performances.

Answer 2

Il n'y a pas de règle. Je trouve les CTE plus lisibles et je les utilise sauf si ils présentent un problème de performance, auquel cas j'étudie le problème réel plutôt que de supposer que l'ETC est le problème et d'essayer de le réécrire en utilisant une approche différente. Le problème ne se limite généralement pas à la manière dont j'ai choisi de déclarer mes intentions dans la requête.

Dans certains cas, il est possible de démêler les CTE ou de supprimer les sous-requêtes et de les remplacer par une table #temp et de réduire la durée. Cela peut être dû à différents facteurs, tels que des statistiques périmées, l'incapacité d'obtenir des statistiques précises (par exemple en joignant une fonction évaluée par une 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 décomposition peut donner à l'optimiseur une chance de s'en sortir). Mais il y a aussi des cas où les E/S impliquées dans la création d'une table #temp peuvent l'emporter sur les autres aspects de performance qui peuvent rendre une forme de plan particulière utilisant un CTE moins attrayante.

Très honnêtement, il y a beaucoup trop de variables pour donner une réponse "correcte" à votre question. Il n'y a pas de moyen prévisible de savoir quand une requête peut pencher en faveur d'une approche ou d'une autre - sachez simplement qu'en théorie, la même sémantique pour un CTE ou une simple sous-requête devrait exécutent exactement la même chose. Je pense que votre question aurait plus de valeur si vous présentiez des cas où cela n'est pas vrai - il se peut que vous ayez découvert une limitation dans l'optimiseur (ou découvert une limitation connue), ou il se peut que vos requêtes ne soient pas sémantiquement équivalentes ou que l'une d'entre elles contienne un élément qui contrecarre l'optimisation.

Je vous suggère donc d'écrire la requête de la manière qui vous semble la plus naturelle, et de ne vous en écarter que si vous découvrez un problème de performance réel rencontré par l'optimiseur. Personnellement, je les classe par ordre d'importance : CTE, puis sous-requête, la table #temp étant le dernier recours.

Answer 3

Le #temp est matérialisé et le CTE ne l'est pas.

Le CTE n'est qu'une syntaxe, donc en théorie, il s'agit d'une sous-requête. Il est exécuté. #temp est matérialisé. Ainsi, un CTE coûteux dans une jointure qui est exécutée plusieurs fois peut être mieux dans un #temp. D'un autre côté, s'il s'agit d'une évaluation facile qui n'est exécutée que quelques fois, cela ne vaut pas la peine d'avoir recours à l'overhead du #temp.

Certaines personnes sur SO n'aiment pas les tables variables, mais je les aime car elles sont matérialisées et plus rapides à créer que #temp. Il arrive que l'optimiseur de requêtes fasse mieux avec une variable #temp qu'avec une variable table.

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

Answer 4

Il y a deux choses qui font qu'il est TOUJOURS préférable d'utiliser une table # Temp plutôt qu'un CTE :

1. Vous ne pouvez pas mettre une clé primaire sur un CTE, de sorte que les données auxquelles le CTE accède devront traverser chacun des index des tables du CTE au lieu d'accéder simplement à la clé primaire ou à l'index de la table temporaire.

2. Comme il n'est pas possible d'ajouter des contraintes, des index et des clés primaires à un CTE, celui-ci est plus sujet à l'apparition de bogues et de données erronées.

---

-le jour où hier

Voici un exemple où les contraintes #table peuvent empêcher les mauvaises données, ce qui n'est pas le cas dans les 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;