Performances de bcp / BULK INSERT par rapport aux paramètres de table

Question

Je suis sur le point d'avoir à réécrire un peu vieux code à l'aide de SQL Server BULK INSERT commande parce que le schéma a changé, et je me dis que je devrais peut-être penser à propos du passage à une procédure stockée avec un paramètre TABLE au lieu de cela, mais je me demandais quel effet cela pourrait avoir sur les performances.

Quelques informations de base qui peut aider à expliquer pourquoi je vous pose cette question:

• Les données est fourni via un service web. Le service web écrit un fichier texte dans un dossier partagé sur le serveur de base de données qui à son tour effectue un BULK INSERT. Ce processus a été à l'origine mis en œuvre sur SQL Server 2000, et à l'époque il n'y avait pas vraiment d'autre choix que de serrage à quelques centaines de INSERT des déclarations sur le serveur, ce qui était à l'origine de processus, et il a un rendement de catastrophe.

• Le bloc de données inséré dans une mise en scène permanente de la table et ensuite fusionnées dans une beaucoup plus grande de la table (à la suite duquel il est supprimé de la table intermédiaire).

• La quantité de données à insérer est "grand", mais pas "énorme" - généralement de quelques centaines de lignes, peut-être 5-10k lignes tops dans de rares cas. Donc mon sentiment est qu' BULK INSERT être un non-enregistrés opération ne fera pas que grand une différence (mais bien sûr, je ne suis pas sûr, d'où la question).

• L'insertion est en fait partie d'un grand pipeline processus de traitement par lots et doit se produire de nombreuses fois dans la succession; par conséquent, la performance est critique.

Les raisons, je souhaiterais remplacer l' BULK INSERT avec une TVP sont:

• L'écriture du fichier texte sur NetBIOS est probablement déjà un coût de quelque temps, et c'est assez horrible d'un point de vue architectural.

• Je crois que la mise en scène de la table peut (et doit) être éliminé. La principale raison à cela est que les données insérées doit être utilisé pour un couple de d'autres mises à jour au même moment de l'insertion, et il est beaucoup plus coûteux de tenter la mise à jour de la production massive d'une table qu'il consiste à utiliser un quasi-vide la table intermédiaire. Avec un PVT, le paramètre de coeur est la mise en scène de la table, je peux faire ce que je veux avec elle avant/après l'insertion.

• Je pourrais très bien faire l'écart avec la dupe de vérification, de nettoyage de code, et tous les frais généraux associés à des insertions.

• Pas besoin de s'inquiéter à propos de verrouillage de la mise en scène de la table ou de la base de données tempdb si le serveur est un peu de ces opérations à la fois (nous essayons de l'éviter, mais ça arrive).

Je suis évidemment à ce profil avant de mettre quoi que ce soit dans la production, mais j'ai pensé qu'il pourrait être une bonne idée de demander autour de vous d'abord avant que je passe tout ce temps, voir si quelqu'un a des avertissements sévères à la question sur l'utilisation les paramètres table à cet effet.

- Pour quelqu'un qui est assez cosy avec SQL Server 2008 pour avoir essayé ou au moins étudié ce, quel est le verdict? Pour les insertions de, disons, quelques centaines à quelques milliers de lignes, qui se passe de manière assez fréquente, ne les paramètres table couper la moutarde? Est-il une différence significative dans les performances par rapport à des insertions?

---

Mise à jour: Maintenant, avec 92% du nombre de points d'interrogation!

(ALIAS: Résultats du Test)

Le résultat final est en cours de production, après ce qui ressemble à un 36 à un stade du processus de déploiement. Les deux solutions ont été testées:

• De l'extraction du dossier partagé code et à l'aide de l' SqlBulkCopy classe directement;
• Le passage à une Procédure Stockée avec les paramètres table.

Juste afin que les lecteurs puissent se faire une idée de ce qui exactement a été testé, afin de dissiper tout doute quant à la fiabilité de ces données, voici une explication plus détaillée de ce que ce processus d'importation en fait:

1. Commencez avec une temporelle de la séquence de données qui est habituellement de 20 à 50 points de données (bien qu'il puisse parfois être jusqu'à quelques centaines);

2. Faire tout un tas de fous de traitement sur ce qui est essentiellement indépendante de la base de données. Ce processus est parallélisée, donc environ 8 à 10 de la séquence en (1) sont traitées en même temps. Chaque processus parallèle génère 3 séquences supplémentaires.

3. Prendre tous les 3 séquences et la séquence d'origine et de les combiner dans un lot.

4. Combiner les lots de tous les 8-10 terminé les tâches de traitement dans un grand super-lot.

