Quel est le moyen le plus rapide d'insérer en masse un grand nombre de données dans SQL Server (client C#) ?

Question

Je rencontre des goulots d'étranglement au niveau des performances lorsque mon client C# insère des données en masse dans une base de données SQL Server 2005 et je cherche des moyens d'accélérer le processus.

J'utilise déjà le SqlClient.SqlBulkCopy (qui est basé sur TDS) pour accélérer le transfert de données à travers le fil, ce qui m'a beaucoup aidé, mais je cherche encore 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 en train d'insérer des données en morceaux d'environ 300 lignes en moyenne, où ContainerId et BinId sont constants dans chaque morceau, la valeur de la séquence est 0-n et les valeurs sont pré-triées en fonction de la clé primaire.

Le compteur de performance %Disk time passe beaucoup de temps à 100%, il est donc clair que les entrées-sorties sur disque sont le principal problème, mais les vitesses que j'obtiens sont plusieurs ordres de grandeur en dessous d'une copie de fichier brut.

Est-ce que ça aide si je :

1. Supprimer la clé primaire pendant que je fais l'insertion et la recréer plus tard.
2. Effectuer des insertions dans une table temporaire avec le même schéma et les transférer périodiquement dans la table principale afin de maintenir la taille de la table où les insertions ont lieu à un niveau faible.
3. Autre chose ?

-- Sur la base des réponses que j'ai reçues, permettez-moi de clarifier un peu :

Portman : J'utilise un index clusterisé parce que lorsque les données seront toutes importées, j'aurai besoin d'accéder aux données de manière séquentielle dans cet ordre. Je n'ai pas particulièrement besoin que l'index soit présent pendant l'importation des données. Y a-t-il un avantage à avoir un index PK non clusterisé pendant les insertions plutôt que de laisser tomber la contrainte entièrement pour l'importation ?

Chopeen : Les données sont générées à distance sur de nombreuses autres machines (mon serveur SQL ne peut en gérer qu'une dizaine actuellement, mais j'aimerais pouvoir en ajouter d'autres). Il n'est pas pratique d'exécuter l'ensemble du processus sur la machine locale car il faudrait alors traiter 50 fois plus de données d'entrée pour générer la sortie.

Jason : Je ne fais pas de requêtes simultanées sur la table pendant le processus d'importation, je vais essayer de supprimer la clé primaire et voir si cela aide.

Answer 1

Voici comment vous pouvez désactiver/activer les index dans SQL Server :

--Disable Index ALTER INDEX [IX_Users_UserID] SalesDB.Users DISABLE
GO
--Enable Index ALTER INDEX [IX_Users_UserID] SalesDB.Users REBUILD

Voici quelques ressources pour vous aider à trouver une solution :

Quelques comparaisons de la vitesse de chargement en vrac

Utiliser SqlBulkCopy pour charger rapidement les données de votre client vers le serveur SQL

Optimisation des performances de la copie en masse

Il faut absolument examiner les options NOCHECK et TABLOCK :

Conseils pour les tables (Transact-SQL)

INSERT (Transact-SQL)

Answer 2

Vous utilisez déjà SqlBulkCopy ce qui est un bon début.

Cependant, le fait d'utiliser la classe SqlBulkCopy ne signifie pas nécessairement que SQL effectuera une copie en masse. En particulier, quelques conditions doivent être remplies pour que SQL Server effectue une insertion en masse efficace.

Pour en savoir plus :

• Conditions préalables pour une journalisation minimale dans l'importation en masse
• Optimisation des performances de l'importation en masse

Par curiosité, pourquoi votre index est-il configuré comme ça ? Il semblerait que ContainerId/BinId/Sequence est beaucoup est mieux adapté pour être un indice non groupé. Y a-t-il une raison particulière pour laquelle vous vouliez que cet index soit clusterisé ?

Answer 3

Je pense que vous verrez une amélioration spectaculaire si vous changez cet indice pour qu'il soit non groupé . Cela vous laisse deux options :

1. Changez l'index en non clusterisé, et laissez-le en tant que table de tas, sans index clusterisé.
2. Changez l'index en non clusterisé, mais ajoutez ensuite une clé de substitution (comme "id") et faites-en une identité, une clé primaire et un index clusterisé.

L'un ou l'autre accélérera vos insertions sans ralentissant sensiblement vos lectures.

Pensez-y de cette façon : pour l'instant, vous demandez à SQL d'effectuer une insertion en masse, mais vous demandez à SQL de réorganiser la table entière à chaque fois que vous ajoutez quelque chose. Avec un index non groupé, vous ajoutez les enregistrements dans l'ordre dans lequel ils arrivent, puis vous construisez un index séparé indiquant l'ordre souhaité.

Answer 4

Avez-vous essayé d'utiliser les transactions ?

D'après votre description, le serveur s'engageant à 100% sur le disque, il semble que vous envoyez chaque ligne de données dans une phrase SQL atomique, ce qui oblige le serveur à s'engager (écrire sur le disque) pour chaque ligne.

Si vous utilisiez des transactions à la place, le serveur n'engagerait que une fois à la fin de la transaction.

Pour une aide supplémentaire : Quelle méthode utilisez-vous pour insérer des données dans le serveur ? La mise à jour d'une DataTable à l'aide d'un DataAdapter, ou l'exécution de chaque phrase à l'aide d'une chaîne de caractères ?

Answer 5

BCP - c'est une douleur à mettre en place, mais cela existe depuis l'aube des BD et c'est très très rapide.

À moins que vous n'insériez des données dans cet ordre, l'index en trois parties ralentira vraiment les choses. L'appliquer plus tard ralentira également les choses, mais dans une deuxième étape.

Les clés composées dans Sql sont toujours assez lentes, plus la clé est grande, plus elle est lente.