SQL Server CE 4.0性能比较

Question

SQL Server CE 4 (SQL Server Compact Edition 4.0) 已经不是新闻了（如果是的话，您可以 阅读本文）

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

尤其是：

• SQLite
• SQL Server (1)
• SQL Server Express *
• 可能是 Firebird

对于功能相当的应用程序，

(1)。不幸的是，谷歌目前提供的关于该主题的链接并不多。实际上我无法找到任何（对于正确的 SQL CE 版本）。

如果有人能够找到或分享此类信息，让我们为未来的人类收集它。

Answer 1

在我看来，将嵌入式数据库（如 SQL CE）与服务器端关系数据库（如所有其他数据库一样，除了 SQLite 和 Firebird 的嵌入式版本）进行比较是不正确的。

它们之间的主要区别在于通用服务器端关系数据库（如MS SQL、MySQL、Firebird Classic和SuperServer等）作为独立服务安装并在外部运行主要应用程序的范围。这就是为什么它们可以表现得更好，因为它们对多核和多 CPU 架构的内在支持，使用预缓存、VSS 等操作系统功能来提高密集数据库操作时的吞吐量，并且可以占用尽可能多的内存您的操作系统可以提供单一服务/应用程序。这也意味着它们的性能指标或多或少独立于您的应用程序，但很大程度上取决于您的硬件。在这方面，我想说任何数据库的服务器版本总是比嵌入式版本具有更高的性能。

SQL CE（以及 Firebird Embedded、SQLite、TurboSQL 等）是嵌入式数据库引擎，这意味着完整的数据库被打包到单个（或最多 2 个）DLL 文件中，这些文件与您的申请。由于明显的大小限制（您是否需要将 30 MB 的 DLL 与 2-3 MB 长的应用程序一起分发？），它们还直接在您的应用程序上下文中运行，并且总<数据访问操作的内存和性能与应用程序的其他部分共享 - 这涉及可用内存、CPU 时间、磁盘吞吐量等。具有与数据访问线程并行运行的计算密集型线程可能会导致数据库性能急剧下降。

由于应用领域不同，这些数据库具有不同的选项：server-db 提供广泛的用户和权限管理、对视图和存储过程的支持，而嵌入式数据库通常缺乏对用户和权限管理的任何支持，并且对视图的支持有限和存储过程（后者失去了在服务器端运行的大部分好处）。数据吞吐量是 RDBMS 的常见瓶颈，服务器版本通常安装在条带 RAID 卷上，而嵌入式 DB 通常是面向内存的（尝试将所有实际数据保留在内存中）并最大限度地减少数据存储访问操作。

因此，比较 .Net 的不同嵌入式 RDBMS 的性能可能是有意义的，例如 MS SQL CE 4.0、SQLite、Firebird Embedded、TurboSQL。 我不希望在通常的非高峰操作期间出现巨大的差异，而某些数据库由于与操作系统更好的集成，可能会为大型 BLOB 提供更好的支持。

-- 更新 --

我必须收回我的最后一次换句话说，我的快速实施显​​示了非常有趣的结果。

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

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();
        }


    }
}

必要的免责声明：

1. 我在我的机器上得到了这些结果：配备 2 个 Intel Xeon E5420 CPU 和 8GB RAM 的 Dell Precision WorkStation T7400，运行 64 位 Win7 Enterprise。
2. 我使用了两个数据库的默认设置以及连接字符串“Data Source=database_file_name”。
3. 我使用了最新版本的 SQL CE 4.0 和 SQLite/System.Data.SQLite（从今天开始，2011 年 6 月 3 日）。

以下是两个不同示例的结果：

> =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

...以及一个更大的示例：

=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

因此，如您所见，与 SQLCE 相比，SQLite 中的任何写入操作（创建、更新、删除）所需的时间几乎多出 1000 倍。它不一定反映该数据库的总体性能不佳，可能是由于以下原因造成的：

