SQLite and Windows Embedded Compact

Posted By Pavel Bansky
Program Manager

In today’s blog post I will look at a database for Windows Embedded Compact. Specifically I will look at SQLite which is becoming more and more popular, and compare it to good old SQL CE.

SQLite is a public domain single file database, which doesn’t require expensive engine to run on a device. All it needs is a single dll file or it can be compiled directly to your project. SQLite has been, for a while now, the database of choice for mobile game developers and it is supported on Windows, Windows RT, Windows Phone and you can get bits for Windows Embedded Compact as well.

I’ve decided to explore the last option and see how difficult it is to setup, use it and how well it performs. I was interested specifically in the performance, because there has been some feedback about poor performance with SQLite on Windows Embedded Compact.

Obtaining SQLite

SQLite ships either as a source or precompiled for different platforms. I’ve decided to go with version precompiled for .NET. When I say precompiled for .NET, I mean native .dll with managed wrapper around it. Some people believe that SQLite is implemented in managed code and therefore not well performing well, which is incorrect. The .NET flavor of SQLite is hosted on https://system.data.sqlite.org/and you can get version for Windows Embedded Compact there. The precompiled version contains binaries for ARM CPU.

Real life scenario data

In order to get as close as possible to the real user scenario, I needed some real data. Microsoft AdventureWorks database was created for that reason. However, AdventureWorks is distributed as Microsoft SQL Server Database and it’s pretty big. I’ve decided to export only two tables, which is small enough for embedded user scenario, but also enough to try joint queries as well. I wrote simple code that exported two lists of INSERT commands that were saved in two .txt files. These files will be read and commands executed by SQLite later on the device, to create and fill following two tables.

Person table contains 10,101 records and Address table contains 18,798 records.

Test code

I wrote a set of tests in C# for .NET Compact Framework 3.5 to be executed on Windows Embedded Compact 7. I wrote the same tests to run with SQLite and SQL CE, that way I could compare not just performance but also differences for developers and potential migration steps.

Creating database

Syntax for database creation is probably the only one with significant difference between SQL CE and SQLite. I created a password protected file and this is the syntax for SQL CE and for SQLite:

SQL CE

 string connectionString = string.Format("DataSource='{0}'; LCID=1033; Password='{1}'", databaseFile, Resources.DBPassword);
using (SqlCeEngine engine = new SqlCeEngine(connectionString))
{
      engine.CreateDatabase();            
}

SQLite

 SQLiteConnection.CreateFile(databasefile);
SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0}", databasefile));
conn.SetPassword(Resources.DBPassword);

As expected there was not a noticeable or even measurable performance difference between SQL CE and SQLite when creating database file.

Inserting records

Next test was more interesting; test code went through a .txt file, read the SQL INSERT commands and executed them one by one. As you can see, SQLite is using the ADO.NET syntax as SQL CE does. The only difference is the SQLite prefix in class names.

 using (SqlCeTransaction transaction = conn.BeginTransaction())
{
  using (SqlCeCommand cmd = new SqlCeCommand())
  {
    cmd.Connection = conn;

    while (!sr.EndOfStream)
    {
      cmd.CommandText = sr.ReadLine();
      cmd.ExecuteNonQuery();
    }
  }
  transaction.Commit();
}

SQLite

 using (SQLiteTransaction transaction = conn.BeginTransaction())
{
  using (SQLiteCommand cmd = new SQLiteCommand())
  {
    cmd.Connection = conn;
                    
    while (!sr.EndOfStream)
    {
      cmd.CommandText = sr.ReadLine();
      cmd.ExecuteNonQuery();
    }
  }
  transaction.Commit();
}

Results of this test were far more interesting. When executing this code outside the transaction, which means every line was executed as a separate transaction; SQLite was considerably slower than SQL CE. We are talking about tens of thousands records here so the small differences added up. Once I ran all inserts as part of one transaction, SQLite significantly outperformed SQL CE.

Queries

Third test was the most interesting one. I’ve run bunch of SQL queries; simple queries, joint queries, ordered results and/or limited results. While the SQLite .NET API is not different from SQL CE, as mentioned above, the actual SQL commands flavor can be different in some cases.

Compare the SELECT TOP query of SQL CE with SQLite version:

SQL CE

 SELECT TOP(1000) * FROM Person JOIN Address ON (Person.AddressID = Address.AddressID)

SQLite

 SELECT * FROM Person JOIN Address ON (Person.AddressID = Address.AddressID) LIMIT 1000

In every SQL query I’ve executed, SQLite outperformed SQL CE. Specifically queries with ordered results were hard to chew for SQL CE.

I’ve performed other set of tests with UPDATE and DELETE commands, but the results there were not much different; SQLite was faster than SQL CE. Another pleasant surprise for embedded developers is database size comparison. SQL CE file was almost twice as big as SQLite database file.

Compare the DELETE query of SQL CE with SQLite version:

SQL CE

 string cmdText = “DELETE FROM Person WHERE FirstName=’JOHN’”;
SqlCeCommand cmd = new SqlCeCommand(cmdText);
cmd.Connection = conn;
int row = cmd.ExecuteNonQuery();

SQLite

 string cmdText = “DELETE FROM Person WHERE FirstName=’JOHN’”;
SQLiteCommand cmd = new SQLiteCommand(cmdText);
cmd.Connection = conn;
int row = cmd.ExecuteNonQuery();

Conclusion

I’m not surprised that SQLite gain such popularity on many platforms, including Microsoft platforms, and I’m happy that it works well on existing versions of Windows Embedded Compact (Windows CE). Good news is also usability; porting your SQL CE powered application to SQLite is very simple because of the ADO.NET syntax, and in most cases only required a simple search and replace.

I would be interesting to hear about your experience with SQLite, especially in relation to embedded operating systems.