La problématique de performance revient dans tous les projets, mais est différente suivant le contexte. Un des axes principaux à travailler pour gagner en performance, mis à part la conception de l’application et l’optimisation logicielle, est la correcte implémentation de la stack data et son impact suivant sa volumétrie.
Nous nous attaquerons dans cet article au sujet suivant : J’ai un besoin régulier d’ingérer de gros volumes de données et ce, en un temps record !
Nous aborderons SqlServer avec le langage C#. Je ne parlerai pas ici de l’implémentation de la stack data via les ORMs.
Modèle de données :
Pour cet article, nous utiliserons un modèle de données simple : Une table User ayant pour clé primaire la colonne Id de type int et étant auto-incrémentée par le SGBD.
Table [dbo].[User]
CREATE TABLE [dbo].[User]( [Id] [int] IDENTITY(1,1) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [CityCode] [nvarchar](50) NOT NULL, [Address] [nvarchar](50) NOT NULL, [Mail] [nvarchar](50) NOT NULL, [Age] [int] NOT NULL, CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
Voici son pendant implémenté côté C# via les deux classes suivantes :
Classe Entity.cs
Cette classe est une classe abstraite et mère des classes d’entités que nous allons utiliser.
Nous attendons que les classes qui en héritent, possèdent une propriété d’identité nommée Id de type int.
Cette clé correspond à la colonne Id de la table [dbo].[User]. Elle est décorée de l’attribut [Key] (System.ComponentModel.DataAnnotations.KeyAttribute)
public abstract class Entity { [Key] public int Id { get; set; } }
Classe User.cs
Cette classe hérite de la classe abstraite Entity et possède donc la propriété Id décorée par l’attribut [Key].
Elle possède en plus un attribut [Table] (System.ComponentModel.DataAnnotations.Schema.TableAttribute) spécifiant la table visée côté SGBD.
[Table("[dbo].[User]")] public class User : Entity { public string LastName { get; set; } public string FirstName { get; set; } public string CityCode { get; set; } public string Address { get; set; } public string Mail { get; set; } public int Age { get; set; } }
/!\ Je suis parti du principe que le nom des colonnes de la table sont identiques à ceux des propriétés de la classe.
Méthodes d’insertion des données
Dans cette section je vous présenterai 4 façons d’insérer des jeux de données avec SqlServer, de la moins performante à la plus performante.
Insertion ligne par ligne
C’est la méthode la plus simple à mettre en place, nous exécutons une requête par objet de la collection.
void InsertRecordsOneByOne(IEnumerable<User> users) { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); string sql = "INSERT INTO [dbo].[User] (LastName, Firstname, CityCode, Address, Mail, Age) VALUES (@lastName, @firstName, @cityCode, @address, @mail, @age)"; using (var cmd = new SqlCommand(sql, connection)) { foreach (var user in users) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@lastName", user.LastName); cmd.Parameters.AddWithValue("@firstName", user.FirstName); cmd.Parameters.AddWithValue("@cityCode", user.CityCode); cmd.Parameters.AddWithValue("@address", user.Address); cmd.Parameters.AddWithValue("@mail", user.Mail); cmd.Parameters.AddWithValue("@age", user.Age); cmd.ExecuteNonQuery(); } } } }
Inconvénients :
– très peu performant avec un grand volume de données
– pas de transaction, donc difficile de contrôler la chaîne d’insertion
– de même une transaction implicite est créée pour chaque requête, ce qui a un coût
– peu sécurisé au niveau du paramétrage de la requête, nous pourrions passer par l’exécution d’une procédure stockée
Insertion ligne par ligne avec transaction
Reprenons le même exemple que précédemment en mettant le tout dans une seule et unique transaction.
void InsertRecordsOneByOneWithTransaction(IEnumerable<User> users) { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); using (var trans = connection.BeginTransaction()) { string sql = "INSERT INTO [dbo].[User] (LastName, Firstname, CityCode, Address, Mail, Age) VALUES (@lastName, @firstName, @cityCode, @address, @mail, @age)"; using (var cmd = new SqlCommand(sql, connection, trans)) { foreach (var user in users) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@lastName", user.LastName); cmd.Parameters.AddWithValue("@firstName", user.FirstName); cmd.Parameters.AddWithValue("@cityCode", user.CityCode); cmd.Parameters.AddWithValue("@address", user.Address); cmd.Parameters.AddWithValue("@mail", user.Mail); cmd.Parameters.AddWithValue("@age", user.Age); cmd.ExecuteNonQuery(); } } trans.Commit(); // Exécution de la transaction } } }
Inconvénients :
– les mêmes que pour l’exemple précédent
Avantages :
– gain de performance si la boucle d’insertion est enveloppée dans une seule et même transaction
– en cas d’erreur, un rollback va s’effectuer sur l’ensemble de la chaîne
– maîtrise de la chaîne d’insertion
Insérer les données par tronçons
Ici, il est question de découper votre collection d’objets en blocs d’une taille définie d’éléments, puis de jouer l’insertion de ces blocs en une transaction.
/!\ La limite du nombre de lignes à insérer en une seule requête est de 1000 et le nombre de paramètres par requête ne doit pas dépasser 2100. Aussi, nous découperons la collection en blocs de 100 éléments.
INSERT INTO TableName(Colum1) VALUES (1), (2), (3), ... jusqu'à 100 lignes.
Fonction d’extension IEnumerable.ChunkData
Cette extension de l’interface IEnumerable a pour but de tronquer une collection en sous-ensembles d’une taille maximale donnée.
static IEnumerable<IEnumerable<T>> ChunkData<T>(this IEnumerable<T> source, int chunkSize) { for (int i = 0; i < source.Count(); i += chunkSize) yield return source.Skip(i).Take(chunkSize); }
Fonction InsertRecordsByChunks
void InsertRecordsByChunks(IEnumerable<User> users, int chunkSize = 100) { // Définition des tronçons d'éléments var chunks = users.ChunkData(chunkSize); using (var connection = new SqlConnection(_connectionString)) { connection.Open(); using (var trans = connection.BeginTransaction()) { // Pour chaque tronçon foreach (var chunk in chunks) { // Construction de la requête et de ses 100 lignes de valeur string sql = "INSERT INTO [dbo].[User] (LastName, Firstname, CityCode, Address, Mail, Age) VALUES "; for (var i = 0; i < chunk.Count(); i++) sql += String.Format($"{(i > 0 ? "," : string.Empty)}(@lastName{i}, @firstName{i}, @cityCode{i}, @address{i}, @mail{i}, @age{i})"); using (var cmd = new SqlCommand(sql, connection, trans)) { // Pour chaque élément du tronçon for (var i = 0; i < chunk.Count(); i++) { var user = chunk.ElementAt(i); cmd.Parameters.AddWithValue($"@lastName{i}", user.LastName); cmd.Parameters.AddWithValue($"@firstName{i}", user.FirstName); cmd.Parameters.AddWithValue($"@cityCode{i}", user.CityCode); cmd.Parameters.AddWithValue($"@address{i}", user.Address); cmd.Parameters.AddWithValue($"@mail{i}", user.Mail); cmd.Parameters.AddWithValue($"@age{i}", user.Age); } cmd.ExecuteNonQuery(); } } trans.Commit(); } } }
Inconvénients :
– nécessité de découper la collection en sous-ensembles de maximum 1000 éléments
– attention au nombre de paramètres maximum à gérer. J’ai ici 6 colonnes et des tronçons de 100 éléments donc 600 paramètres
Avantages :
– énorme gain de performance
Insérer les données via SqlBulkCopy (la meilleure solution)
C’est ici et de loin la meilleure solution en termes de performance. Elle fait intervenir la notion de DataTable dans laquelle nous pouvons renseigner n’importe quel type de source de données (ex: collection d’objets, XML, CSV etc…).
J’ai optimisé le code pour qu’il soit générique et puisse s’adapter à tout type de classe d’objet héritant de la classe abstraite Entity.
Fonction GetTableName
Cette fonction permet d’obtenir le nom de la table rattachée à une classe via son attribut [Table]. Pour User.cs, le TableAttribute est [dbo].[User].
string GetTableName<T>() { var tableAttribute = typeof(T).GetCustomAttribute<TableAttribute>(); // System.ComponentModel.DataAnnotations.Schema.TableAttribute if (tableAttribute == null) throw new ArgumentException($"The given entity type {typeof(T).FullName} must declare a TableAttribute to specify the target table"); return tableAttribute.Name; }
Fonction ConvertToDataTable
Cette fonction permet de retourner un DataTable depuis n’importe quelle collection d’objet héritant de la classe abstraite Entity. Elle utilise les Generics et la Reflection, ce qui permet de s’affranchir du type de classe utilisée et du paramétrage des types des propriétés de cette même classe.
DataTable ConvertToDataTable<T>(IEnumerable<T> entities) where T : Entity { var table = new DataTable(); var properties = TypeDescriptor.GetProperties(typeof(T)); // Définition de la structure du DataTable. Nous itérons sur chaque propriété de la class de type T foreach (PropertyDescriptor prop in properties) { // Je ne cherche à ajouter au DataTable que les colonnes qui ne sont pas des clés afin de garder l'auto-incrémentation gérée par le SGBD pour chaque lignes if (!prop.Attributes.OfType<KeyAttribute>().Any()) // Ajout au datatable de la colonne correspondant à la propriété table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); } // Ajout des lignes de données au DataTable foreach (T entity in entities) { var row = table.NewRow(); foreach (PropertyDescriptor prop in properties) { // Même mécanisme ici nous ne voulons pas toucher à la clé primaire if (!prop.Attributes.OfType<KeyAttribute>().Any()) row[prop.Name] = prop.GetValue(entity) ?? DBNull.Value; } table.Rows.Add(row); } return table; }
Fonction BulkInsert
Cette fonction ins les données par batchs suivant un paramétrage qu’il est possible de définir.
void BulkInsert<T>(IEnumerable<T> entities, int batchSize = 5000) where T : Entity { // Récupération du DataTable depuis la source de données var datatable = ConvertToDataTable(entities); var tableName = GetTableName<T>(); using (var connection = new SqlConnection(_connectionString)) { connection.Open(); using (var trans = connection.BeginTransaction()) { using (var bulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, trans)) { // Récupération de la table associée à la classe de type T bulk.DestinationTableName = tableName; bulk.BatchSize = batchSize; bulk.BulkCopyTimeout = 60; /* Mapping des colonnes. Important car nous voulons nous baser sur le nom des colonnes et non pas via un ordinal. Utile quand les colonnes ne sont pas forcément dans le bon ordre ou que l'on ne veut pas traiter la colonne de clé primaire. */ datatable.Columns.Cast<DataColumn>().ToList().ForEach(c => bulk.ColumnMappings.Add(c.ColumnName, c.ColumnName)); bulk.WriteToServer(datatable); } trans.Commit(); } } }
SqlBulkCopyOptions :
Cette énumération influera sur l’exécution du SqlBulkCopy et est à prendre en considération suivant les cas d’utilisation. Voir la Documentation Microsoft.
Il est possible de chaîner les options :
using (var bulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.TableLock, trans))
BatchSize :
C’est un paramètre qui peut influer fortement sur les performances du batch et de la quantité de mémoire utilisée sur la machine. A vous de trouver le valeur adéquate. Cette option détermine la taille d’un lot à traiter.
Inconvénients :
– mis en place (encore que…)
Avantages :
– un maximum de performance
– sécurité du paramétrage
– malléable
– générique
Place au benchmark
Configuration de test
– i5-9600k 3.7GHz, 16Go de RAM
– Windows 10 Professionnel 64bits
– Visual Studio 2019
– SQLServer 2019
– Instance SQLEXPRESS exécutée en local sur la machine
– chaque fonction est testée (différence entre heure avant et après exécution) 5 fois d’affilé et une moyenne en millisecondes est dégagée
Résultats
Comparatif des 4 méthodes testées en millisecondes pour un jeu de données allant de 100 à 1 000 000 d’éléments.
10 | 100 | 1 000 | 10 000 | 100 000 | 1 000 000 | |
InsertRecordsOneByOne | 49 | 430 | 4274 | 42 023 | > 5min | xx |
InsertRecordsOneByOneWithTransaction | 42 | 409 | 4068 | 39 943 | > 5min | xx |
InsertRecordsByChunks | 4 | 14 | 145 | 1 428 | 14 336 | > 2min |
BulkInsert | 2 | 7 | 14 | 75 | 658 | 6 776 |
Le résultat est sans appel comme vous pouvez le constater.