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: .NET 4.0, SqlCommand, SqlBulkCopy, LinqToSql, Linq, Performance