Skip to main content

Using a simple ORM with sqlite on WP7

I come from a background of using ORMs to do data access when developing for the full version of .Net. I'm very use to leveraging the power of these tools to do what I see as an infrastructure concern - repetitive and time consuming code to get data in and out of a persistence store. Now we are writing apps for WP7 and some require the use of databases, specifically sqlite. This required me to return to writing a DAL for several of the applications Rich and I are building.

Obviously the support for a full blown ORM on a phone device probably can't be done and why would you want too? I believe most apps requiring a database are going to have a read only nature (or mostly read with few writes) and have a simple relational model with few tables & constraints. Personally I wouldn't use a database to store a score table or user setting for a game\app, I'd more likely use isolated storage with a serialized object structure.

Rich & I've been porting an iPhone app recently, it makes use of a sqlite database. This is well supported on the iPhone platform and fortunately for us sqlite is also supported for WP7, see here. We decided to see if there was an support on the platform for a simple ORM tool (also known as ORM lite) to help simplify any DAL code we had to write. If you're wondering what an ORM lite is check out Dapper.Net or Massive written by Rob Corney of SubSonic fame.

Unfortunately most of these tools are not going to be used on the platform - they require the use of system.data namespace. But there is one which does support sqlite on WP7 - Vici Project. They have a simple ORM library called CoolStorage. This library is written on top of the sqlite support for WP7. Shown below is how easy it can be to access data in your database along with a screenshot of the database schema. This code reads all the postcodes from a test database into a POCO array:

private IEnumerable GetAllPostCodes()
{
    CSConfig.SetDB(databaseFile, SqliteOption.None);
    return CSDatabase.RunQuery(viciCoolStorageSql);
}


As you can see I can do the data access in 2 lines of code :) I definitely like the approach and it gives me the ability to populate the model classes with ease. The model here is the PostCode class it is no more than a bindable POCO class with 4 settable properties.

Now I'm to old to expect to get anything for nothing, those 2 lines of code surely must have an associated cost. After all this library is built on top of the WP7 implementation for sqlite database access. The next step was to do some simple tests to compare performance.

We decided to CoolStorage to writing the DAL code explicitly. The first test was to get all the post codes:

CoolStorage
SQL -'SELECT postCode AS Value, district AS PostDistrict, latitude AS Latitude, longitude AS Longitude FROM PostCodes'
private void CoolStorageExecute()
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();
                
    CSConfig.SetDB(databaseFile, SqliteOption.None);
    var results = CSDatabase.RunQuery(viciCoolStorageSql);

    stopwatch.Stop();

    Debug.WriteLine("Vici CoolStorage duration: {0} ms, results count - {1}", stopwatch.ElapsedMilliseconds, results.Length);
}

Explicit DAL Code - I was a bit rusty writing this :)
SQL - 'SELECT postCode, district, latitude, longitude FROM PostCodes'
private void ExplicitDalExecute()
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();

    var results = new List<PostCode>();
    using (var conn = new SqliteConnection("Version=3,uri=file:" + databaseFile))
    {
        conn.Open();

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = csharpSqliteSql;
            using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
            {
                while (reader.Read())
                {
                    var postCode = new PostCode
                                        {
                                            Value = reader.GetString(0),
                                            District = reader.GetString(1),
                                            Latitude = reader.GetDouble(2),
                                            Longitude = reader.GetDouble(3),
                                        };

                    results.Add(postCode);
                }
            }

            conn.Close();
        }
    }

    stopwatch.Stop();

    Debug.WriteLine("Explicit CSharp Sqlite duration: {0} ms, results count - {1}", stopwatch.ElapsedMilliseconds, results.Count);
}

I used a simple WP7 app to run this code and produce the following results, what you can see is the explicit DAL is faster than CoolStorage, by an approximate factor of 1.5 to return a recordset of 2821 rows.

CoolStorage  average = 322 ms
Explicit DAL average = 253 ms

I negated the first debug output as this includes JIT'ing time for any session classes.


I'm not surprised by the results, as I said you don't get anything for free. What is surprising is my thoughts about moving away from using CoolStorage. Normally I would do anything to improve app performance but right now we aren't in any hurry to swap out the code, why?

