oct. 14 2010

[Performance] LinqToSql vs SqlCommand vs SqlBulkCopy

Category: .NET 4.0 | Linq To SqlNicolas Esprit @ 18:00

J'ai lu il y a quelques jours le billet de Philippe Vialatte sur "l'amateurisme" de certains développeurs. Je ne vais pas me lancer ici dans le débat. Par contre j'en profite pour faire un petit rappel en rapport avec ma série de billet en cours sur l'amélioration des performances d'applications ASP.NET. Je voudrais parler aujourd'hui des performances lors de l'insertion de données dans une base et rappeller que, même si c'est bien pratique, LinqToSql n'est pas la méthode la plus performante, loin de là. Je vais donc comparer dans ce billet les performances de LinqToSql vs SqlCommand Vs SqlBulkCopy.

Prenons un exemple simple : l'insertion en masse de lignes dans une table. La table se nomme EntityBidon et possède 5 colonnes : une clé incrémentielle, un champ numérique et 3 champ alphanumériques. Pour ce faire, j'utilise cette méthode pour l'insertion avec LinqToSql et plus précisément la méthode InsertOnSubmit

public static void TestLinqInsertOnSubmit(int occurences)
{
using (DataBaseBidonContextDataContext dbContext = new DataBaseBidonContextDataContext(connectionString))
{
Stopwatch watch = Stopwatch.StartNew();
for (int i = 0; i < occurences; i++)
{
EntityBidon e = new EntityBidon
{
BidonInt = i,
BidonString1 = "String1 " + i,
BidonString2 = "String2 " + i,
BidonString3 = "String3 " + i
};
dbContext.EntityBidons.InsertOnSubmit(e);
}
dbContext.SubmitChanges();
Console.WriteLine("Linq InsertOnSubmit : {0}", watch.Elapsed);
}
}

 

Ensuite une deuxième méthode, j'utilise cette fois la méthode InsertAllOnSubmit de LinqToSql :

public static void TestLinqInsertAllOnSubmit(int occurences)
{
using (DataBaseBidonContextDataContext dbContext = new DataBaseBidonContextDataContext(connectionString))
{
Stopwatch watch = Stopwatch.StartNew();
List<EntityBidon> lstToInsert = new List<EntityBidon>();

for (int i = 0; i < occurences; i++)
{
lstToInsert.Add(new EntityBidon
{
BidonInt = i,
BidonString1 = "String1 " + i,
BidonString2 = "String2 " + i,
BidonString3 = "String3 " + i
});
}

dbContext.EntityBidons.InsertAllOnSubmit(lstToInsert);
dbContext.SubmitChanges();
Console.WriteLine("Linq InsertAllOnSubmit : {0}", watch.Elapsed);
}
}

 

Une troisième méthode pour utiliser une simple requête SQL Insert avec une SqlCommand :

public static void TestSqlCommand(int occurences)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
Stopwatch watch = Stopwatch.StartNew();

using (SqlCommand comm = new SqlCommand(string.Empty,conn))
{
for (int i = 0; i < occurences; i++)
{
comm.CommandText = "INSERT INTO EntityBidon (BidonInt, BidonString1, BidonString2, BidonString3) "
+ "VALUES ('" + i + "', 'String1 " + i + "', 'String2 " + i + "', 'String3 " + i + "')";
comm.ExecuteNonQuery();
}
}

Console.WriteLine("SqlCommand : {0}", watch.Elapsed);
}
}

Enfin, une dernière méthode à tester mais cette fois avec le SqlBulkCopy :

public static void TestSqlBulkCopy(int occurences)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
Stopwatch watch = Stopwatch.StartNew();

using (SqlBulkCopy sbc = new SqlBulkCopy(conn))
{
sbc.DestinationTableName = "EntityBidon";
sbc.ColumnMappings.Add("BidonInt", "BidonInt");
sbc.ColumnMappings.Add("BidonString1", "BidonString1");
sbc.ColumnMappings.Add("BidonString2", "BidonString2");
sbc.ColumnMappings.Add("BidonString3", "BidonString3");

using (DataTable table = new DataTable())
{
table.Columns.Add("BidonInt");
table.Columns.Add("BidonString1");
table.Columns.Add("BidonString2");
table.Columns.Add("BidonString3");

for (int i = 0; i < occurences; i++)
{
DataRow row = table.NewRow();
row["BidonInt"] = i;
row["BidonString1"] = "String1 " + i;
row["BidonString2"] = "String2 " + i;
row["BidonString3"] = "String3 " + i;
table.Rows.Add(row);
}

sbc.WriteToServer(table);
}
}

Console.WriteLine("SqlBulkCopy : {0}", watch.Elapsed);
}
}

 

Au passage une petite méthode pour purger la table afin de ne pas fausser les tests :

public static void TruncateTable()
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand("Truncate Table EntityBidon", conn);
comm.ExecuteNonQuery();
}
}

 

