本文介绍了SQL Server CE 4.0 性能对比的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

SQL Server CE 4 (SQL Server Compact Edition 4.0) 已经不是新闻了(如果是,你可以 阅读这篇文章)

但是看到 SQL Server CE 4 性能与其他数据库的比较非常有趣.

特别是:

  • -- 更新 --

    我必须收回我最后的话,因为我的快速实施显​​示了非常有趣的结果.

    我编写了一个简短的控制台应用程序来测试这两个数据提供程序,如果您想自己试验它们,这里是您的源代码.

    使用系统;使用 System.Collections.Generic;使用 System.Linq;使用 System.Text;使用 System.Data.SQLite;使用 System.Data.SqlServerCe;使用 System.Data.Common;命名空间 TestSQL{课程计划{const int NUMBER_OF_TESTS = 1000;私有静态字符串create_table;private static string create_table_sqlce = "CREATE TABLE Test (id integer not null identity primary key, textdata nvarchar(500));";private static string create_table_sqlite = "CREATE TABLE Test (id integer not null primary key, textdata nvarchar(500));";私有静态字符串 drop_table = "DROP TABLE 测试";private static string insert_data = "INSERT INTO Test (textdata) VALUES ('{0}');";private static string read_data = "SELECT textdata FROM Test WHERE id = {0}";private static string update_data = "更新测试集 textdata = '{1}' WHERE id = {0}";私有静态字符串delete_data =从测试中删除ID = {0}";静态操作ACreateTable = (a) =>创建表(一);静态操作ATestWrite = (a) =>测试写入(一,NUMBER_OF_TESTS);静态操作ATestRead = (a) =>TestRead(a, NUMBER_OF_TESTS);静态操作ATestUpdate = (a) =>测试更新(一,NUMBER_OF_TESTS);静态操作ATestDelete = (a) =>TestDelete(a, NUMBER_OF_TESTS);静态操作ADropTable = (a) =>DropTable(a);static FuncMeasureExecTime = (a,b) =>{ var start = DateTime.Now;(b);var 完成 = DateTime.Now;返回完成 - 开始;};静态动作AMeasureAndOutput = (a, b) =>Console.WriteLine(a, b.TotalMilliseconds);static void Main(string[] args){//打开数据库SQLiteConnection.CreateFile("sqlite.db");SQLiteConnection sqliteconnect = new SQLiteConnection("Data Source=sqlite.db");SqlCeConnection sqlceconnect = new SqlCeConnection("Data Source=sqlce.sdf");sqlceconnect.Open();sqliteconnect.Open();Console.WriteLine("=测试嵌入式数据库的CRUD性能=");Console.WriteLine(" => Samplesize: {0}", NUMBER_OF_TESTS);create_table = create_table_sqlite;Console.WriteLine("==测试SQLite==");DoMeasures(sqliteconnect);create_table = create_table_sqlce;Console.WriteLine("==测试 SQL CE 4.0==");DoMeasures(sqlceconnect);Console.ReadKey();}静态无效 DoMeasures(DbConnection con){AMeasureAndOutput("Creating table: {0} ms", MeasureExecTime(ACreateTable, con));AMeasureAndOutput("写入数据:{0} ms", MeasureExecTime(ATestWrite, con));AMeasureAndOutput("更新数据:{0} ms", MeasureExecTime(ATestUpdate, con));AMeasureAndOutput("读取数据:{0} ms", MeasureExecTime(ATestRead, con));AMeasureAndOutput("删除数据:{0} ms", MeasureExecTime(ATestDelete, con));AMeasureAndOutput("删除表:{0} ms", MeasureExecTime(ADropTable, con));}静态无效 CreateTable(DbConnection con){var sqlcmd = con.CreateCommand();sqlcmd.CommandText = create_table;sqlcmd.ExecuteNonQuery();}static void TestWrite(DbConnection con, int num){for (; num-- > 0; ){var sqlcmd = con.CreateCommand();sqlcmd.CommandText = string.Format(insert_data,Guid.NewGuid().ToString());sqlcmd.ExecuteNonQuery();}}static void TestRead(DbConnection con, int num){随机 rnd = new Random(DateTime.Now.Millisecond);for (var max = num; max-- > 0; ){var sqlcmd = con.CreateCommand();sqlcmd.CommandText = string.Format(read_data, rnd.Next(1,num-1));sqlcmd.ExecuteNonQuery();}}静态无效测试更新(DbConnection con,int num){随机 rnd = new Random(DateTime.Now.Millisecond);for (var max = num; max-- > 0; ){var sqlcmd = con.CreateCommand();sqlcmd.CommandText = string.Format(update_data, rnd.Next(1, num - 1), Guid.NewGuid().ToString());sqlcmd.ExecuteNonQuery();}}静态无效 TestDelete(DbConnection con, int num){随机 rnd = new Random(DateTime.Now.Millisecond);var order = Enumerable.Range(1, num).ToArray();动作交换 = (arr, a, b) =>{ int c = arr[a];arr[a] = arr[b];arr[b] = c;};//打乱数组for (var max=num; max-- > 0; ) swap(order, rnd.Next(0, num - 1), rnd.Next(0, num - 1));foreach(int index in order){var sqlcmd = con.CreateCommand();sqlcmd.CommandText = string.Format(delete_data, index);sqlcmd.ExecuteNonQuery();}}静态无效 DropTable(DbConnection con){var sqlcmd = con.CreateCommand();sqlcmd.CommandText = drop_table;sqlcmd.ExecuteNonQuery();}}}

    必要的免责声明:

    1. 我在我的机器上得到了以下结果:

      -- update --

      I have to take back my last words, for my quick implementation shows very interesting results.

      I wrote a short console application to test both data providers, here is the source code for you if you want to experiment with them on your own.

      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using System.Data.SQLite;
      using System.Data.SqlServerCe;
      using System.Data.Common;
      
      namespace TestSQL
      {
          class Program
          {
              const int NUMBER_OF_TESTS = 1000;
      
              private static string create_table;
      
              private static string create_table_sqlce =  "CREATE TABLE Test ( id integer not null identity primary key, textdata nvarchar(500));";
              private static string create_table_sqlite = "CREATE TABLE Test ( id integer not null primary key, textdata nvarchar(500));";
      
              private static string drop_table = "DROP TABLE Test";
              private static string insert_data = "INSERT INTO Test (textdata) VALUES ('{0}');";
              private static string read_data = "SELECT textdata FROM Test WHERE id = {0}";
              private static string update_data = "UPDATE Test SET textdata = '{1}' WHERE id = {0}";
              private static string delete_data = "DELETE FROM Test WHERE id = {0}";
      
              static Action<DbConnection> ACreateTable = (a) => CreateTable(a);
              static Action<DbConnection> ATestWrite = (a) => TestWrite(a, NUMBER_OF_TESTS);
              static Action<DbConnection> ATestRead = (a) => TestRead(a, NUMBER_OF_TESTS);
              static Action<DbConnection> ATestUpdate = (a) => TestUpdate(a, NUMBER_OF_TESTS);
              static Action<DbConnection> ATestDelete = (a) => TestDelete(a, NUMBER_OF_TESTS);
              static Action<DbConnection> ADropTable = (a) => DropTable(a);
      
              static Func<Action<DbConnection>,DbConnection, TimeSpan> MeasureExecTime = (a,b) => { var start = DateTime.Now; a(b); var finish = DateTime.Now; return finish - start; };
      
              static Action<string, TimeSpan> AMeasureAndOutput = (a, b) => Console.WriteLine(a, b.TotalMilliseconds);
      
              static void Main(string[] args)
              {
                  // opening databases
                  SQLiteConnection.CreateFile("sqlite.db");
                  SQLiteConnection sqliteconnect = new SQLiteConnection("Data Source=sqlite.db");
                  SqlCeConnection sqlceconnect = new SqlCeConnection("Data Source=sqlce.sdf");
      
                  sqlceconnect.Open();
                  sqliteconnect.Open();
      
                  Console.WriteLine("=Testing CRUD performance of embedded DBs=");
                  Console.WriteLine(" => Samplesize: {0}", NUMBER_OF_TESTS);
      
                  create_table = create_table_sqlite;
                  Console.WriteLine("==Testing SQLite==");
                  DoMeasures(sqliteconnect);
      
                  create_table = create_table_sqlce;
                  Console.WriteLine("==Testing SQL CE 4.0==");
                  DoMeasures(sqlceconnect);
      
      
      
                  Console.ReadKey();
      
              }
      
              static void DoMeasures(DbConnection con)
              {
                  AMeasureAndOutput("Creating table: {0} ms", MeasureExecTime(ACreateTable, con));
                  AMeasureAndOutput("Writing data: {0} ms", MeasureExecTime(ATestWrite, con));
                  AMeasureAndOutput("Updating data: {0} ms", MeasureExecTime(ATestUpdate, con));
                  AMeasureAndOutput("Reading data: {0} ms", MeasureExecTime(ATestRead, con));
                  AMeasureAndOutput("Deleting data: {0} ms", MeasureExecTime(ATestDelete, con));
                  AMeasureAndOutput("Dropping table: {0} ms", MeasureExecTime(ADropTable, con));
              }
      
      
      
              static void CreateTable(DbConnection con)
              {
                  var sqlcmd = con.CreateCommand();
                  sqlcmd.CommandText = create_table;
                  sqlcmd.ExecuteNonQuery();
              }
      
              static void TestWrite(DbConnection con, int num)
              {
                  for (; num-- > 0; )
                  {
                      var sqlcmd = con.CreateCommand();
                      sqlcmd.CommandText = string.Format(insert_data,Guid.NewGuid().ToString());
                      sqlcmd.ExecuteNonQuery();
                  }
      
              }
      
              static void TestRead(DbConnection con, int num)
              {
                  Random rnd = new Random(DateTime.Now.Millisecond);
                  for (var max = num; max-- > 0; )
                  {
                      var sqlcmd = con.CreateCommand();
                      sqlcmd.CommandText = string.Format(read_data, rnd.Next(1,num-1));
                      sqlcmd.ExecuteNonQuery();
                  }
              }
      
              static void TestUpdate(DbConnection con, int num)
              {
                  Random rnd = new Random(DateTime.Now.Millisecond);
                  for (var max = num; max-- > 0; )
                  {
                      var sqlcmd = con.CreateCommand();
                      sqlcmd.CommandText = string.Format(update_data, rnd.Next(1, num - 1), Guid.NewGuid().ToString());
                      sqlcmd.ExecuteNonQuery();
                  }
              }
      
              static void TestDelete(DbConnection con, int num)
              {
                  Random rnd = new Random(DateTime.Now.Millisecond);
                  var order = Enumerable.Range(1, num).ToArray<int>();
                  Action<int[], int, int> swap = (arr, a, b) => { int c = arr[a]; arr[a] = arr[b]; arr[b] = c; };
      
                  // shuffling the array
                  for (var max=num; max-- > 0; ) swap(order, rnd.Next(0, num - 1), rnd.Next(0, num - 1));
      
      
                  foreach(int index in order)
                  {
                      var sqlcmd = con.CreateCommand();
                      sqlcmd.CommandText = string.Format(delete_data, index);
                      sqlcmd.ExecuteNonQuery();
                  }
              }
      
              static void DropTable(DbConnection con)
              {
                  var sqlcmd = con.CreateCommand();
                  sqlcmd.CommandText = drop_table;
                  sqlcmd.ExecuteNonQuery();
              }
      
      
          }
      }
      

      Necessary disclaimer:

      1. I got these results on my machine: Dell Precision WorkStation T7400 equipped with 2 Intel Xeon E5420 CPUs and 8GB of RAM, running 64bit Win7 Enterprise.
      2. I used the default settings for both DBs with connection string "Data Source=database_file_name".
      3. I used the latest versions of both SQL CE 4.0 and SQLite/System.Data.SQLite (from today, June 3rd 2011).

      Here are the results for two different samples:

      > =Testing CRUD performance of embedded DBs=
      > => Samplesize: 200
      > ==Testing SQLite==
      > Creating table: 396.0396 ms
      > Writing data: 22189.2187 ms
      > Updating data: 23591.3589 ms
      > Reading data: 21.0021 ms
      > Deleting data: 20963.0961 ms
      > Dropping table: 85.0085 ms
      
      > ==Testing SQL CE 4.0==
      > Creating table: 16.0016 ms
      > Writing data: 25.0025 ms
      > Updating data: 56.0056 ms
      > Reading data: 28.0028 ms
      > Deleting data: 53.0053 ms
      > Dropping table: 11.0011 ms
      

      ... and a bigger sample:

      =Testing CRUD performance of embedded DBs=
       => Samplesize: 1000
      ==Testing SQLite==
      Creating table: 93.0093 ms
      Writing data: 116632.6621 ms
      Updating data: 104967.4957 ms
      Reading data: 134.0134 ms
      Deleting data: 107666.7656 ms
      Dropping table: 83.0083 ms
      
      ==Testing SQL CE 4.0==
      Creating table: 16.0016 ms
      Writing data: 128.0128 ms
      Updating data: 307.0307 ms
      Reading data: 164.0164 ms
      Deleting data: 306.0306 ms
      Dropping table: 13.0013 ms
      

      So, as you can see, any writing operations (create, update, delete) require almost 1000x more time in SQLite compared to SQLCE. It does not necessarily reflect the general bad performance of this database and might be due to the following:

      1. The data provider I use for SQLite is the System.Data.SQLite, that is a mixed assembly containing both managed and unmanaged code (SQLite is originally written completely in C and the DLL only provides bindings). Probably P/Invoke and data marshaling eats up a good piece of the operation time.
      2. Most likely SQLCE 4.0 caches all the data in memory by default, whereas SQLite flushes most of the data changes directly to the disk storage every time the change happens. One can supply hundreds of parameters for both databases via connection string and tune them appropriately.
      3. I used a series of single queries to test the DB. At least SQLCE supports bulk operations via special .Net classes that would be better suited here. If SQLite supports them too (sorry, I am not an expert here and my quick search yielded nothing promising) it would be nice to compare them as well.
      4. I have observed many problems with SQLite on x64 machines (using the same .net adapter): from data connection being closed unexpectedly to database file corruption. I presume there is some stability problems either with the data adapter or with the library itself.

      这篇关于SQL Server CE 4.0 性能对比的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-23 03:42
查看更多