5. Importer en utilisant soit l' BULK INSERT stratégie (voir étape suivante), ou de la TVP de la stratégie (passez à l'étape 8).

6. Utiliser l' SqlBulkCopy de la classe pour vider l'ensemble de super-lot dans 4 permanent de la mise en scène des tables.

7. Exécuter une Procédure Stockée qui (a) effectue un tas d'agrégation des mesures sur 2 des tableaux, dont plusieurs JOIN conditions, et ensuite (b) effectue une MERGE sur 6 tables de production en utilisant à la fois les données agrégées et des données non agrégées. (Terminé)

   OU

8. Générer de 4 DataTable objets contenant les données à fusionner; 3 d'entre eux contiennent des types CLR qui, malheureusement, ne sont pas correctement pris en charge par ADO.NET les paramètres table, de sorte qu'ils ont à être poussé en tant que représentations de chaîne, qui fait mal aux performances un peu.

9. Nourrir les paramètres table à une Procédure Stockée, qui fait essentiellement le même traitement que (7), mais directement avec le reçu de tables. (Terminé)

Les résultats ont été assez proches, mais la TVP approche finalement obtenu de meilleurs résultats sur la moyenne, même lorsque les données ont dépassé les 1000 lignes par une petite quantité.

Notez que ce processus d'importation est d'exécuter plusieurs milliers de fois à la suite, de sorte qu'il était très facile d'obtenir une moyenne de temps simplement en comptant combien d'heures (oui, heures), il a pris à la fin de la fusion.

À l'origine, un moyen de fusion a pris presque 8 secondes (en vertu de la charge normale). Retrait de la NetBIOS bidouille et de commutation à l' SqlBulkCopy réduit le temps de presque 7 secondes. De commutation pour les paramètres table a encore réduit le temps de 5,2 secondes par lot. C'est une amélioration de 35% de débit pour un processus dont le temps d'exécution est mesurée en heures pas mal du tout. C'est aussi un ~25% d'amélioration sur SqlBulkCopy.

Je suis assez confiant que la véritable amélioration est beaucoup plus que cela. Pendant les tests, il est devenu évident que la dernière fusion n'était plus le chemin critique; au lieu de cela, le Service Web qui a été fait tout le traitement des données commençait à ployer sous le nombre de demandes à venir. Ni le PROCESSEUR ni la base de données I/O étaient vraiment au maximum, et il n'y a pas eu de blocage de l'activité. Dans certains cas, nous avons été voir un écart de quelques secondes d'inactivité entre les fusionne. Il y avait un léger décalage, mais beaucoup plus petite (d'une demie-seconde) lors de l'utilisation d' SqlBulkCopy. Mais je suppose que cela va devenir une histoire pour un autre jour.

Conclusion: les Paramètres de la Table vraiment s'en sortent mieux que d' BULK INSERT des opérations complexes à l'importation+transformer les processus d'exploitation sur le milieu de la taille des ensembles de données.

---

Je voudrais ajouter un autre point, juste pour apaiser toute appréhension sur une partie des gens qui sont pro-mise en scène-tables. D'une certaine manière, l'ensemble de ce service est l'un géant processus de mise en scène. Chaque étape du processus est largement vérifié, donc nous n'avons pas besoin d' une mise en scène de la table pour déterminer pourquoi certains de fusion a échoué (bien qu'en pratique cela n'arrive presque jamais). Tout ce que nous avons à faire est de définir un indicateur de débogage dans le service et il rompra le débogueur ou de vidage de ses données dans un fichier au lieu de la base de données.

En d'autres termes, nous avons déjà plus de suffisamment d'informations sur le processus et n'ont pas besoin de la sécurité d'une mise en scène de la table; la seule raison pour laquelle nous avons eu la mise en scène de la table en premier lieu pour éviter la surcharge sur l'ensemble de l' INSERT et UPDATE des déclarations que nous avons eu à l'utiliser autrement. Dans le processus d'origine, la mise en scène des données vécu dans la table intermédiaire pour quelques fractions de seconde de toute façon, il ajoute pas de valeur dans l'entretien/maintenance des termes.

Notez aussi que nous avons pas remplacé chaque BULK INSERT fonctionnement avec les paramètres table. Plusieurs opérations qui traitent de grandes quantités de données et/ou n'avez pas besoin de faire quelque chose de spécial avec les données autres que les jeter à la DB de toujours utiliser SqlBulkCopy. Je ne suggère pas que les paramètres table sont une performance panacée, seulement qu'ils ont réussi plus de SqlBulkCopy dans ce cas précis impliquant plusieurs transformations entre la stadification initiale et de la dernière fusion.

Donc là vous l'avez. Le Point va à TToni pour trouver les plus pertinents en lien, mais j'apprécie les autres réponses. Merci encore!

Answer 1

Je n'ai pas vraiment d'expérience avec TVP encore, cependant, il ya une belle performance tableau de comparaison vs BULK INSERT dans MSDN ici.

Ils disent que BULK INSERT a le plus de coût de démarrage, mais il est plus rapide par la suite. À un client distant scénario ils tracer la ligne à environ 1000 lignes (pour "simple" serveur logique). À en juger par leur description, je dirais que vous devez être bien avec l'aide de la TVP. L'impact sur les performances - le cas échéant - est probablement négligeable et l'architecture des prestations semblent très bon.

Edit: Sur une note de côté, vous pouvez éviter le serveur de fichier local, et continuer à utiliser la copie en bloc à l'aide de la SqlBulkCopy objet. Juste remplir un DataTable, et l'introduire dans le "WriteToServer"-Méthode d'un SqlBulkCopy instance. Facile à utiliser, et très rapide.

Answer 2

Je pense que je serais encore au bâton avec un bulk insert approche. Vous pouvez constater que la base de données tempdb encore se fait frapper à l'aide d'une TABLE avec un nombre raisonnable de lignes. C'est mon sentiment, je ne peux pas dire que j'ai testé les performances de l'utilisation de la TVP (je me suis intéressé à l'audience d'autres, d'entrée trop bien)