La méthode pour lancer nos tests :

public static void LaunchTest(int occurences)
{
Console.WriteLine(string.Format("Début du test avec {0} insertions", occurences));

TestLinqInsertOnSubmit(occurences);
TruncateTable();

TestLinqInsertAllOnSubmit(occurences);
TruncateTable();

TestSqlCommand(occurences);
TruncateTable();

TestSqlBulkCopy(occurences);
TruncateTable();

Console.WriteLine(string.Empty);
}

 

Enfin, nous lancerons ici une série de tests, d'abord pour 100 lignes à insérer, pour arriver à 1.000.000 de lignes.

static string connectionString = "Data Source=(local);Initial Catalog=DataBaseBidon;Integrated Security=True";

static void Main(string[] args)
{
LaunchTest(100);
LaunchTest(1000);
LaunchTest(10000);
LaunchTest(100000);
LaunchTest(1000000);
}

 

Le résultat est clair. Pour 100 lignes :

Linq InsertOnSubmit : 00:00:00.2084424
Linq InsertAllOnSubmit : 00:00:00.0772654
SqlCommand : 00:00:00.0368524
SqlBulkCopy : 00:00:00.0119051

Pour 1.000 insertions :

Linq InsertOnSubmit : 00:00:00.9968116
Linq InsertAllOnSubmit : 00:00:00.7329576
SqlCommand : 00:00:00.3691226
SqlBulkCopy : 00:00:00.0245087

Pour 10.000 insertions :

Linq InsertOnSubmit : 00:00:07.3503769
Linq InsertAllOnSubmit : 00:00:07.4846280
SqlCommand : 00:00:04.4195024
SqlBulkCopy : 00:00:00.2172323

Pour 100.000 insertions:

Linq InsertOnSubmit : 00:01:19.3651855
Linq InsertAllOnSubmit : 00:01:22.3192992
SqlCommand : 00:00:38.2850719
SqlBulkCopy : 00:00:02.6105147

Et enfin pour 1.000.000 insertions:

Linq InsertOnSubmit : 00:14:42.1455606
Linq InsertAllOnSubmit : 00:15:57.7896931
SqlCommand : 00:07:10.3268784
SqlBulkCopy : 00:00:35.6539292

Pour résumer, pensez à utiliser ou vous renseigner sur les bonnes méthodes pour les bons problèmes. Prenons le cas d'un développeur qui ne voit pas plus loin que le bout de son nez : avec LinqToSql et un appel à InsertOnSubmit pour chaque ligne, l'insertion d'un million de lignes prendrait 15min, alors qu'avec un simple SqlBulkCopy... 35 secondes.

Si vous le désirez je peux fournir le code source de l'exemple.

Tags: , , , , ,

Commentaires

1.
pingback topsy.com says:

Pingback from topsy.com

Twitter Trackbacks for
        
        [Performance] LinqToSql vs SqlCommand vs SqlBulkCopy
        [nicolasesprit.com]
        on Topsy.com

2.
Olivier Olivier France says:

Cela serrait intéressant de voir ce que donne Entity Framework / LinqToEntities ?

3.
Nicolas Esprit Nicolas Esprit France says:

Je n'ai pas pris la peine de regarder effectivement. Je testerais à l'occasion.
Cependant, il faut s'attendre à des performances semblables à LinqToSql.

Dans tous les cas, rien ne vaut un SqlBulkCopy

4.
jycouet jycouet France says:

Effectivement, pas mal !

Le Entity Framework / LinqToEntities doit etre à regarder aussi, car c'est tres en vogue Smile

Sur un INSERT on voir ces perf, mais sur des UPDATE et des SELECT ? De plusieurs table?

Smile

Merci en tout cas

5.
Anthony Goudemez Anthony Goudemez France says:

Cela me rappelle un excellent article sur les dérives de LinqToSql lorsque l'on ne fait pas attention aux requêtes générées.

www.hanselman.com/.../...sWhatYouThinkItMeans.aspx

Un grand merci pour ce comparatif !

6.
Gyzmau Gyzmau France says:

Sur un gros volume de données j'ai souvent eu des problèmes avec LinqToSql.
Comme le dit  Anthony il faut bien faire attention aux requêtes générées.
D'ailleurs parfois ca pique les yeux de les regarder sur des constructions un peu complexe ;).


Sinon comment ca va nico au luxembourg
Ici ca se passe pas trop mal à la SG ;).

7.
Nicolas Esprit Nicolas Esprit France says:

Et oui, Linq est super pratique, il n'y a rien à y redire, mais ça demande une certaine attention d'un point de vue performance.

A part ça, ça se passe bien au Luxembourg. Il n'y a pas de grosse boîte comme la SGCIB, c'est beaucoup plus petit et les mentalités sont différentes. Mais il y a des projets intéressants, et la qualité de vie est meilleure qu'à Paris Smile

Passe le bonjour aux habitués du Valmy Smile

Les commentaires sont clos