Very bad SQLite performance on Windows Phone 8

Question

I have a Windows Phone 8 Application that uses a SQLite database. To access the database, I use the WinRT Wrapper by Andy Wigley (source)

My database is very simple, only one table: Locations: integer primary key "FieldIndex", varchar "Field1", varchar "Field2", varchar "Field3", varchar "Field4", varchar "Field5", varchar "Field6", int "CategoryID"

I also have a index on both "FieldIndex" and "CategoryID".

There are in total 4000 entries in the table with the database being 900 kB in size. I have also compacted the database (vacuum). The database is deployed to the phone as application content (= in the installation folder = read-only). I only use the database for Queries. My data access code looks like this:

using (var db = new SQLiteWinRTPhone.Database(Package.Current.InstalledLocation, @"Model\db.sqlite"))
{
    await db.OpenAsync(SQLiteWinRTPhone.SqliteOpenMode.OpenRead);
    await db.ExecuteStatementAsync("PRAGMA journal_mode = MEMORY");
    await db.ExecuteStatementAsync("PRAGMA temp_store =  2;");

    using (var stmt = await db.PrepareStatementAsync("SELECT * FROM Locations;"))
    {
        int i = 0;
        while (await stmt.StepAsync())
        {
            // There is nothing happening here
            // Just for testing. In my real code, I iterate on all rows and store them in a object. I wanted isolate the issue here.
            i++;
        }
    }
    MessageBox.Show("We read " + i.toString() + " records.");
}

At the moment, the above statement takes 20 seconds to complete. From my standpoint, this is unacceptable. I tried to profile my application and the hotpath is in the native code libaries (mostly ConcRT related). The WinRT wrapper is compiled as "release" and I don't understand why the performance is so bad.

GOAL: I want to read all rows and store them in a object for binding to my view, searching, etc. and so on.

Any ideas what I could do to make my database queries somewhat acceptable (< 5 seconds)?

Answer 1

I dont' know what is wrong with your code. You can also try to use another wrapper for example c#-sqlite + sqlite-net ORM. https://github.com/peterhuene/sqlite-net-wp8 I have used it for my project and performance was very good.

Answer 2

The main fault for the horrible performance is the WinRT wrapper by Microsoft, which has bad performance by default. Always use the following way to select and insert in loops:

await stmt.StepAsync().AsTask().ConfigureAwait(false))

More details: http://blogs.msdn.com/b/andy_wigley/archive/2013/11/21/how-to-massively-improve-sqlite-performance-using-sqlwinrt.aspx