je suis sur le point de devoir réécrire un code plutôt ancien en utilisant la commande BULK INSERT de SQL Server parce que le schéma a changé, et il m'est venu à l'esprit que peut-être je devrais penser à passer à une procédure stockée avec un TVP à la place, mais je me demande quel effet cela pourrait avoir sur les performances.

quelques renseignements généraux qui pourraient aider à expliquer pourquoi je pose cette question:

• 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 vraiment pas d'autre alternative que de découper quelques centaines de INSERT déclarations sur le serveur, qui était en fait le processus original et était un désastre de performance.

• les données sont insérées en vrac dans un table de staging et ensuite fusionné dans une table beaucoup plus grande (après quoi il est supprimé de la table de staging).

• la quantité de données à insérer est" grande", mais pas" énorme " - habituellement quelques centaines de lignes, peut-être 5-10K lignes en haut dans de rares cas. Par conséquent, mon intuition est que BULK INSERT étant une opération non-enregistrée ne fera pas que grande différence (mais bien sûr, je ne suis pas sûr, d'où la question).

• l'insertion fait en fait partie d'un procédé par lots pipeliné beaucoup plus important et doit se produire plusieurs fois de suite; par conséquent, la performance est critique.

les raisons pour lesquelles je voudrais remplacer le BULK INSERT par un TVP sont:

• écrire le fichier texte sur NetBIOS coûte probablement déjà un certain temps, et il est assez horrible à partir 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 pour laquelle il est là est que les données insérées doit être utilisé pour un couple d'autres mises à jour en même temps de l'insertion, et il est beaucoup plus coûteux de tenter la mise à jour à partir de la table de production massive qu'il est d'utiliser une table de staging presque vide. Avec une TVP, le paramètre essentiellement est le 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 me débarrasser de la vérification du dupe, du code de nettoyage, et de tous les frais généraux associés aux encarts en vrac.

• pas besoin de s'inquiéter de lock contention sur la table de staging ou tempdb si le serveur obtient quelques-unes de ces transactions à la fois (nous essayons de l'éviter, mais cela arrive).

je vais évidemment établir le profil avant de mettre quoi que ce soit en production, mais j'ai pensé que ce pourrait être une bonne idée de demander autour d'abord avant que je passe tout ce temps, voir si quelqu'un a des avertissements sévères à émettre sur l'utilisation de TVPs à cette fin.

donc - pour quiconque est assez à l'aise avec SQL Server 2008 pour avoir essayé ou au moins enquêté là-dessus, Quel est le verdict? Pour les insertions de, disons, quelques centaines à quelques milliers de lignes, qui se passe assez souvent, les TVP coupent-ils la moutarde? Est-il une différence significative dans les performances par rapport à des insertions?

---

mise à jour: maintenant avec 92% moins de points d'interrogation!

(alias: résultats des tests)

le résultat final est maintenant en production après ce qui ressemble à un processus de déploiement en 36 étapes. Les deux solutions ont été longuement testées:

• arrachant le code du dossier partagé et utilisant directement la classe SqlBulkCopy ;
• passe à une procédure stockée avec TVPs.

juste pour que les lecteurs puissent avoir une idée de ce que exactement a été testé, pour dissiper tout doute quant à la fiabilité de ces données, Voici une explication plus détaillée de ce que ce processus d'importation fait réellement :

1. commencer par une séquence temporelle de données qui est habituellement d'environ 20-50 points de données (bien qu'elle puisse parfois être de quelques centaines);

2. font un tas de traitement fou sur elle qui est la plupart du temps indépendant de la base de données. Ce processus est parallélisé, de sorte qu'environ 8-10 des séquences dans (1) sont traitées en même temps. Chaque processus parallèle génère 3 séquences supplémentaires.

3. prendre les 3 séquences et la séquence originale et les combiner dans un lot.

4. combinez les lots des 8-10 tâches de traitement maintenant terminées en un grand super-lot.

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

6. utilisez la classe SqlBulkCopy pour décharger la totalité super-lot en 4 tables de staging permanentes.

