Windows Phone Local Database tip: Exploring INSERT performance–5 power tweaks

Josué Yeray Julián, (blog in Spanish here), has done some interesting tests on INSERT performance, both on the emulator (like I started doing in my previous blog post) and on two real devices, an HTC Mazaa and Nokia Lumia 800. (Sadly, I do not own a real device – yet!)

In summary,  the following parameters can improve performance for doing INSERTs of 500.000 rows:

1: Set “Max Database Size” on your connection string  to an expected size in advance (default is 32 MB, max is 512 MB on Windows Phone)

2: Increase “Max Buffer Size” to a value higher than the default of 384 KB. On the tested real devices the max of 4096 worked best.

3: Run the INSERT process in a background thread.

4: Remove any Version columns (if only INSERTSs matter, not UPDATEs)

5: Use InsertAllOnSubmit instead of InsertOnSubmit

These tweaks reduced the total processing time on the Nokia Lumia 800 from 29:20 to 16:04!

But - keep in mind that you can use Database first development with the SQL Server Compact Toolbox add-in and then include the database with your application package. You can also use the SqlCeBlukCopy API to insert 1.000.000 rows (twice as many as tested) in 6 seconds on the desktop!

You can read the Microsoft Translator versions of his 2 blog posts here and here.

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.