1. 我用于 SQLite 的数据提供程序是 System.Data.SQLite，这是一个包含托管和非托管代码的混合程序集（SQLite 最初完全用 C 编写，DLL 仅提供绑定）。可能 P/Invoke 和数据封送占用了大部分操作时间。
2. 默认情况下，SQLCE 4.0 很可能将所有数据缓存在内存中，而 SQLite 在每次发生更改时将大部分数据更改直接刷新到磁盘存储。人们可以通过连接字符串为两个数据库提供数百个参数并适当调整它们。
3. 我使用了一系列单个查询来测试数据库。至少 SQLCE 通过特殊的 .Net 类支持批量操作，这更适合这里。如果 SQLite 也支持它们（抱歉，我不是这方面的专家，我的快速搜索没有产生任何有希望的结果），那么对它们进行比较也会很好。
4. 我在 x64 机器上观察到 SQLite 的许多问题（使用相同的 .net 适配器）：从数据连接意外关闭到数据库文件损坏。我认为数据适配器或库本身存在一些稳定性问题。

Answer 2

这是我在 CodeProject 网页上新鲜出炉的关于基准测试的文章：

基准测试 .Net 嵌入式数据库的性能：SQL CE 4.0 vs SQLite

（该文章现在处于待处理状态，您需要登录 CodeProject 才能访问其内容）

PS：我错误地将我之前的答案标记为社区 wiki 条目，并且不会得到任何内容为它赢得声誉。这鼓励我为代码项目撰写关于此主题的文章，其中包含一些优化的代码、有关嵌入式数据库和统计分析的更多附加信息结果。因此，如果您喜欢这篇文章和我的第二个答案，请投票支持该答案。

Answer 3

因为我对 Alaudo 的测试、测试结果以及最终的结论感到非常困难，所以我继续尝试了一下他的程序，并提出了一个修改版本。

它测试以下 10 次中的每一个并输出平均时间：

• 没有事务的 sqlite、使用默认的 jounal_mode
• sqlite 和事务、使用默认的 Journal_mode
• sqlite 没有事务、使用 WAL jounal_mode
• sqlite 和事务、使用 WAL 日志模式
• sqlce 没有事务
• sqlce 和事务

这里是程序（实际上是一个类）：

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;

class SqliteAndSqlceSpeedTesting
{
    class Results
    {
        public string test_details;
        public long create_table_time, insert_time, update_time, select_time, delete_time, drop_table_time;
    }

    enum DbType { Sqlite, Sqlce };

    const int NUMBER_OF_TESTS = 200;
    const string create_table_sqlite = "CREATE TABLE Test (id integer not null primary key, textdata nvarchar(500));";
    const string create_table_sqlce = "CREATE TABLE Test (id integer not null identity primary key, textdata nvarchar(500));";
    const string drop_table = "DROP TABLE Test";
    const string insert_data = "INSERT INTO Test (textdata) VALUES ('{0}');";
    const string read_data = "SELECT textdata FROM Test WHERE id = {0}";
    const string update_data = "UPDATE Test SET textdata = '{1}' WHERE id = {0}";
    const string delete_data = "DELETE FROM Test WHERE id = {0}";

    public static void RunTests()
    {
        List<Results> results_list = new List<Results>();

        for (int i = 0; i < 10; i++) {
            results_list.Add(RunTest(DbType.Sqlite, false, false));
            results_list.Add(RunTest(DbType.Sqlite, false, true));
            results_list.Add(RunTest(DbType.Sqlite, true, false));
            results_list.Add(RunTest(DbType.Sqlite, true, true));
            results_list.Add(RunTest(DbType.Sqlce, false));
            results_list.Add(RunTest(DbType.Sqlce, true));                
        }

        foreach (var test_detail in results_list.GroupBy(r => r.test_details)) {
            Console.WriteLine(test_detail.Key);
            Console.WriteLine("Creating table: {0} ms", test_detail.Average(r => r.create_table_time));
            Console.WriteLine("Inserting data: {0} ms", test_detail.Average(r => r.insert_time));
            Console.WriteLine("Updating data: {0} ms", test_detail.Average(r => r.update_time));
            Console.WriteLine("Selecting data: {0} ms", test_detail.Average(r => r.select_time));
            Console.WriteLine("Deleting data: {0} ms", test_detail.Average(r => r.delete_time));
            Console.WriteLine("Dropping table: {0} ms", test_detail.Average(r => r.drop_table_time));
            Console.WriteLine();
        }
    }

