Quand devrais-je utiliser une variable de table vs une table temporaire dans le serveur SQL?

Question

Je suis en train d'apprendre plus de détails dans le tableau de variables.Il est dit que les tables temporaires sont toujours sur le disque,et les variables de table sont en mémoire,c'est-à-dire que la performance d'une variable de table est mieux que la table temporaire parce que la variable de table utilise moins d'opérations d'e / s de la table temporaire.

Mais parfois, si il y a trop d'enregistrements dans une table de variables qui ne peuvent pas être contenues dans la mémoire,le tableau de la variable sera mis sur le disque comme la table temp.

Mais je ne sais pas ce que le "trop grand nombre de dossiers". 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? Est-il une fonction ou un outil dans sql server 2005 pour mesurer l'ampleur de la variable de table ou de me laisser savoir quand la variable de table est mis sur le disque à partir de la mémoire ?

Answer 1

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

J'ai écrit tout un vaste réponse sur le DBA site de regarder les différences entre les deux types d'objet. Cela permet également de résoudre votre question sur le disque vs mémoire (je n'ai pas vu aucune différence significative de comportement entre les deux).

Concernant la question dans le titre même si, comme pour les cas d'utilisation d'une variable de table vs 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, puis seulement #temp tableau fera (table des paramètres de permettre à accéder en lecture seule).

Où vous avez le choix quelques suggestions ci-dessous.

1. Si vous avez besoin d'un indice qui ne peut pas être créé implicitement par le biais d'un UNIQUE ou PRIMARY KEY contrainte alors vous avez besoin d'un #temporary table, car il n'est pas possible de créer ces sur une table de variables. (Des exemples de tels indices sont non spécifiques, index filtrés ou d'index avec INCLUDEd colonnes). NB: SQL Server 2014 permettra non les index uniques à être déclarées en ligne pour les variables de table.
2. Si vous serez à plusieurs reprises l'ajout et la suppression d'un grand nombre de lignes de la table, puis utiliser un #temporary table. Qui prend en charge TRUNCATE ce qui est plus efficace que l' DELETE , et en outre des insertions ultérieures à la suite d'un TRUNCATE peut avoir de meilleures performances que ceux qui suivent un DELETE comme illustré ci-dessous.
3. Si le plan optimal à l'aide de la table variera selon les données, puis utiliser un #temporary table. Qui prend en charge la création de statistiques qui permet au plan dynamique recompilé selon les données (même si pour la mise en cache des tables temporaires dans les procédures stockées de la recompilation de comportement doit être compris séparément).
4. Si le plan optimal de la requête à l'aide de la table est peu susceptible de changer, vous pouvez alors envisager une variable de table pour ignorer la surcharge de la création de statistiques et recompile (éventuellement exiger des conseils pour fixer le plan que vous voulez).
5. Si la source de l'insertion de données de la table est de potentiellement coûteux SELECT déclaration de considérer, à l'aide d'une variable de table permet de bloquer la possibilité de cette utilisation d'un plan parallèle.
6. Si vous avez besoin des données dans la table pour survivre à une restauration de l'extérieur de transaction de l'utilisateur, puis utiliser une variable de table. Un possible cas d'utilisation de ce pourrait être la journalisation de l'état d'avancement des différentes étapes d'un long traitement SQL.
7. Lors de l'utilisation d'un #temp tableau à l'intérieur d'une transaction utilisateur verrous plus longtemps que pour les variables de table et il peut également empêcher la troncation de l' tempdb journal des transactions jusqu'à ce que l'utilisateur de la transaction se termine. Donc cela pourrait favoriser l'utilisation de la table des variables.
8. Dans les routines stockées à la fois les variables de table et tables temporaires peuvent être mis en cache. Les métadonnées d'entretien pour la mise en cache de la table des variables est inférieure à l' #temporary tables. Bob Ward souligne dans son tempdb présentation que cela peut entraîner une augmentation de la contention sur les tables système dans des conditions de forte concurrence. En outre, lorsque vous traitez avec de petites quantités de données, cela peut faire une différence mesurable de la performance.

Answer 2

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

Utiliser une table temporaire pour un grand nombre de données

Une autre façon de chose à ce sujet: si vous pensez que vous pourriez bénéficier d'un index, automatisé des statistiques, ou de toute optimiseur SQL bonté, puis votre jeu de données est probablement trop grand pour un temporory table.

Dans mon exemple, je voulais juste mettre environ 20 lignes dans un format et de les modifier en tant que groupe, avant de les utiliser pour mettre à JOUR / INSERTION d'une table permanente. Si une variable de table est parfait.

Mais je suis également en cours d'exécution SQL pour remplir des milliers de lignes à la fois, et je peux certainement dire que les tables temporaires effectuer beaucoup mieux que les variables de table.

Ce n'est pas, contrairement à la façon dont CTE sont une préoccupation pour une taille similaire raison - si les données de la CTE est très petite, j'ai trouver une CTE effectue aussi bien ou mieux que ce que l'optimiseur qui vient avec, mais si elle est assez grande, puis il vous fait 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.

Answer 3

Microsoft dit ici

Les variables de Table n'a pas de statistiques de distribution, ils ne vont pas déclencher recompile. Par conséquent, dans de nombreux cas, l'optimiseur va construire un plan de requête sur l'hypothèse que la variable de table n'a pas de ligne. Pour cette raison, vous devez être prudent sur l'utilisation d'une variable de table si vous vous attendez à un plus grand nombre de lignes (plus de 100). Les tables temporaires peut être une meilleure solution dans ce cas.

Answer 4

Je suis totalement d'accord avec Abacus (désolé - n'ont pas suffisamment de points pour le commentaire).

Aussi, gardez à l'esprit qu'il ne doit pas nécessairement venir vers le bas pour combien de dossiers que vous avez, mais la taille de vos dossiers.

Par exemple, avez-vous considéré la différence de performances entre 1 000 dossiers avec 50 colonnes de vs de 100 000 enregistrements avec seulement 5 colonnes de chaque?

Enfin, peut-être vous interrogez/stocker plus d'informations que vous avez besoin? Voici une bonne lecture sur SQL stratégies d'optimisation. Limiter la quantité de données que vous êtes en tirant, surtout si vous ne l'utilisez pas tous (certains programmeurs SQL ne de devenir paresseux et il suffit de sélectionner tout, même si il utilise seulement un petit sous-ensemble). N'oubliez pas de l'analyseur de requêtes SQL peut aussi devenir votre meilleur ami.