Quand dois-je utiliser une variable table vs table temporaire dans sql server?

j'apprends plus de détails dans la variable de tableau. Il dit que les tables de température sont toujours sur le disque, et les variables de table sont en mémoire, c'est-à-dire, la performance de la variable de table est meilleure que la table de température parce que la variable de table utilise moins D'opérations IO que la table de température.

mais parfois, s'il y a trop d'enregistrements dans une variable de table qui ne peut pas être contenue dans la mémoire, la variable de table sera mise sur disque comme la table de température.

Mais je ne sais pas ce qu'est le "trop de disques". De 100 000 enregistrements? ou de 1 000 000 enregistrements? Comment puis-je savoir si une variable de table que j'utilise est en mémoire ou sur le disque? Y a-t-il une fonction ou un outil dans SQL Server 2005 pour mesurer l'échelle de la variable table ou me faire savoir quand la variable table est placée sur le disque à partir de la mémoire?

240
demandé sur Luke Girvin 2012-08-08 08:46:42

6 réponses

votre question montre que vous avez succombé à certaines des idées fausses communes entourant les variables de table et les tables temporaires.

j'ai écrit réponse assez complète sur le site DBA en regardant les différences entre les deux types d'objet. Cela répond aussi à votre question sur le disque vs la mémoire (je n'ai pas vu de différence significative dans le comportement entre les deux).

au sujet de la question de la titre cependant pour savoir quand utiliser une variable de table par rapport à une table temporaire locale vous n'avez pas toujours le choix. Dans les fonctions, par exemple, il est seulement possible d'utiliser une variable de table et si vous avez besoin d'écrire à la table d'un enfant alors qu'un #temp table ne (les paramètres évalués dans le tableau permettent accès en lecture seule ).

où vous avez un choix quelques suggestions sont ci-dessous (bien que la méthode la plus fiable est de tester simplement les deux avec votre charge de travail spécifique).

  1. si vous avez besoin d'un indice qui ne peut pas être créé implicitement par une contrainte UNIQUE ou PRIMARY KEY alors vous avez besoin d'une table #temporary car il n'est pas possible de créer ces variables sur la table. (Par exemple, des index non uniques, des index filtrés ou des index avec des colonnes INCLUDE d). NB: SQL Server 2014 permettra aux index non uniques d'être déclarés en ligne pour les variables de table.
  2. si vous ajouterez et supprimerez à plusieurs reprises un grand nombre de lignes de la table, puis vous utiliserez une table #temporary . Qui supporte TRUNCATE (qui est plus efficace que DELETE pour les grandes tables) et en outre inserts suivants après un TRUNCATE peut avoir de meilleures performances que ceux qui suivent un DELETE comme illustré ici .
  3. si vous supprimez ou mettez à jour un grand nombre de lignes, alors la table temp peut bien effectuer beaucoup mieux qu'un tableau de variable si elle est en mesure d'utiliser l'ensemble de lignes de partage (voir "Effets de l'ensemble de lignes de partage" ci-dessous pour un exemple).
  4. si le plan optimal utilisant la table varie en fonction des données, utilisez alors une table #temporary . Cela soutient la création de statistiques qui permet au plan d'être dynamiquement recompilé selon les données (bien que pour les tableaux temporaires mis en cache dans les procédures stockées le comportement de recompilation doit être compris séparément).
  5. si le plan optimal pour la requête à l'aide de la table est peu susceptible de changer jamais, alors vous pouvez considérer une variable de table pour sauter les frais généraux de la création de statistiques et recompiler (nécessiterait éventuellement des conseils pour fixer le plan que vous voulez).
  6. si la source des données insérées dans la table provient d'une déclaration SELECT potentiellement coûteuse, alors considérer que l'utilisation d'une variable de table bloquera la possibilité de cette en utilisant un plan parallèle.
  7. si vous avez besoin des données dans la table pour survivre à un rollback d'une transaction d'utilisateur externe puis utiliser une variable de table. Un cas d'utilisation possible pour cela pourrait être la journalisation de la progression de différentes étapes dans un lot SQL long.
  8. lors de l'utilisation d'une table #temp à l'intérieur d'une transaction utilisateur, les serrures peuvent être conservées plus longtemps que pour les variables de table (potentiellement jusqu'à la fin de la transaction vs fin de l'énoncé dépendant du type de serrure et de plus, il peut empêcher la troncature du journal des transactions tempdb jusqu'à la fin de la transaction utilisateur. Cela pourrait donc favoriser l'utilisation de variables de tableau.
  9. dans les routines stockées, les variables de table et les tables temporaires peuvent être mises en cache. La maintenance des métadonnées pour les variables de table en cache est inférieure à celle des tables #temporary . Bob Ward souligne dans son tempdb présentation que cela peut causer des contestation sur les tables de système dans des conditions de haute concurrence. De plus, lorsqu'on traite de petites quantités de données, cela peut faire de une différence mesurable par rapport à la performance .

Effets de l'ensemble de lignes de partage

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T
305
répondu Martin Smith 2018-02-18 15:31:58

utiliser une variable de table si pour une très petite quantité de données (milliers d'octets)

utilisez un table temporaire pour un grand nombre de données

une autre façon d'y penser: si vous pensez que vous pourriez bénéficier d'un indice, de statistiques automatisées, ou de n'importe quelle qualité D'optimiseur SQL, alors votre ensemble de données est probablement trop grand pour une variable de table.

Dans mon exemple, je voulais juste mettre environ 20 lignes dans un format et les modifier en groupe, avant de les utiliser pour mettre à jour / insérer une table permanente. Donc une variable de table est parfaite.

mais je suis aussi en train D'exécuter SQL pour remplir des milliers de lignes à la fois, et je peux certainement dire que les tables temporaires effectuent beaucoup mieux que les variables de table.

cela n'est pas différent de la façon dont CTE sont une préoccupation pour une raison de taille similaire-si les données dans le CTE est très petite, I trouver un CTE fonctionne aussi bien ou mieux que ce que l'optimizer vient avec, mais si elle est assez grande alors il vous blesse mal.

ma compréhension est principalement basée sur http://www.developerfusion.com/article/84397/table-variables-v-temporary-tables-in-sql-server / , qui a beaucoup plus de détails.

58
répondu Abacus 2016-07-06 16:21:38

Microsoft dit ici

variables de Table n'a pas de statistiques de distribution, ils ne déclencheront pas recompilations. Par conséquent, dans de nombreux cas, l'optimiseur construira un plan de requête sur l'hypothèse que la variable table n'a pas de lignes. Pour cette raison, vous devriez être prudent sur l'utilisation d'une variable de tableau si vous vous attendez à un plus grand nombre de lignes (plus de 100). Les tables à température peuvent être une meilleure solution dans ce cas.

28
répondu Paul Sturm 2014-02-26 19:56:40

je suis tout à fait d'accord avec Abacus (désolé - je n'ai pas assez de points à commenter).

aussi, gardez à l'esprit qu'il ne s'agit pas nécessairement de combien dossiers vous avez, mais le taille de vos dossiers.

par exemple, Avez-vous tenu compte de la différence de rendement entre 1 000 enregistrements de 50 colonnes chacun et 100 000 enregistrements de seulement 5 colonnes chacun?

enfin, peut-être vous interrogez/stockez plus de données que vous n'en avez besoin? Voici une bonne lecture sur stratégies D'optimisation SQL . Limitez la quantité de données que vous tirez, surtout si vous ne l'utilisez pas entièrement (certains programmeurs SQL deviennent paresseux et sélectionnent tout simplement même s'ils n'utilisent qu'un petit sous-ensemble). N'oubliez pas que L'analyseur de requêtes SQL peut aussi devenir votre meilleur ami.