7. exécuter une procédure stockée qui (a) effectue un ensemble d'étapes d'agrégation sur 2 des tables, y compris plusieurs conditions JOIN , et (b) effectue ensuite un MERGE sur 6 tables de production en utilisant à la fois les données agrégées et 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 ne sont malheureusement pas correctement supportés par ADO.NET les TVP, donc ils doivent être introduits comme des représentations de cordes, ce qui nuit un peu à la performance.

9. alimenter les TVP à un procédé stocké, qui fait essentiellement le même traitement que (7), mais directement avec les tableaux reçus. (Fini)

les résultats étaient raisonnablement proches, mais l'approche TVP s'est finalement avérée plus efficace en moyenne, même lorsque les données dépassaient de peu les 1000 lignes.

notez que ce processus d'importation est exécuté plusieurs milliers de fois de suite, il était donc très facile d'obtenir un temps moyen simplement en comptant combien d'heures (Oui, heures) il a fallu pour terminer toutes les fusions.

à l'origine, une fusion moyenne a pris presque exactement 8 secondes (sous charge normale). La suppression du NetBIOS kludge et le passage à SqlBulkCopy ont réduit le temps à presque exactement 7 secondes. Le passage à la TVPs a réduit encore le temps à 5,2 secondes par lot. C'est une amélioration de 35% en débit pour un procédé dont la durée de fonctionnement est mesurée en heures - donc pas mal du tout. C'est aussi une amélioration d'environ 25% par rapport à SqlBulkCopy .

je suis en fait assez confiant que le véritable amélioration est beaucoup plus que cela. Au cours des essais, il est devenu évident que la fusion finale n'était plus le chemin critique; au lieu de cela, le Service Web qui faisait tout le traitement des données commençait à céder sous le nombre de demandes reçues. Ni le CPU ni l'E/S de la base de données n'étaient vraiment au maximum, et il n'y avait pas d'activité de verrouillage importante. Dans certains cas, nous avons observé un écart de quelques secondes inactives entre les fusions successives. Il y avait un léger écart, mais beaucoup plus petit (une demi-seconde environ) en utilisant SqlBulkCopy . Mais je suppose que cela deviendra une histoire pour un autre jour.

Conclusion: les Paramètres de la Table vraiment faire mieux que les BULK INSERT opérations complexes d'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 de la part des gens qui sont pro-mise en scène-tables. D'une certaine manière, tout ce service est un gigantesque processus de mise en scène. Chaque étape du processus est lourdement audité, donc nous ne besoin une table d'étape pour déterminer pourquoi une fusion particulière a échoué (bien que dans la pratique, il se produit presque jamais). Tout ce que nous avons à faire est de mettre un drapeau de débogage dans le service et il va casser au débogueur ou balancer ses données dans un fichier au lieu de la base de données.

En d'autres termes, nous avons déjà plus de assez de perspicacité dans le processus et n'ont pas besoin de la sécurité d'une table de staging; la seule raison pour laquelle nous avions la table de staging en premier lieu était d'éviter de frapper sur toutes les déclarations INSERT et UPDATE que nous aurions dû utiliser autrement. Dans le processus original, les données de stadification ne vivaient dans la table de stadification que pour des fractions d'une seconde de toute façon, de sorte qu'elles n'ajoutaient aucune valeur en termes de maintenance/maintenabilité.

aussi noter que nous avons Non remplace chaque opération BULK INSERT par des TVP. Plusieurs opérations qui traitent de plus grandes quantités de données et/ou n'ont pas besoin de faire quoi que ce soit de spécial avec les données autres que de le jeter à la DB utilisent encore SqlBulkCopy . Je ne dis pas que les TVP sont une panacée de la performance, mais seulement qu'ils ont réussi plus de SqlBulkCopy dans ce cas précis impliquant plusieurs transformations entre la mise en scène initiale et la fusion finale.

Alors voilà. Le Point revient à TToni pour trouver le lien le plus pertinent, mais j'apprécie également les autres réponses. Merci encore!