    static Results RunTest(DbType db_type, bool use_trx, bool use_wal = false)
    {
        DbConnection conn = null;
        if (db_type == DbType.Sqlite)
            conn = GetConnectionSqlite(use_wal);
        else
            conn = GetConnectionSqlce();

        Results results = new Results();
        results.test_details = string.Format("Testing: {0}, transactions: {1}, WAL: {2}", db_type, use_trx, use_wal);
        results.create_table_time = CreateTable(conn, db_type);
        results.insert_time = InsertTime(conn, use_trx);
        results.update_time = UpdateTime(conn, use_trx);
        results.select_time = SelectTime(conn, use_trx);
        results.delete_time = DeleteTime(conn, use_trx);
        results.drop_table_time = DropTableTime(conn);
        conn.Close();
        return results;
    }

    static DbConnection GetConnectionSqlite(bool use_wal)
    {
        SQLiteConnection conn = new SQLiteConnection("Data Source=sqlite.db");
        if (!File.Exists(conn.Database))
            SQLiteConnection.CreateFile("sqlite.db");
        conn.Open();
        if (use_wal) {
            var command = conn.CreateCommand();
            command.CommandText = "PRAGMA journal_mode=WAL";
            command.ExecuteNonQuery();
        }
        return conn;
    }

    static DbConnection GetConnectionSqlce()
    {
        SqlCeConnection conn = new SqlCeConnection("Data Source=sqlce.sdf");
        if (!File.Exists(conn.Database))
            using (var sqlCeEngine = new SqlCeEngine("Data Source=sqlce.sdf"))
                sqlCeEngine.CreateDatabase();
        conn.Open();
        return conn;
    }

    static long CreateTable(DbConnection con, DbType db_type)
    {
        Stopwatch sw = Stopwatch.StartNew();
        var sqlcmd = con.CreateCommand();
        if (db_type == DbType.Sqlite)
            sqlcmd.CommandText = create_table_sqlite;
        else
            sqlcmd.CommandText = create_table_sqlce;
        sqlcmd.ExecuteNonQuery();
        return sw.ElapsedMilliseconds;
    }

    static long DropTableTime(DbConnection con)
    {
        Stopwatch sw = Stopwatch.StartNew();
        var sqlcmd = con.CreateCommand();
        sqlcmd.CommandText = drop_table;
        sqlcmd.ExecuteNonQuery();
        return sw.ElapsedMilliseconds;
    }

    static long InsertTime(DbConnection con, bool use_trx)
    {
        Stopwatch sw = Stopwatch.StartNew();
        var sqlcmd = con.CreateCommand();
        DbTransaction trx = null;
        if (use_trx) {
            trx = con.BeginTransaction();
            sqlcmd.Transaction = trx;
        }
        for (int i = 0; i < NUMBER_OF_TESTS; i++) {
            sqlcmd.CommandText = string.Format(insert_data, Guid.NewGuid().ToString());
            sqlcmd.ExecuteNonQuery();
        }
        if (trx != null)
            trx.Commit();
        return sw.ElapsedMilliseconds;
    }

    static long SelectTime(DbConnection con, bool use_trx)
    {
        Stopwatch sw = Stopwatch.StartNew();
        var sqlcmd = con.CreateCommand();
        DbTransaction trx = null;
        if (use_trx) {
            trx = con.BeginTransaction();
            sqlcmd.Transaction = trx;
        }
        Random rnd = new Random(DateTime.Now.Millisecond);
        for (var max = NUMBER_OF_TESTS; max-- > 0; ) {
            sqlcmd.CommandText = string.Format(read_data, rnd.Next(1, NUMBER_OF_TESTS - 1));
            sqlcmd.ExecuteNonQuery();
        }
        if (trx != null)
            trx.Commit();
        return sw.ElapsedMilliseconds;
    }

    static long UpdateTime(DbConnection con, bool use_trx)
    {
        Stopwatch sw = Stopwatch.StartNew();
        var sqlcmd = con.CreateCommand();
        DbTransaction trx = null;
        if (use_trx) {
            trx = con.BeginTransaction();
            sqlcmd.Transaction = trx;
        }
        Random rnd = new Random(DateTime.Now.Millisecond);
        for (var max = NUMBER_OF_TESTS; max-- > 0; ) {
            sqlcmd.CommandText = string.Format(update_data, rnd.Next(1, NUMBER_OF_TESTS - 1), Guid.NewGuid().ToString());
            sqlcmd.ExecuteNonQuery();
        }
        if (trx != null)
            trx.Commit();
        return sw.ElapsedMilliseconds;
    }

