SQL Server CE 4.0 performance comparison

Question

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.

Answer 1

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.