SQL Server CE 4.0 性能对比 [英] SQL Server CE 4.0 performance comparison

问题描述

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

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

特别是:

• SQLite
• SQL Server (1)
• SQL Server Express *
• 也许是火鸟

(1) 适用于功能相当的应用程序.

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

如果有人能找到或分享此类信息，就让我们在这里收集这些信息，以供未来的人类使用.

解决方案

在我看来，将嵌入式数据库(如 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 提供更好的支持.

-- 更新 --

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

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

使用系统；使用 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 Func,DbConnection, TimeSpan>MeasureExecTime = (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. 我在我的机器上得到了以下结果:Dell Precision WorkStation T7400 配备 2 个 Intel Xeon E5420 CPU 和 8GB RAM，运行 64 位 Win7 Enterprise.
2. 我将两个数据库的默认设置与连接字符串Data Source=database_file_name"一起使用.
3. 我使用了 SQL CE 4.0 和 SQLite/System.Data.SQLite 的最新版本(从今天开始，2011 年 6 月 3 日).

以下是两个不同样本的结果:

<前>> =测试嵌入式数据库的CRUD性能=> => 样本量:200> ==测试SQLite==> 创建表:396.0396 毫秒> 写入数据:22189.2187 ms> 更新数据:23591.3589 毫秒> 读取数据:21.0021 毫秒> 删除数据:20963.0961 毫秒> 丢弃表:85.0085 毫秒> ==测试 SQL CE 4.0==> 创建表:16.0016 毫秒> 写入数据:25.0025 ms> 更新数据:56.0056 毫秒> 读取数据:28.0028 毫秒> 删除数据:53.0053 毫秒> 丢弃表:11.0011 毫秒

...以及更大的样本:

<前>=测试嵌入式数据库的CRUD性能==> 样本量:1000==测试SQLite==创建表:93.0093 毫秒写入数据:116632.6621 ms更新数据:104967.4957 毫秒读取数据:134.0134 毫秒删除数据:107666.7656 毫秒丢弃表:83.0083 毫秒==测试 SQL CE 4.0==创建表:16.0016 毫秒写入数据:128.0128 毫秒更新数据:307.0307 毫秒读取数据:164.0164 毫秒删除数据:306.0306 毫秒丢弃表:13.0013 毫秒

因此，如您所见，与 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 适配器):从数据连接意外关闭到数据库文件损坏.我认为数据适配器或库本身存在一些稳定性问题.

SQL Server CE 4 (SQL Server Compact Edition 4.0) is not news already (If it is, you could read this article)

But it is very interesting to see SQL Server CE 4 performance comparison to other databases.

Especially with:

• SQLite
• SQL Server (1)
• SQL Server Express *
• maybe Firebird

(1) for applications where functionality is comparable.

Unfortunately there are not so much links about the subject that google provides right now. Actually I was unable to find any (for proper SQL CE version).

If one could find or share such information lets collect it here for future humanity.

解决方案

In my opinion, it is incorrect to compare the embedded database (like SQL CE) versus server-side relational database (like all the rest, except for SQLite and the Embedded version of Firebird).

The main difference between them is that the general-purpose server-side relational databases (like MS SQL, MySQL, Firebird Classic and SuperServer etc.) are installed as an independent service and run outside of the scope of your main application. That is why they can perform much better because of the intrinsic support for multi-core and multi-CPU architectures, using OS features like pre-caching, VSS etc to increase the throughput in case of intensive database operation and can claim as much memory as your OS can provide for a single service/application. It also means that the performance indicators for them are more or less independent from your application, but largely depend upon your hardware. In this respect I would say that the server versions of any database are always more performance compared to the embedded ones.

SQL CE (along with Firebird Embedded, SQLite, TurboSQL and some other) are embedded DB engines, meaning that the complete database is packed into a single (or maximally 2) DLL-files that are distributed together with your application. Due to the evident size limitations (would you like to have to distribute a 30 MB DLL together with your 2-3 MB long application?) they also run directly in the context of your application and the total memory and performance for data access operations are shared with other parts of your application -- that regards both available memory, CPU time, disk throughput etc. Having a computation-intensive threads running in parallel with your data access thread might lead to dramatic decrease of your database performance.

Due to the different areas of application these databases have different palette of options: server-db provide extensive user and right management, support for views and stored procedures, whereas embedded database normally lack any support for users and rights management and have limited support for views and stored procedures (latter ones lose the majority of their benefits of running on server side). Data throughput is a usual bottlenecks of RDBMS, server versions are usually installed on striped RAID volumes, whereas embedded DB are often memory-oriented (try to keep all the actual data in the memory) and minimize the data storage access operations.

So, what would make sense probably is to compare different embedded RDBMS for .Net for their performance, like MS SQL CE 4.0, SQLite, Firebird Embedded, TurboSQL. I wouldn't expect drastic differences during usual non-peak operation, whereas some database may provide better support for large BLOBs due to better integration with OS.

-- 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 性能对比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！