Choisir entre les variables de table et les tables temporaires (ST011, ST012)

Les gens peuvent, et font, beaucoup de discussions sur les mérites relatifs des variables de table et des tables temporaires. Parfois, comme lors de lécriture de fonctions, vous navez pas le choix; mais quand vous le faites, vous constaterez que les deux ont leur utilité, et il est facile de trouver des exemples où l’une ou l’autre est plus rapide. Dans cet article, jexpliquerai les principaux facteurs impliqués dans le choix de lun ou lautre, et je vous montrerai quelques «règles» simples pour obtenir les meilleures performances.

En supposant que vous respectiez les règles de base de lengagement , vous devez alors considérer les variables de table comme premier choix lorsque vous travaillez avec des ensembles de données relativement petits. Ils sont plus faciles à utiliser et déclenchent moins de recompilations dans les routines dans lesquelles ils sont utilisés, par rapport à l’utilisation de tables temporaires. Les variables de table nécessitent également moins de ressources de verrouillage car elles sont «privées» pour le processus et le lot qui les ont créées. SQL Prompt implémente cette recommandation en tant que règle danalyse de code, ST011 – Pensez à utiliser une variable de table au lieu dune table temporaire.

Si vous effectuez un traitement plus complexe sur des données temporaires ou si vous devez utiliser plus que des quantités raisonnablement petites de les données quils contiennent, puis les tables temporaires locales seront probablement un meilleur choix. SQL Code Guard inclut une règle danalyse de code, basée sur sa recommandation, ST012 – Pensez à utiliser une table temporaire au lieu dune variable de table, mais elle nest actuellement pas implémentée dans linvite SQL.

Avantages et inconvénients des variables de table et des tables temporaires

Les variables de table ont tendance à avoir une mauvaise presse, car les requêtes qui les utilisent entraînent parfois des plans dexécution très inefficaces. Cependant, si vous suivez quelques règles simples, elles constituent un bon choix pour les tables intermédiaires «de travail» et pour transmettre les résultats entre les routines, où les ensembles de données sont petits et le traitement requis est relativement simple.

Les variables de table sont très simples à utiliser, principalement parce quelles ne nécessitent aucune maintenance. Elles sont étendues au lot ou à la routine dans laquelle elles sont créées, et sont supprimées automatiquement une fois lexécution terminée, et donc à les utiliser dans une connexion de longue durée ne risque pas de problèmes de «saturation des ressources» dans tempdb. Si une variable de table est déclarée dans une procédure stockée, elle est locale à cette procédure stockée et ne peut pas être référencée dans une procédure imbriquée. Il ny a pas non plus de recompilations basées sur les statistiques pour les variables de table et vous ne pouvez pas ALTER un, donc les routines qui les utilisent ont tendance à entraîner moins de recompilations que celles qui utilisent des tables temporaires. Elles ne sont pas non plus entièrement journalisées, donc leur création et leur remplissage sont plus rapides et nécessite moins despace dans le t journal de rançon. Lorsquelles sont utilisées dans des procédures stockées, il y a moins de conflits sur les tables système, dans des conditions de forte concurrence. En bref, il est plus facile de garder les choses en ordre.

Lorsque vous travaillez avec des ensembles de données relativement petits, ils sont plus rapides que la table temporaire comparable. Cependant, à mesure que le nombre de lignes augmente, au-delà denviron 15 000 lignes, mais variant selon le contexte, vous pouvez rencontrer des difficultés, principalement en raison de leur manque de support pour les statistiques. Même les index qui appliquent les contraintes PRIMARY KEY et UNIQUE aux variables de table ne disposent pas de statistiques . Par conséquent, loptimiseur utilisera une estimation codée en dur de 1 ligne renvoyée par une variable de table, et aura donc tendance à choisir les opérateurs optimaux pour travailler avec de petits ensembles de données (comme lopérateur de boucles imbriquées pour les jointures). Plus il y a de lignes dans la variable de table, plus les écarts entre lestimation et la réalité sont importants, et plus les choix de plan de loptimiseur deviennent inefficaces. Le plan qui en résulte est parfois affreux.

Le développeur expérimenté ou DBA sera à laffût de ce genre de problème, et sera prêt à ajouter le OPTION (RECOMPILE) indice de requête à linstruction qui utilise la variable de table. Lorsque nous soumettons un lot contenant une variable de table, loptimiseur compile dabord le lot à quel point la variable de table est vide. Lorsque le lot commence à sexécuter, lindication provoquera la recompilation de cette seule instruction, à quel point la variable de table sera remplie et loptimiseur peut utiliser le nombre réel de lignes pour compiler un nouveau plan pour cette instruction. Parfois, mais rarement, même cela n’aide pas. De plus, une dépendance excessive à cet indice annulera dans une certaine mesure lavantage que les variables de table ont de provoquer moins de recompilations que les tables temporaires.

