Quel est le moyen le plus rapide pour vrac insérer un grand nombre de données dans SQL Server, C# client)
Je suis frapper certains goulots d'étranglement de performances avec mon client C# insertion en bloc de données dans une base de données SQL Server 2005 et je suis à la recherche de moyens pour accélérer le processus.
Je suis déjà à l'aide de la SqlClient.SqlBulkCopy (qui est basé sur TDS) pour accélérer le transfert de données à travers le fil qui m'a beaucoup aidé, mais je suis toujours à la recherche pour plus.
J'ai un tableau simple qui ressemble à ceci:
CREATE TABLE [BulkData](
[ContainerId] [int] NOT NULL,
[BinId] [smallint] NOT NULL,
[Sequence] [smallint] NOT NULL,
[ItemId] [int] NOT NULL,
[Left] [smallint] NOT NULL,
[Top] [smallint] NOT NULL,
[Right] [smallint] NOT NULL,
[Bottom] [smallint] NOT NULL,
CONSTRAINT [PKBulkData] PRIMARY KEY CLUSTERED
(
[ContainerIdId] ASC,
[BinId] ASC,
[Sequence] ASC
))
Je suis d'insertion de données dans des morceaux moyenne d'environ 300 lignes où ContainerId et BinId sont constants dans chaque bloc et la valeur de la Séquence est de 0-n et les valeurs sont pré-triés en fonction de la clé primaire.
Le %de temps du Disque du compteur de performance passe beaucoup de temps à 100% donc c'est clair que l'e /s disque est le principal problème, mais les vitesses que je reçois sont de plusieurs ordres de grandeur au-dessous d'un fichier raw copie.
Il aide tout si j':
- Drop la clé Primaire alors que je suis en train de faire de l'insertion et de le recréer plus tard
- Faire des insertions dans une table temporaire avec le même schéma et périodiquement les transférer dans la table principale de maintenir la taille de la table où les insertions sont en passe de petites
- Autre chose?
--
Sur la base des réponses que j'ai obtenu, permettez-moi de clarifier un peu:
Portman: je suis en utilisant un index cluster, car lorsque les données sont importées, j'ai besoin d'accéder à des données de manière séquentielle dans l'ordre. Je n'ai pas particulièrement besoin de l'index pour être là lors de l'importation des données. Est-il un avantage à avoir un cluster PK index tout en faisant de la insère, par opposition à une chute de la contrainte entièrement pour l'importation?
Chopeen: Les données sont générées à distance sur d'autres machines (my SQL server ne peut traiter environ 10 actuellement, mais j'aimerais être en mesure d'ajouter plus). C'est pas pratique pour exécuter l'ensemble du processus sur la machine locale, car il serait alors à traiter de 50 fois plus de données d'entrée pour générer la sortie.
Jason: je ne fais pas toutes les requêtes simultanées contre la table pendant le processus d'importation, je vais essayez de laisser tomber la clé primaire et voir si cela aide.
Vous devez vous connecter pour publier un commentaire.
Voici comment vous pouvez désactiver/activer l'index dans SQL Server:
Voici quelques ressources pour vous aider à trouver une solution:
Certains en vrac de la vitesse de chargement des comparaisons
Utilisation SqlBulkCopy pour Charger Rapidement les Données à partir de votre Client de SQL Server
Optimiser La Copie En Bloc De La Performance
Vraiment regarder dans NOCHECK et TABLOCK options:
Les indicateurs de Table (Transact-SQL)
INSERT (Transact-SQL)
Vous utilisez déjà SqlBulkCopy, ce qui est un bon début.
Cependant, en utilisant seulement la SqlBulkCopy de classe ne signifie pas nécessairement que SQL va effectuer une copie en bloc. En particulier, il y a quelques exigences qui doivent être remplies pour SQL Server pour effectuer un efficace bulk insert.
Pour en savoir plus:
Par curiosité, pourquoi est votre indice mis en place comme ça? Il semble que ContainerId/BinId/Séquence est beaucoup le mieux adapté à être un index non-cluster. Est-il une raison particulière pour laquelle vous voulais cet index en cluster?
Ma conjecture est que vous verrez une amélioration spectaculaire si vous changez que l'indice de cluster. Cela vous laisse avec deux options:
Soit on augmentera la vitesse de votre insère sans sensiblement ralentir votre lit.
Pensez-y de cette façon-là, maintenant, vous dites SQL pour faire une insertion en bloc, mais alors vous êtes en demandant SQL pour réorganiser l'ensemble de la table chaque table vous ajouter quelque chose. Avec un index non-cluster, vous allez ajouter les enregistrements dans l'ordre qu'ils viennent, et puis de construire un index séparé indiquant leur ordre souhaité.
Avez-vous essayé d'utiliser les transactions?
De ce que vous décrivez, ayant le serveur de commettre 100% du temps sur le disque, il semble que vous êtes de l'envoi de chaque ligne de données dans un atomique SQL phrase ainsi forcer le serveur à s'engager (écriture sur le disque) chaque ligne unique.
Si vous avez utilisé les transactions au lieu de cela, le serveur ne nous engagent qu' une fois à la fin de la transaction.
Pour obtenir de l'aide: Quelle méthode utilisez-vous pour l'insertion de données pour le serveur? Mise à jour d'une Table de données à l'aide d'un DataAdapter, ou de l'exécution de chaque phrase à l'aide d'une chaîne de caractères?
BCP - c'est une douleur à mettre en place, mais il a été autour depuis l'aube de la bd et c'est très très rapide.
Sauf si vous êtes l'insertion de données dans cette ordonnance, la partie 3 de l'indice seront vraiment ralentir les choses. L'application plus tard, il va vraiment ralentir les choses un peu trop, mais sera dans une deuxième étape.
Composé clés dans Sql sont toujours assez lent, plus la clé la plus lente.
Je ne suis pas vraiment un homme intelligent et je n'ai pas beaucoup d'expérience avec le SqlClient.SqlBulkCopy méthode, mais voici mes 2 cents pour ce que ça vaut. J'espère qu'il vous aide et d'autres (ou au moins, amène les gens à l'appel de mon ignorance ;).
Vous n'aurez plus jamais correspondre à un fichier raw vitesse de copie à moins que votre base de données fichier de données (mdf) se trouve sur un autre disque physique à partir de votre fichier de journal de transactions ldf). En outre, les index en cluster devrait également être sur un autre disque physique pour un monde plus juste comparaison.
De votre première copie n'est pas l'exploitation forestière ou le maintien d'un ordre de tri de sélectionner les champs (colonnes) pour l'indexation.
Je suis d'accord avec Portman sur la création d'un cluster de l'identité des semences et de l'évolution de votre index non-cluster à un index cluster.
Aussi loin que de construire que vous utilisez sur les clients...(carte de données, dataset, datatable, etc). Si votre disque e /s sur le serveur est à 100%, je ne pense pas que votre temps est mieux dépensé l'analyse client construit comme ils semblent être plus rapide que le serveur peut actuellement traiter.
Si vous suivez Portman de liens à propos de la journalisation minimale, je ne pense pas que entourant votre copie en bloc dans les transactions aiderait beaucoup si tout, mais j'ai été mal de fois dans ma vie 😉
Ce ne sera pas nécessairement vous aider dès maintenant, mais si vous trouvez votre numéro actuel, ce commentaire suivant peut aider avec la prochaine goulot d'étranglement (le débit du réseau) - surtout si c'est sur Internet...
Chopeen posé une question intéressante aussi. Comment avez-vous déterminer à les utiliser à 300 le nombre d'enregistrement des morceaux à insérer? SQL Server a une taille de paquet par défaut (je crois que c'est de 4096 octets) et il serait logique pour moi de tirer la taille de vos dossiers et de s'assurer que vous êtes en faisant une utilisation efficace de la transmission de paquets entre le client et le serveur. (Notez que vous pouvez modifier votre taille de paquet sur votre code client, par opposition à l'option de serveur qui serait évidemment le modifier pour toutes les communications serveur - probablement pas une bonne idée.) Par exemple, si votre taille d'enregistrement des résultats dans 300 record lots nécessitant 4500 octets, vous devrez envoyer 2 paquets avec le deuxième paquet étant la plupart du temps gaspillé. Si le lot de nombre d'enregistrements a été arbitrairement, il pourrait être judicieux de faire quelques rapides et faciles de mathématiques.
De ce que je peux dire (et rappelez-vous sur le type de données de tailles de), vous avez exactement 20 octets pour chaque enregistrement (si int=4 octets et smallint=2 octets). Si vous êtes à l'aide de 300 le nombre d'enregistrement des lots, puis vous essayez d'envoyer 300 x 20 = de 6 000 octets (en plus je suis deviner un peu de surcharge pour la connexion, etc). Vous pourriez être plus efficace d'envoyer jusqu'à 200 le nombre d'enregistrement des lots (200 x 20 = + de 4000 salle pour les frais généraux) = 1 paquet. Puis de nouveau, le goulot d'étranglement semble toujours être le serveur d'e /s disque.
Je me rends compte que vous comparez un transfert de données brutes à la SqlBulkCopy avec le même matériel/configuration mais ici, c'est là que j'irais aussi, si le défi a été la mienne:
Ce post ne sera probablement pas vous aider plus que c'est un peu vieux mais je voudrais ensuite demander à ce que votre disque de configuration du RAID et de ce que la vitesse de disque que vous utilisez? Essayez de mettre le fichier sur un lecteur qui utilise le RAID 10, RAID 5 (idéalement 1) sur votre fichier de données. Cela peut aider à réduire beaucoup de mouvement broche de différents secteurs sur le disque et plus de temps de lecture/écriture au lieu de la improductives "déplacement" de l'état. Si vous avez déjà séparer vos données et les fichiers journaux, vous avez votre index sur un autre disque physique à partir de votre fichier de données (vous pouvez faire cela seulement avec index en cluster). Qui permettrait de ne pas uniquement simultanément la mise à jour des informations de journalisation avec les données de l'insertion, mais permettrait indice d'insertion (et de toute coûteux page d'index des opérations) à se produire simultanément.
Je pense que cela ressemble à ce qui pourrait être fait à l'aide de Les packages SSIS. Elles sont similaires à SQL 2000 packages DTS. J'ai utilisé à tout transformer de la plaine de texte, fichiers CSV, existant tables SQL, et même à partir de fichiers XLS à 6 chiffres lignes fractionné sur plusieurs feuilles de calcul. Vous pouvez utiliser C# pour transformer les données en un format importable (CSV, XLS, etc), alors votre serveur SQL exécution planifiée de travail SSIS pour importer les données.
Il est assez facile de créer un package SSIS, il y a un assistant intégré dans SQL Server Enterprise Manager outil (appelé "Importer des Données", je crois), et à la fin de l'assistant, il vous donne la possibilité de l'enregistrer dans un package SSIS. Il y a un tas plus d'infos sur Technet ainsi.
Oui, vos idées aidera.
Penchez-vous sur l'option 1 si il n'y a pas de lectures qui se passe pendant que votre chargement.
Penchez-vous sur l'option 2 si vous table de destination est interrogée au cours de votre traitement.
@Andrew
Question. Votre insertion dans des segments de 300. Quel est le montant total de votre insertion? SQL server doit être en mesure de traiter 300 plain old insère très rapide.