12
répondu Do What You Love 2014-07-07 03:24:56

table Variable est disponible uniquement pour la session en cours, par exemple, si vous avez besoin de EXEC une autre procédure stockée dans la procédure en cours, vous devrez passer la table comme Table Valued Parameter et bien sûr cela affectera les performances, avec tables temporaires vous pouvez le faire avec seulement passer le nom de la table temporaire

pour tester une table temporaire:

  • Ouvrir l'éditeur de requête management studio
  • créer une table temporaire
  • ouvrir une autre fenêtre d'éditeur de requête
  • sélectionner dans ce tableau" disponibles

pour tester une table Variable:

  • Ouvrir l'éditeur de requête management studio
  • créer une table Variable
  • Ouvrir une autre fenêtre d'éditeur de requête
  • sélectionner dans ce tableau "non disponible"

autre chose que j'ai expérimenté est: si votre schéma n'a pas GRANT privilège de créer des tables puis utiliser des tables variables. De plus les tables temporaires situées dans la base de données tempdb et les variables de table peuvent être localisées à la fois sur la mémoire et sur le disque de la base de données tempdb. Voir ici: https://www.linkedin.com/pulse/highlighted-differences-between-sql-server-temporary-tables-andrew

3
répondu Mina Gabriel 2016-12-02 13:55:01

en écrivant des données dans les tables déclarées declare @tb et après avoir joint avec d'autres tables, je me suis rendu compte que le temps de réponse par rapport aux tables temporaires tempdb .. # tb est beaucoup plus élevé.

quand je les Rejoins avec @tb le temps est beaucoup plus long pour retourner le résultat, contrairement à #tm , le retour est presque instantané.

j'ai fait des tests avec un 10.000 lignes de jointure et de jointure avec 5 autres tables

2
répondu César Augusto 2017-07-14 15:13:39