Deuxièmement, certaines limitations dindex avec des variables de table deviennent un facteur plus important lors du traitement grands ensembles de données. Bien que vous puissiez désormais utiliser la syntaxe de création dindex en ligne pour créer des index non clusterisés sur une variable de table, il existe certaines restrictions et il ny a toujours pas de statistiques associées.

Même avec un nombre de lignes relativement modeste, vous pouvez rencontrer des problèmes de performances des requêtes si vous essayez dexécuter une requête qui est une jointure et que vous oubliez de définir un PRIMARY KEY ou UNIQUE contrainte sur la colonne que vous utilisez pour la jointure. Sans les métadonnées quils fournissent, loptimiseur na aucune connaissance de lordre logique des données, ni du fait que les données de la colonne de jointure contiennent des valeurs en double, et choisira probablement des opérations de jointure inefficaces, entraînant des requêtes lentes. Si vous travaillez avec un tas de variables de table, vous ne pouvez l’utiliser qu’une liste simple qui est susceptible d’être traitée en une seule gulp (analyse de table). Si vous combinez à la fois lutilisation de lindicateur OPTION (RECOMPILE), pour des estimations de cardinalité précises, et une clé sur la colonne de jointure pour rendre loptimiseur utile métadonnées, alors pour des ensembles de données plus petits, vous pouvez souvent atteindre des vitesses de requête similaires ou supérieures à lutilisation dune table temporaire locale.

Une fois que le nombre de lignes augmente au-delà de la zone de confort dune variable de table, ou vous devez traiter des données plus complexes traitement, il est préférable de passer à l’utilisation de tables temporaires. Ici, vous avez toutes les options à votre disposition pour lindexation, et loptimiseur aura le luxe dutiliser des statistiques pour chacun de ces index. Bien sûr, linconvénient est que les tables temporaires ont un coût de maintenance plus élevé. Vous devez vous assurer de vous éclaircir après vous-même, pour éviter la congestion de tempdb. Si vous modifiez une table temporaire, ou modifiez les données quelle contient, vous risquez de devoir recompiler la routine parente.

Les tables temporaires sont préférables lorsquil y a un besoin dun grand nombre de suppressions et dinsertions (partage de lignes ). Cela est particulièrement vrai si les données doivent être entièrement supprimées de la table, car seules les tables temporaires prennent en charge la troncature. Les compromis dans la conception des variables de table, tels que le manque de statistiques et de recompilations, fonctionnent contre eux si les données sont volatiles.

Quand il est rentable dutiliser des variables de table

Nous Je vais commencer par un exemple où une variable de table est idéale et se traduit par de meilleures performances. Nous produirons une liste demployés pour Adventureworks, dans quel département ils travaillent et les quarts de travail dans lesquels ils travaillent. Nous avons affaire à un petit ensemble de données (291 lignes).

Nous allons placer les résultats dans une deuxième table temporaire, comme si nous transmettions le résultat au lot suivant. Le listing 1 montre le code.

Et voici un résultat typique sur ma machine de test lente:

Lutilisation dune table temporaire est toujours plus lente, même si les exécutions individuelles peuvent varier beaucoup.

Les problèmes déchelle et doubli de fournir une clé ou un indice

Quelles sont les performances si lon joint deux variables de table? Essayons-le. Pour cet exemple, nous avons besoin de deux tableaux simples, lun avec tous les mots courants de la langue anglaise (CommonWords), et lautre avec une liste de tous les mots dans Dracula de Bram Stoker (WordsInDracula). Le téléchargement TestTVsAndTTs inclut le script permettant de créer ces deux tables et de remplir chacune à partir de son fichier texte associé. Il y a 60 000 mots courants, mais Bram Stoker nen a utilisé que 10 000. Le premier est bien en dehors du seuil de rentabilité, où lon commence à préférer les tables temporaires.

Nous utiliserons quatre simples requêtes de jointure externe, testant le résultat pour NULL valeurs, pour découvrir les mots communs qui ne sont pas dans Dracula, les mots communs qui sont dans Dracula, les mots dans Dracula qui ne sont pas communs, et enfin une autre requête pour trouver des mots communs dans Dracula, mais se rejoignant dans la direction opposée. Vous verrez les requêtes sous peu, lorsque jafficherai le code du banc dessai.

Voici les résultats des tests initiaux. Dans la première exécution, les deux variables de table ont des clés primaires, et dans la seconde, elles sont toutes les deux des tas, juste pour voir si jexagère les problèmes de ne pas déclarer un index dans une variable de table. Enfin, nous exécutons les mêmes requêtes avec des tables temporaires. Tous les tests ont été exécutés, délibérément, sur un serveur de développement lent, à des fins dillustration; vous obtiendrez des résultats très différents avec un serveur de production.

