tomlev

Comparison of insertion performance

Sep 9th, 2011
94
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. void Main()
  2. {
  3.     var dbs = new[]
  4.     {
  5.         new { Provider = "System.Data.OleDb", ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\tmp\testdb\access.mdb" },
  6.         new { Provider = "System.Data.SqlServerCe.3.5", ConnectionString = @"Data Source=E:\tmp\testdb\sqlce35.sdf" },
  7.         new { Provider = "System.Data.SqlServerCe.4.0", ConnectionString = @"Data Source=E:\tmp\testdb\sqlce40.sdf" },
  8.         new { Provider = "System.Data.SQLite", ConnectionString = @"Data Source=E:\tmp\testdb\test.db" }
  9.     };
  10.  
  11.     foreach (var db in dbs)
  12.     {
  13.         var factory = DbProviderFactories.GetFactory(db.Provider);
  14.         using (var connection = factory.CreateConnection())
  15.         {
  16.             connection.ConnectionString = db.ConnectionString;
  17.             connection.Open();
  18.             CreateSchema(connection);
  19.             lastId = 0;
  20.            
  21.             // First run for JIT warmup
  22.             TestInsert(connection, 1, true);
  23.            
  24.             Stopwatch sw = Stopwatch.StartNew();
  25.             TestInsert(connection, 100000, true);
  26.             sw.Stop();
  27.             Console.WriteLine ("{0} (with transaction) - {1}", db.Provider, sw.Elapsed);
  28.            
  29.             sw.Reset();
  30.             sw.Start();
  31.             TestInsert(connection, 100000, false);
  32.             sw.Stop();
  33.             Console.WriteLine ("{0} (no transaction) - {1}", db.Provider, sw.Elapsed);
  34.         }
  35.     }
  36.  
  37. }
  38.  
  39. void CreateSchema(DbConnection connection)
  40. {
  41.     try
  42.     {
  43.         using (var dropCommand = connection.CreateCommand())
  44.         {
  45.             dropCommand.CommandText = "DROP TABLE Product";
  46.             dropCommand.ExecuteNonQuery();
  47.         }
  48.     }
  49.     catch { }
  50.    
  51.     using (var createTableCommand = connection.CreateCommand())
  52.     {
  53.         createTableCommand.CommandText = "CREATE TABLE Product (Id INTEGER NOT NULL PRIMARY KEY, Name NVARCHAR(200) NOT NULL)";
  54.         createTableCommand.ExecuteNonQuery();
  55.     }
  56. }
  57.  
  58. int lastId = 0;
  59. void TestInsert(DbConnection connection, int count, bool useTransaction)
  60. {
  61.     DbTransaction transaction = null;
  62.     if (useTransaction)
  63.         transaction = connection.BeginTransaction();
  64.     using (transaction)
  65.     {
  66.         using (var command = connection.CreateCommand())
  67.         {
  68.             command.CommandText = "INSERT INTO Product(Id, Name) VALUES (@id, @name)";
  69.            
  70.             var idParameter = command.CreateParameter();
  71.             idParameter.ParameterName = "id";
  72.             idParameter.DbType = DbType.Int32;
  73.             command.Parameters.Add(idParameter);
  74.            
  75.             var nameParameter = command.CreateParameter();
  76.             nameParameter.ParameterName = "name";
  77.             nameParameter.DbType = DbType.String;
  78.             nameParameter.Value = "blabla";
  79.             command.Parameters.Add(nameParameter);
  80.            
  81.             command.Transaction = transaction;
  82.            
  83.             for (int i = 0; i < count; i++)
  84.             {
  85.                 idParameter.Value = ++lastId;
  86.                
  87.                 command.ExecuteNonQuery();
  88.             }
  89.         }
  90.         if (transaction != null)
  91.             transaction.Commit();
  92.     }
  93.  
  94. }
RAW Paste Data