Blog

Différences entre tables temporaires et variable table : ##temp, #temp, @temp… ou CTE ?


Question récurrente : Quelles différences entre une table temporaire ##temp, #temp (et non, ça n’a rien à voir avec un hashtag), une variable table @temp et une CTE (même si c’est un peu à part) ? C’est un article que je songeais à écrire depuis longtemps car à chaque audit SQL, le sujet revient sur le tapis. Pour vulgariser, cela revient à se demander quelle variété de pomme utiliser pour différents desserts. Typiquement pour faire une tarte aux pommes, on choisira plutôt la Granny-Smith; on évitera soigneusement la Boskoop et la Canada  plus adaptées à la compote.

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - Teletubbies

Pour commencer, voici quelques différences illustrées par des exemples :

Stockage

Une bonne fois pour toute, une variable table n’est pas stockée en mémoire, pour preuve, démarrons une session dans ProcessMonitor et appliquons deux filtres :

– Process Name = sqlservr.exe
– Operation = WriteFile

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - ProcessMonitor_Filters

Créons une table dans laquelle on injecte 1000 lignes.

1) Quand on lance une recherche sur la localisation physique des pages dans la table @temp, la requête renvoie bien un résultat :

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - phyLocFormat

2) La requête permettant de retourner l’allocation de pages retourne elle aussi des données :

 

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - ResultAlloc

3) Et enfin, la session ProcessMonitor fait bien état d’un processus d’écriture dans la base de données tempdb :

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - ProcessMonitor_Result-1024x193

Transaction

Dans la démo ci-dessous, on utilise une table @temp pour conserver un jeu d’enregistrements qui a fait l’objet d’un rollback, alors que l’ajout de données dans la table #temp a bien été annulé. Cette possibilité de soustraire un jeu de données à un rollback peut être utile à des fins de traçabilité. Vous l’aurez donc compris, une variable table n’est pas transactionnelle.

Cardinalité

Un autre point à prendre en considération pour le choix d’une table temporaire est la volumétrie. Typiquement une variable table ne possède pas de statistiques. Sur un nombre de lignes important, cela devient problématique. Néanmoins, il y a plusieurs manières de forcer l’estimation de cardinalité telle que l’option RECOMPILE :

Cela étant, la méthode RECOMPILE peut s’avérer extrêmement coûteuse en temps d’exécution et CPU, particulièrement au sein d’une fonction scalaire qui sera appelée pour chaque ligne.
Depuis SQL Server 2012 SP2, il est possible d’utiliser une méthode intermédiaire, avec le traceflg 2453 dont la recompilation n’est pas systémique. Reste à savoir que l’estimation de cardinalité n’opère que si le plan associé à la requête n’est pas trivial !

Requête avec un plan trivial

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - TF2453_plan_trivial1_

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - TF2453_plan_exec_trivial1

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - TF2453_plan_exec_info_trivial1

Le nombre de lignes estimées est toujours égal à 1 !

Requête 1 avec un plan full

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - TF2453_plan_full1_

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - TF2453_plan_exec_full1

Le nombre de lignes estimées est réaliste.

Requête 2 avec un plan full

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - TF2453_plan_full2_

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - TF2453_plan_exec_full2

Le nombre de lignes estimées est encore une fois réaliste.

NB : La granularité du traceflag 2453 peut être le batch et l’instance.

https://support.microsoft.com/en-us/help/2952444/fix-poor-performance-when-you-use-table-variables-in-sql-server-2012-o

Recompilation

En plus de la mise à jour de données (INSERT, UPDATE, DELETE) de nature à modifier les statistiques, les tables #temp et ##temp peuvent entraîner la recompilation des procédures stockées, ces tables pouvant faire l’objet d’opérations DDL postérieures à leur création, telle que l’ajout de colonne comme dans l’exemple ci-dessous après l’injection de données :

Avant l’exécution de la procédure stockée, une trace XEvent est créer pour consigner les recompilations. La définition et l’exploitation de la trace est consultable dans cet article : https://www.concatskills.com/2018/01/12/sql-server-compilation-et-recompilation/
Une fois la trace créée, on peut passer à l’exécution de la procédure stockée, 10 fois, comme suit :