    static long DeleteTime(DbConnection con, bool use_trx)
    {
        Stopwatch sw = Stopwatch.StartNew();
        Random rnd = new Random(DateTime.Now.Millisecond);
        var order = Enumerable.Range(1, NUMBER_OF_TESTS).ToArray<int>();
        Action<int[], int, int> swap = (arr, a, b) => { int c = arr[a]; arr[a] = arr[b]; arr[b] = c; };
        var sqlcmd = con.CreateCommand();
        DbTransaction trx = null;
        if (use_trx) {
            trx = con.BeginTransaction();
            sqlcmd.Transaction = trx;
        }
        // shuffling the array
        for (var max = NUMBER_OF_TESTS; max-- > 0; ) swap(order, rnd.Next(0, NUMBER_OF_TESTS - 1), rnd.Next(0, NUMBER_OF_TESTS - 1));

        foreach (int index in order) {
            sqlcmd.CommandText = string.Format(delete_data, index);
            sqlcmd.ExecuteNonQuery();
        }
        if (trx != null)
            trx.Commit();
        return sw.ElapsedMilliseconds;
    }
}

这是我得到的数字：

Testing: Sqlite, transactions: False, WAL: False
Creating table: 24.4 ms
Inserting data: 3084.7 ms
Updating data: 3147.8 ms
Selecting data: 30 ms
Deleting data: 3182.6 ms
Dropping table: 14.5 ms

Testing: Sqlite, transactions: False, WAL: True
Creating table: 2.3 ms
Inserting data: 14 ms
Updating data: 12.2 ms
Selecting data: 6.8 ms
Deleting data: 11.7 ms
Dropping table: 0 ms

Testing: Sqlite, transactions: True, WAL: False
Creating table: 13.5 ms
Inserting data: 20.3 ms
Updating data: 24.5 ms
Selecting data: 7.8 ms
Deleting data: 22.3 ms
Dropping table: 16.7 ms

Testing: Sqlite, transactions: True, WAL: True
Creating table: 3.2 ms
Inserting data: 5.8 ms
Updating data: 4.9 ms
Selecting data: 4.4 ms
Deleting data: 3.8 ms
Dropping table: 0 ms

Testing: Sqlce, transactions: False, WAL: False
Creating table: 2.8 ms
Inserting data: 24.4 ms
Updating data: 42.8 ms
Selecting data: 30.4 ms
Deleting data: 38.3 ms
Dropping table: 3.3 ms

Testing: Sqlce, transactions: True, WAL: False
Creating table: 2.1 ms
Inserting data: 24.6 ms
Updating data: 44.2 ms
Selecting data: 32 ms
Deleting data: 37.8 ms
Dropping table: 3.2 ms

使用 sqlite 进行 200 次插入或更新大约 3 秒可能看起来仍然有点高，但至少比 23 秒更合理。相反，人们可能会担心 SqlCe 完成相同的 200 次插入或更新所需的时间太少，特别是因为在单个事务中使用每个 SQL 查询或在一个事务中一起使用每个 SQL 查询之间似乎没有真正的速度差异。我对 SqlCe 不太了解，无法解释这一点，但这让我担心。这是否意味着当 .Commit() 返回时，您不确定更改是否实际写入磁盘？

Answer 4

我最近参与了一个使用 SQL CE 4 和 NHibernate 的项目，我发现性能非常好。使用 SQL CE 4，我们能够在一秒钟内插入 8000 条记录。使用网络上的 Oracle，即使使用批量大小和 seqhilo 方法，我们每秒也只能插入 100 条记录。

我没有测试它，但查看了 .NET 的 NoSQL 产品的一些性能报告，SQL CE 4 似乎是独立应用程序的最佳解决方案之一。

只要避免使用 Identity 列，我们注意到如果不使用它们，性能会提高 40 倍。当使用 Identity 列作为 PK 时，插入相同的 8000 条记录需要 40 秒。