Vous ne mentionnez pas si vous utilisez .NET, mais l'approche que j'ai prises pour optimiser les solutions précédentes, c'était faire d'une masse de données à l'aide de la SqlBulkCopy classe - vous n'avez pas besoin d'écrire les données dans un fichier avant de le charger, il suffit de donner le SqlBulkCopy classe (par exemple) un DataTable - c'est le moyen le plus rapide pour insérer des données dans la DB. 5-10K lignes n'est pas beaucoup, j'ai utilisé ce jusqu'à 750 K lignes. Je soupçonne que, en général, avec quelques centaines de lignes, qu'elle ne ferait pas une grande différence à l'aide d'une TVP. Mais la généralisation de la hausse serait limitée à mon humble avis.

Peut-être la nouvelle FUSION fonctionnalités de SQL 2008 profiterait-il à vous?

Aussi, si votre mise en scène de la table est une table qui est utilisée pour chaque instance de ce processus et que vous êtes inquiet au sujet de la contention, etc, avez-vous envisagé la création d'un nouveau "temporaire", mais la mise en scène de la table à chaque fois, pour ensuite les laisser tomber quand c'est fini?

Remarque vous pouvez optimiser le chargement dans cette mise en scène de la table, en le remplissant sans index. Puis, une fois rempli, ajouter toute indices sur ce point (taux de remplissage=100 pour optimiser les performances de lecture, car à ce moment, il ne sera pas mis à jour).

Answer 3

Mise en scène tables sont bons! Vraiment, je ne voudrais pas faire d'une autre manière. Pourquoi? Parce que l'importation des données peut changer de façon inattendue (Et souvent d'une manière qu'on ne puisse pas prévoir, comme le temps où les colonnes étaient encore appelés prénom et le nom, mais avait le prénom de données dans la colonne nom, par exemple, pour choisir un exemple qui n'est pas au hasard.) Facile de faire des recherches le problème avec une mise en scène de la table de sorte que vous pouvez voir exactement quelles données dans les colonnes de l'importation traitées. Plus difficile à trouver, je pense que lorsque vous utilisez une table en mémoire. Je connais beaucoup de gens qui ne les importations pour vivre comme je le fais et tous d'entre eux vous recommandons d'utiliser les tables intermédiaires. Je soupçonne qu'il ya une raison pour cela.

En outre la fixation d'une petite modification de schéma d'un processus de travail est plus facile et moins de temps que la refonte du processus. Si cela fonctionne et personne n'est disposé à payer pour les heures à le changer, alors seulement corriger ce qui doit être réparé en raison de la modification de schéma. En changeant l'ensemble du processus, vous présenter beaucoup plus de potentiel de nouveaux bugs que par un petit changement à un existant, testé processus de travail.

Et juste comment allez-vous vous faire disparaître toutes les données des tâches de nettoyage? Vous pouvez le faire différemment, mais ils ont encore besoin d'être fait. Encore une fois, changer le processus de la façon dont vous décrivez est très risqué.

Personnellement, il me semble que vous êtes juste offensé en utilisant les techniques les plus anciennes plutôt que d'avoir la chance de jouer avec de nouveaux jouets. Vous semblez avoir aucune base réelle pour vouloir changer les autres que l'instruction bulk insert est donc 2000.