La trace XEvent a bien enregistré 10 recompilations dûes à un changement de schéma :

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - result_xevent_recompile

Parallélisme

Nativement, une variable table n’est pas éligible à la parallélisation contrairement aux tables #temp et ##temp. Néanmoins c’est un comportement qu’on peut modifier moyennant le recours à la recompilation ou au traceflag 8649 sur un SELECT uniquement. Dans cet exemple, en premier lieu on injecte une quantité de données conséquente dans la table temporaire #TI.

Dans un second temps, on alimente la variable table @T1 et une table temporaire #T à partir de la table #TI, puis on lance un SELECT sur celles-ci pour observer la parallélisation sur ces deux opérations (INSERT et SELECT), avec différents query hint :

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - parallelisme_@temp

La parallélisation est effective sur les INSERT et SELECT de la table temporaire #T2 (requête 2 et 6). A noter que la parallélisation des INSERT n’est disponible que depuis SQL Server 2014. En revanche, concernant la variable table @T1, la parallélisation n’est effective que sur l’opération de SELECT à l’aide d’un RECOMPILE (on reste néanmoins tributaire du CTP) ou du traceflag 8649 (requête 4 et 5). Cela étant le RECOMPILE et le traceflag restent inopérant sur un INSERT. Plus globalement, l’intérêt du traceflag 8649 réside dans le fait de pouvoir forcer le parallélisme en s’affranchissant de la comparaison des coûts (plans sérialisés VS plans parallélisés) sur une instance où l’on a désactivé cette option par exemple mais ce traceflag requiert un niveau de droit élevé.

Contraintes et indexation

L’ajout de contraintes et d’index sur une variable table ne peut pas être postérieur à sa création. L’indexation n’était possible qu’indirectement, autrement dit à partir de la création de contrainte (ex : PRIMARY KEY, UNIQUE) pour les versions antérieures à SQL Server 2014. Petite nouveauté sur SQL Server 2016, on peut désormais ajouter des index filtrant.

Locking allégé

On ne peut pas parler d’absence de locking sur une variable table. Cela étant, à l’aide du traceflag 1200 qui permet d’afficher les informations sur le locking, la sortie de l’exemple ci-dessous indique qu’il est considérablement réduit.

Sortie :

Logging

Pour ce qui est du logging, il se fait de la même façon que ce soit pour une table temporaire ou une variable table dans le journal de transaction. Pour s’en rendre compte, il suffit d’interroger le fichier de transaction via  la fonction fn_dblog ,après avoir lancer les mêmes mises à jour sur une table temporaire et une variable table. On obtient un nombre/type d’opérations et quantité de log similaires :

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - result_logging

Et la CTE dans tout ça ?

Ce n’est pas une table en sois mais une vue calculée à la volée, non matérialisée et sans metadonnées. L’appel à une CTE peut même faire partie d’une vue contrairement aux tables temporaires ##temp et #temp. Voici quelques exemples d’implémentation d’une CTE :

Mise à jour basée sur une agrégation

Au préalable, création d’une table contenant des montants de commandes associés à des produits : on souhaite remonter sur chaque ligne le résultat d’agrégation à savoir le montant minimum et maximum pour chaque produit.

On pourrait passer par une sous-requête mais le recours à une CTE est aussi valable :

Récursivité

Dans le cas présent, on souhaite générer un calendrier pour l’année en cours :

On peut tout aussi bien générer des incréments comme suit :

Pratique pour la récursivité mais dans certains cas un WHILE sera beaucoup plus performant.

Synthèse

Pour conclure, voici une matrice permettant d’orienter votre choix pour l’utilisation d’une table temporaire, multi-sessions ou pas, d’une variable table ou d’une CTE :

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - excel_comp_temp_tables

Auteur

Expert SQL Server - Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ? - SQL Server  - avatar_ninja_tete-150x150
Sarah Béquet
Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
error: