Windows Phone Local Database tip: Batch INSERT performance

You could imagine a scenario, where one of the tables in your local database need to be periodically updated. For Windows Phone Local Database, there is no support for Sync Framework or similar technologies, so you may have to simply add a bunch of rows to your table, based on data retrieved from a web service. This blog post explores how SQL Server Compact on the device does INSERTs, and how batch INSERTs can be tweaked for optimum performance.

Let’s start by using the DebugWriter class from my previous post, and examine what INSERT statements look like. For this test, I have included a copy of the Chinook database with the phone application as an embedded resource, so the test table already contains 275 rows. Then use this code to view INSERT statements.

private void button1_Click(object sender, RoutedEventArgs e)
{
    CleanDatabase();
    using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
    {
        db.LogDebug = true;

        var artist = new Artist();
        artist.Name = Guid.NewGuid().ToString();

        db.Artist.InsertOnSubmit(artist);
        db.SubmitChanges();
    }
}

private static void CleanDatabase()
{
    using (var store = IsolatedStorageFile.GetUserStoreForApplication())
    {
        store.Remove();
    }

    using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
    {
        db.CreateIfNotExists();
    }
}

This results – surprisingly – in the following output:

-- CURSOR BASED INSERT [Artist]
-- [Name] <= [2807fb28-49d7-4a25-8350-e4e40245934a]

-- AUTOSYNC [ArtistId] <= [276]

This indicates that in order to perform INSERTs to the Local Database, the query processor is bypassed (no “INSERT” SQL statement is shown). Instead, the LINQ to SQL implementation on the device uses the SqlCeResultSet and SqlCeUpdateableRecord classes (or equivalent), that allows you to access the database tables without the overhead of the query processor, similar to what I use in the SqlCeBulkCopy library for performing fast INSERTs in SQL Server Compact databases on the desktop. So for INSERTs, the device implementation is already optimized in that respect.

Let’s then look at the speed of “many” INSERTs, in this case 10000. The following code implements this test:

CleanDatabase();

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    List<Artist> artistList = new List<Artist>();

    for (int i = 0; i < 10000; i++)
    {
        var artist = new Artist();
        artist.Name = Guid.NewGuid().ToString();

        artistList.Add(artist);
    }

    db.Artist.InsertAllOnSubmit(artistList);
    System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
    sw.Start();
    db.SubmitChanges();
    sw.Stop();
    System.Diagnostics.Debug.WriteLine(sw.ElapsedMilliseconds);            
}

On my emulator, this takes about 2500 ms for each run. So 2.5 seconds to insert 10.000 rows – no too bad. Let’s see if there are any knobs available in the connection string parameters that will allow us to tweak this. The connection string parameters available for Windows Phone Local Database are documented here.

Inspired by this article by Joao Paulo Figueira, we could try to increase the Max Buffer Size from it’s default value of 384 K to 1024 K.

This can be done in the connection string like this:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString + ";max buffer size=1024"))

And lo and behold, the same process of inserting 10000 rows now takes 1.6 seconds on my emulator, a 36% improvement! (Your mileage may vary, of course).

Note: Increasing above 1024 buffer size had no positive effect.

Hope you found this useful.

Speaking of Data on Windows Phone, I will be reviewing the Windows Phone 7.5 Data Cookbook soon, you can download a sample chapter on Isolated Storage from here.