Les résultats montrent que lorsque les variables de table sont des tas, vous courez le risque que la requête sexécute pendant dix minutes au lieu de 100 millisecondes. Celles-ci donnent un excellent exemple de la performance horrible que vous pouvez vivre si vous ne connaissez pas les règles. Même lorsque nous utilisons des clés primaires, cependant, le nombre de lignes que nous traitons signifie que lutilisation de tables temporaires est maintenant deux fois plus rapide.

Je ne vais pas approfondir les détails des plans dexécution derrière ces mesures de performance, sauf pour donner quelques explications générales sur les principales différences. Pour les requêtes de la table temporaire, loptimiseur, armé dune connaissance complète de la cardinalité et des métadonnées des contraintes de clé primaire, choisit un opérateur Merge Join efficace pour effectuer lopération de jointure.Pour la variable de tables avec des clés primaires, loptimiseur connaît lordre des lignes dans la colonne de jointure et quelles ne contiennent pas de doublons, mais suppose quil ne traite quune seule ligne et choisit donc à la place une jointure de boucles imbriquées. Ici, il scanne une table, puis pour chaque ligne renvoyée, il effectue des recherches individuelles de lautre table. Cela devient moins efficace à mesure que les ensembles de données sont volumineux et est particulièrement mauvais dans les cas où il scanne la variable de table CommonWords, car il en résulte plus de 60K recherches de la Dracula variable de table. La jointure de boucles imbriquées atteint une «inefficacité maximale» pour deux requêtes de dix minutes utilisant des tas de variables de table, car elle implique des milliers danalyses de table de CommonWords. Il est intéressant de noter que les deux requêtes « Mots communs dans Dracula » fonctionnent beaucoup mieux et cela est dû au fait que, pour ces deux-là, loptimiseur a plutôt choisi une jointure Hash Match.

Dans lensemble, les tables temporaires semblent être le meilleur choix , mais nous navons pas encore fini! Ajoutons le conseil OPTION (RECOMPILE) aux requêtes qui utilisent les variables de table avec les clés primaires, et réexécutez les tests pour ces requêtes, et les requêtes originales en utilisant les tables temporaires. Nous laissons de côté les pauvres tas pour le moment.

Comme vous pouvez le voir, lavantage de performance de la table temporaire disparaît. Armé de correctement le nombre de lignes et les entrées ordonnées, loptimiseur choisit la jointure par fusion bien plus efficace.

Que se passerait-il si vous donniez à ces pauvres tas le OPTION (RECOMPILE) un indice aussi? Enfin, l’histoire change pour eux de sorte que les trois horaires sont beaucoup plus proches.

Fait intéressant, les deux « mots courants de Dracula » interrogent étaient rapide même sur des tas sont maintenant beaucoup plus lents. Armé du bon nombre de lignes, loptimiseur change sa stratégie, mais comme il na toujours aucune des métadonnées utiles disponibles lorsque nous définissons des contraintes et des clés, il fait un mauvais choix. Il analyse le tas de CommonWords puis tente une « agrégation partielle », estimant quil sagrégera de 60 000 lignes à quelques centaines. Il ne sait pas quil ny a pas de doublons, donc en fait, il ne sagrège pas du tout, et lagrégation et la jointure subséquente se déversent dans tempdb.

Le banc dessai

Veuillez noter quil sagit du banc dessai dans sa forme finale affichant des performances à peu près égales pour les trois types de table différents. Vous devrez supprimer les indices OPTION (RECOMPILE) pour revenir à loriginal.

Listing 2

Conclusions

Il ny a rien dimprudence à utiliser des variables de table. Ils donnent de meilleures performances lorsquils sont utilisés aux fins pour lesquelles ils ont été prévus, et ils font leur propre nettoyage. À un certain moment, les compromis qui leur donnent de meilleures performances (ne pas déclencher de recompilations, ne pas fournir de statistiques, pas de retour en arrière, pas de parallélisme) deviennent leur chute.

Souvent, lexpert de SQL Server donnera de sages conseils sur la taille du résultat qui causera des problèmes pour une variable de table. Les résultats que je vous ai montrés dans cet article vous suggéreront que cela simplifie à lextrême les problèmes. Il y a deux facteurs importants: si vous avez un résultat de plus de, disons, 1000 lignes (et ce chiffre dépend du contexte) alors vous devez avoir un PRIMARY KEY ou UNIQUE clé pour toutes les requêtes qui se joignent à une variable de table. À un certain moment, vous devrez également déclencher une recompilation pour obtenir un plan dexécution décent, qui a sa propre surcharge.

Même dans ce cas, les performances peuvent souffrir gravement, surtout si vous effectuez un traitement plus complexe , car loptimiseur na toujours pas accès aux statistiques, et donc aucune connaissance de la sélectivité dun prédicat de requête. Dans de tels cas, vous devrez passer à lutilisation de tables temporaires.

Lectures complémentaires