.Net / ASP.Net / experiences / SQL Server

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.

101001 00010 000100 0001 000 000
InsertRecordsOneByOne49430427442 023> 5minxx
InsertRecordsOneByOneWithTransaction42409406839 943> 5minxx
InsertRecordsByChunks4141451 42814 336> 2min
BulkInsert2714756586 776

Le résultat est sans appel comme vous pouvez le constater.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.