Accueil > .Net, C#, Développement, Sql Server > [C#] Bulk Insert

[C#] Bulk Insert

Voici la problématique que j’ai eu récemment : pour mes tests automatisés, j’ai besoin d’insérer des données.
Jusque là, tout va bien.
Pour des tests de charge, j’ai besoin de beaucoup de données.

Sur le ring, j’ai donc :
Côté droit, ma table, près de 70.000.000 de lignes, plus de 80 colonnes.
Côté gauche, mes données, presque 5.000 lignes à insérer.

A la base, mon moteur de gestion de base de données gère les insertions ligne par ligne (il n’a pas été fait, à l’origine, pour du test de charge).
Du coup, pour plusieurs lignes, c’est géré via une basique boucle.
Mais voilà, avec cette méthode, j’ai un rythme d’environ 500 lignes par minute.
Pas terrible, terrible…

Alors, comment faire ?
Le Bulk Insert à la rescousse !

 

Déjà, qu’est ce que le Bulk Insert ?

 

Le Bulk Insert est, à l’origine, une manière, en Transact-SQL d’inserer en masse des données.
Il se base sur l’instruction « BULK INSERT » et prend en paramètre un fichier texte plat avec la liste des valeurs séparées par un séparateur défini.
Il peut également prendre en paramètre un fichier descriptif qui permet de faire l’association entre les données du fichier plat et les colonnes dans la base de données.

C’est donc du chargement par batch.
Pour plus d’informations, je vous invite à vous rendre sur la MSDN : BULK INSERT (Transact-SQL).

 

Et en C# ?

 

Après tout, mes données étant générées par du C#, ça serait assez bête de créer un fichier plat, un fichier descripteur pour ensuite tout balancer à SQL Server via l’instruction BULK INSERT.

Alors, est-ce qu’il y a l’équivalent en C# ?
Oui !

La classe dédié est SqlBulkCopy, disponible dans le namespace System.Data.SqlClient (apportée par le Framework 2.0, donc ce n’est pas nouveau, mais plus facilement méconnu).
Pour voir ce qui compose cette classe, je vous invite à vous rendre également sur la MSDN : SqlBulkCopy Class.

Pour ce qui est de l’utilisation, maintenant.
Déjà, il y a plusieurs « limites » :

  1. La première étant qu’il faut travailler sur une DataTable, un tableau de DataRow ou un IDataReader.
  2. Ensuite, et c’est certainement le point le plus important, si la DataTable ne contient pas le même nombre de colonnes que la table de destination ou dans un ordre différent, alors il faut spécifier le mapping. En effet, le mapping des colonnes sera fera de façon ordinale (c’est à dire en utilisant les index respectifs de la DataTable et de la table de destination).

Pour l’utilisation, il faut d’abord construire l’objet :

SqlBulkCopy bulkCopy = new SqlBulkCopy(
    this.Connexion, // Connexion à la base de données
    SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers,
    this.Transaction); // Transaction, si besoin

Comme c’est une méthode interne à mon moteur de gestion de base, this.Connexion représente un objet SqlConnection et this.Transaction un objet SqlTransaction (qui peut être null).
Les options, second paramètre, permettent de spécifier les « règles » à respecter, du genre s’il y a des triggers sur la table : les déclencher (ou pas, c’est selon).

Pour ce qui est du mapping des colonnes (important dans le cas où la DataTable ne spécifie pas les colonnes Identity ou Compute), il suffit d’utiliser la collection ColumnMappings, comme ceci :

bulkCopy.ColumnMappings.Add("DataTableColumn", "DatabaseColumn");

Et le plus dur, l’insertion :

bulkCopy.DestinationTableName = typeof(T).Name;
DataTable table = ObjectBase<T>.DataToDataTable(data, bulkCopy.ColumnMappings);
bulkCopy.WriteToServer(table);

La méthode ObjectBase.DataToDataTable(List<T>, SqlBulkCopyColumnMappingCollection) s’occupe de faire plusieurs choses :

  • Création d’une DataTable vide (uniquement avec la description des colonnes).
  • Mapping des colonnes DataTable – table.
  • Insertion des données dans la DataTable

Je ne la présente pas ici, car non pertinente.

Au final, donc, rien de bien compliqué.

Et en terme de performances ?
Voici les résultats :

10 lignes, 85 ms (8,5 ms / ligne).
100 lignes, 235 ms (2,35 ms / ligne).
1000 lignes, 791 ms (0,791 ms / ligne).
25.000 lignes, 9.551 ms (0,38204 ms / ligne).
50.000 lignes, 18.327 ms (0,36654 ms / ligne).

Comme on peut le voir, plus il y a de lignes, plus ça va vite !
A comparer avec le 500 lignes / minute du début (7.200 ms / ligne).
Ça laisse rêveur, ce genre d’optimisation😀

Pour des informations additionnelles, je vous conseille la lecture de ce billet : Bulk Insert into SQL from C# App.

Bon, maintenant que j’ai insérer tout ça, va falloir faire le ménage…

Catégories :.Net, C#, Développement, Sql Server
  1. Aucun commentaire pour l’instant.
  1. 01/12/2013 à 00:23
  2. 14/01/2014 à 20:49

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

%d blogueurs aiment cette page :