SQLite vs. SQLCE vs. ? in a Mobile Application

Question

When developing a mobile application on Windows Mobile, what are some of the database choices you've made?

Why did you make that choice? In particular we've got a mobile app that we're building which will have up to 360,000 records in some of the tables.

We were planning to use SQLCE 3.5 but we've got a number of drawbacks when working with large sets of data like this. Since data will be loaded into a DB on a desktop computer and then copied to the mobile device, the tables have to be re-indexed...

SQLite seems like an attractive alternative. Any advice/opinions?

Answer 1

I like the sqlite solution. Extremely small and simple to maintain.

Answer 2

• SQLite is great, really fast, really portable, really tiny. widely used and time-tested. can't go wrong with it.
• SQLite db files are crossplatform compatible, no need to reindex data when copying around. just copy the datafile to target and you're set.
• MS-SQL CE is closed source, and far heavier (min 2MB vs. max 300KB of code), and size-limited (db file is 4GB max vs ~1TB total, 1GB BLOBs)

sounds like a no-brainer

Answer 3

Just as an FYI, here's a benchmark I recently ran on both (with a home-grown bulk load utility in C#):

Details:

• Bulk insert of ~165,000 rows of data to a single table
• Insert data came from a text file (CSV file, though was pipe-delimited)
• Data was a mix of strings, integers, currency, floats etc.
• Table had 28 columns
• Only primary key index was used during the insert to speed up inserts

For SqlCe I tried two approaches:

• parameterized query, using a Prepare() and a single transaction for the entire insert.
• SqlCeResultSet (updatable), with an SqlCeUpdatableRecord

For SQLite I tried the following:

• parameterized query, using a Prepare() and a single transaction for the entire insert.

Here were the approximate time in seconds:

• SqlCe with Parm query - 48s
• SqlCe with resultset - 16s
• SQLite with Parm query - 11s

As you can see, SQLite was the fastest, not only when comparing both databases with parameterized queries, but also compared to an updateable resultset (which is considered the best and fastest way by many when it comes to bulk inserts in sqlce).

I should note that I used the SQLite.Net data provider for the SQLite code. For SqlCe I used the v3.5 SP1.

Another interesting point. The SqlCe database was approx 29 MB after I ran the bulk insert. The SQLite database was approx 23 MB.

Answer 4

I personally think you've hit upon the right idea. SqlLite is fast, cute as a barrel of ickle kittens and just generally amazing. On an embedded device with only one client it is awesome.

As i've commented in Javier's post I would suggest you focus on using the System.Data.Common types and isolating the SqlLite types through inheritance so if you do need to switch for better desktop integration or the better feature set its very easy to do.

Answer 5

If you want to speed sqlite a bit more

1. Try adding the index after you do the row inserts on the sqlite db. From my experience this helps a bit. ie don't create index before you insert, do it after

2. If you don't need transactions during the bulk insert using try

    db.execDML("PRAGMA synchronous = OFF;");
   
   
    // don't count changes
    db.execDML("PRAGMA count_changes = 0;");
   
   
    // turn journaling off
    db.execDML("PRAGMA journal_mode = OFF;");
   

Your bulk insert will scream, I had speed ups to 5+ times.

Note : You will you need 3.6.7 or later for the journal_mode pragma

Answer 6

A secondary but important consideration is that Sqlite has been around and stable for years and years. The .NET portable database solutions are frequently deprecated. I think there are at least three products currently at some level of support.

Answer 7

We are using SQLite as an application and stuff DB, with a fast home-made C++ wrapper. Since, it is fast, well-documented, in public domain, supports SQL92 with some minor exceptions, and so on. But may be you should consider liabilities of SQLite before choosing it. Since, tradeoffs are important. But, in my opinion, it is the optimum for embedded applications.

• SQLite does not support multiple connections, multiple reads possible but multiple writes is not supported. It is not design for high-concurrency.
• Omitted SQL92 features can be found at [1]
• SQLite does not enforce data types in any way. While there has been some healthy debate on this topic, it is in the final analysis considered to be a feature. (Type enforce is not related with primary keys)
• SQLite does not have the sophisticated planners and optimizers that developers might find in large multi-user databases.
• SQLite memory requirement increases proportionally with the size of database.

References

• Available at http://www.sqlite.org/omitted.html, December 2009