We are currently in a UAT phase for the app and unless the performance of the search is flagged up as an issue it won't be changed. It would delay the release and for what, this isn't a trading system...


Comments

  1. I'm using this sqlite library in my wp7 app, and for me performance is critical as I may be loading tens of thousands of smallish objects from the db.

    ORMs-lite may be fine if you don't expect huge datasets. Otherwise, learn the native sqlite API from sqlite.org and use that, there are all sorts of optimizations specific to sqlite that you can use to wring out that last drop of performance, and I'm sure the ORM is not that smart, and you will never know about these optimizations without digging through the sqlite docs.

    ReplyDelete
  2. I appreciate performance is critical for you, but our usage scenario it isn't yet. I also appreciate the comments about sqlite.org and the tweeks available.

    Out of interest, why are you possibly loading tens of thousands objects, surely some form of pagination solves this problem?

    ReplyDelete
  3. I admit my situation is unusual. In a nutshell, a user may have 10k widgets, but only a small subset are of interest TODAY. The widgets are lazy-loaded, but to figure out today's subset, I need to analyze the "stats" object for each widget.

    I'll pre-calculate this offline once mango is widespread and I can use background agents, but for now its a load screen and fighting tooth and nail for every millisecond.

    ReplyDelete
  4. So are you using a port of the sqlite code on google code?

    And does an in memory caching pattern not speed up the access?

    I guess once you open the database you keep the connection open all the time the app is running.

    ReplyDelete
  5. Yup,
    Yup - but you lose the cache as soon as the app tombstones, trying to preserve it turned out to be too heavy an op and would occasionally throw on tombstone.
    Yup

    ReplyDelete
  6. Interest, we built an isolated cache provider for the WP7Contrib so it can survive tombstoning but as you stated reviving the cache requires time. We also have an in memory version which doesn't survive tombstoning.

    ReplyDelete
  7. Ollie Riches

    Here is an ORM that works with Sqlite
    https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

    ReplyDelete

Post a Comment

Popular posts from this blog

WPF tips & tricks: Dispatcher thread performance

Not blogged for an age, and I received an email last week which provoked me back to life. It was a job spec for a WPF contract where they want help sorting out the performance of their app especially around grids and tabular data. I thought I'd shared some tips & tricks I've picked up along the way, these aren't probably going to solve any issues you might be having directly, but they might point you in the right direction when trying to find and resolve performance issues with a WPF app. First off, performance is something you shouldn't try and improve without evidence, and this means having evidence proving you've improved the performance - before & after metrics for example. Without this you're basically pissing into the wind, which can be fun from a developer point of view but bad for a project :) So, what do I mean by ' Dispatcher thread performance '? The 'dispatcher thread' or the 'UI thread' is probably the most

Showing a message box from a ViewModel in MVVM

I was doing a code review with a client last week for a WPF app using MVVM and they asked ' How can I show a message from the ViewModel? '. What follows is how I would (and have) solved the problem in the past. When I hear the words ' show a message... ' I instantly think you mean show a transient modal message box that requires the user input before continuing ' with something else ' - once the user has interacted with the message box it will disappear. The following solution only applies to this scenario. The first solution is the easiest but is very wrong from a separation perspective. It violates the ideas behind the Model-View-Controller pattern because it places View concerns inside the ViewModel - the ViewModel now knows about the type of the View and specifically it knows how to show a message box window: The second approach addresses this concern by introducing the idea of messaging\events between the ViewModel and the View. In the example below

Implementing a busy indicator using a visual overlay in MVVM

This is a technique we use at work to lock the UI whilst some long running process is happening - preventing the user clicking on stuff whilst it's retrieving or rendering data. Now we could have done this by launching a child dialog window but that feels rather out of date and clumsy, we wanted a more modern pattern similar to the way <div> overlays are done on the web. Imagine we have the following simple WPF app and when 'Click' is pressed a busy waiting overlay is shown for the duration entered into the text box. What I'm interested in here is not the actual UI element of the busy indicator but how I go about getting this to show & hide from when using MVVM. The actual UI elements are the standard Busy Indicator coming from the WPF Toolkit : The XAML behind this window is very simple, the important part is the ViewHost. As you can see the ViewHost uses a ContentPresenter element which is bound to the view model, IMainViewModel, it contains 3 child v