Use ConfigureAwait(false) on calls to SqlWinRT async methods to improve INSERT performance up to 145 times, and SELECT performance up to 80 times 

Sample project: SQLitePerformance.zip

I have been seeing a few comments by people complaining of poor performance when executing SELECT statements against a SQLite database using the SQLWinRT wrapper, or when adding new records using multiple consecutive INSERT statements.

With many relational databases, poor performance on INSERT can often be rectified just by wrapping all your operations within a single transaction. If you don’t explicitly declare a transaction when programming against SQLite, the database engine creates an implicit transaction for each statement execution and that has an overhead associated with it, so by wrapping all your INSERTs inside a single transaction, you would expect a performance boost.

With SELECT operations, poor performance is often just down to not having the correct keys defined. If you have a statement such as SELECT * FROM CUSTOMER WHERE NAME = ‘CONTOSO’ and you execute it on a database where you have not defined a key on the NAME column, the only way the database engine can find the required record is to start at the first record and read sequentially down the table until it reaches the end in order to select the rows to return (called a ‘Table Scan’). If you simply want to select all the records in a table (SELECT * FROM CUSTOMER), then you can’t avoid the Table Scan – in fact that’s what you want – so you would expect that there wasn’t very much you could do to improve performance in that case.

Well – that’s the theory. I set about building a simple demo to demonstrate these points, but it didn’t turn out quite how I expected! It turns out that, important though the points already mentioned are, the most important factor BY FAR is how you call the async methods of the SQLWinRT API! Read on…

The Test Program

For my test program, I was inspired by this post on Stack Overflow: How do I improve the performance of SQLite. That post is quite old and is talking about C++ dev, so not directly relevant to Windows Phone or Windows Store app development using managed code, but I liked the test case, which was to take a large dataset from the City of Toronto’s publically available transportation data. Unfortunately, the exact same dataset used in the original post isn’t available anymore, so I couldn’t do a direct comparison, but they did have the Transportation Trips dataset, containing 128982 records that we can first insert into a SQLite database, and then select them out again.

SQLite Bulk Insert

The dataset is in the form of a CSV text file, representing 128982 rows, each containing 8 columns. The code to read the file and extract the values  looks like this:

SQLiteWinRT.Database db;

private async void Button_Start_Click(object sender, RoutedEventArgs e)
{
    int Route_ID;
    int Service_ID;
    int Trip_ID;
    string Trip_Headsign;
    int Direction_ID;
    int Block_ID;
    int Shape_ID;
    bool Wheelchair_Accessible;

    Button_Start.IsEnabled = false;

    /*********************************************/
    /* Open the Database and create the Schema */
    await LoadDatabase();

    Stopwatch sw = Stopwatch.StartNew();
    int n = 0;

    // Prepare statement
    using (var statement = await db.PrepareStatementAsync(
        @"INSERT INTO TTC (id, Route_ID, Service_ID, Trip_ID, 
                           Trip_Headsign, Direction_ID, Block_ID, 
                           Shape_ID, Wheelchair_Accessible) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"))
    {
        // Start transaction
        await db.ExecuteStatementAsync("BEGIN TRANSACTION");

        /*********************************************/
        /* Open input file and import into Database*/
        var inputfile = await Windows.ApplicationModel.Package.Current.
            InstalledLocation.GetFileAsync("trips.txt");
        using (var inputStream = await inputfile.OpenSequentialReadAsync())
        {
            using (StreamReader rdr = 
                new StreamReader(inputStream.AsStreamForRead()))
            {
                while (true)
                {
                    string inputline = await rdr.ReadLineAsync();
                    if (inputline == null)
                        break;

                    // Discard line 0 - header line
                    if (n > 0)
                    {
                        string[] fields = inputline.Split(new char[] { ',' });

                        Route_ID = Int32.Parse(fields[0]); ;
                        Service_ID = Int32.Parse(fields[1]);
                        Trip_ID = Int32.Parse(fields[2]);
                        Trip_Headsign = fields[3];
                        Direction_ID = Int32.Parse(fields[4]);
                        Block_ID = Int32.Parse(fields[5]);
                        Shape_ID = Int32.Parse(fields[6]);
                        Wheelchair_Accessible = fields[7] == "1" ? true : false;

                        /* Bind parameter values and Insert */
                        statement.Reset();
                        statement.BindIntParameterAt(1, n);
                        statement.BindIntParameterAt(2, Route_ID);
                        statement.BindIntParameterAt(3, Service_ID);
                        statement.BindIntParameterAt(4, Trip_ID);
                        statement.BindTextParameterAt(5, Trip_Headsign);
                        statement.BindIntParameterAt(6, Direction_ID);
                        statement.BindIntParameterAt(7, Block_ID);
                        statement.BindIntParameterAt(8, Shape_ID);
                        statement.BindIntParameterAt(9, Wheelchair_Accessible ? 1 : 0);

                        await statement.StepAsync();
                    }

                    n++;

                    if (n % 100 == 0)
                    {
                        progressBar.Value = n;
                    }
                }
            }
        }

        // Commit transaction
        await db.ExecuteStatementAsync("COMMIT TRANSACTION");
    }

    db.Dispose();
    db = null;

    var report = String.Format(
        "Inserted {0:d} records in {1:0.00} seconds", 
        n - 1, 
        (double)sw.ElapsedMilliseconds / 1000);
    MessageBox.Show(report);

    Button_Start.IsEnabled = true;
}

private async Task LoadDatabase()
{
    db = new SQLiteWinRT.Database(
        ApplicationData.Current.LocalFolder, "sqliteperf.db");

    await db.OpenAsync();

    string sql = @"
        CREATE TABLE IF NOT EXISTS TTC 
            (id INTEGER PRIMARY KEY, 
                Route_ID INTEGER, 
                Service_ID INTEGER, 
                Trip_ID INTEGER, 
                Trip_Headsign TEXT,
                Direction_ID INTEGER, 
                Block_ID INTEGER, 
                Shape_ID INTEGER, 
                Wheelchair_Accessible BOOL)";

    await db.ExecuteStatementAsync(sql);
}

Using this code, and by commenting out lines here and there as appropriate, we can test three scenarios:

  • Processing the input file but not inserting records in the database (this just gives us the time for the file processing)
  • Uncomment the call to StepAsync() to Insert each record into the database individually
  • Uncomment the lines executing the BEGIN TRANSACTION, END TRANSACTION statements so all records are inserted inside a single transaction

testrunner  Transaction

And the results? Well, not great, to be honest:

Test Total Time (seconds) – 128982 records Insert Time per record (ms)
Process records – no insert 7 -
Insert records individually 6307 49
Insert records within a containing transaction 3177 25

Although wrapping all the inserts into a single transaction halves the time it takes to insert the 128982 records compared to separate operations (where the database engine will activate an implicit transaction for every insert), we are still only getting 25ms per insert, or 40 operations per second. That’s not too impressive.

Trying ConfigureAwait(false)

Those performance figures are disappointing and make bulk insert operations in a Windows Phone or Windows Store app impractical. However, look what happens when we make a subtle change to the main loop of code:

Stopwatch sw = Stopwatch.StartNew();
int n = 0;

// Prepare statement
using (var statement = await db.PrepareStatementAsync(
    @"INSERT INTO TTC (id, Route_ID, Service_ID, Trip_ID, Trip_Headsign, 
                        Direction_ID, Block_ID, Shape_ID, Wheelchair_Accessible) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"))
{
    // Start transaction
    await db.ExecuteStatementAsync("BEGIN TRANSACTION");

    /*********************************************/
    /* Open input file and import into Database*/
    var inputfile = await Windows.ApplicationModel.Package.Current.
        InstalledLocation.GetFileAsync("trips.txt");
    using (var inputStream = await inputfile.OpenSequentialReadAsync())
    {
        using (StreamReader rdr = new StreamReader(inputStream.AsStreamForRead()))
        {
            while (true)
            {
                string inputline = await rdr.ReadLineAsync();
                if (inputline == null)
                    break;

                // Discard line 0 - header line
                if (n > 0)
                {
                    string[] fields = inputline.Split(new char[] { ',' });

                    Route_ID = Int32.Parse(fields[0]); ;
                    Service_ID = Int32.Parse(fields[1]);
                    Trip_ID = Int32.Parse(fields[2]);
                    Trip_Headsign = fields[3];
                    Direction_ID = Int32.Parse(fields[4]);
                    Block_ID = Int32.Parse(fields[5]);
                    Shape_ID = Int32.Parse(fields[6]);
                    Wheelchair_Accessible = fields[7] == "1" ? true : false;

                    /* Bind parameter values and Insert */
                    statement.Reset();
                    statement.BindIntParameterAt(1, n);
                    statement.BindIntParameterAt(2, Route_ID);
                    statement.BindIntParameterAt(3, Service_ID);
                    statement.BindIntParameterAt(4, Trip_ID);
                    statement.BindTextParameterAt(5, Trip_Headsign);
                    statement.BindIntParameterAt(6, Direction_ID);
                    statement.BindIntParameterAt(7, Block_ID);
                    statement.BindIntParameterAt(8, Shape_ID);
                    statement.BindIntParameterAt(9, Wheelchair_Accessible ? 1 : 0);

                    await statement.StepAsync().AsTask().ConfigureAwait(false);
                }

                n++;
                if (n % 100 == 0)
                {
                    Dispatcher.BeginInvoke(()=>progressBar.Value = n);
                }
            }
        }
    }

    // Commit transaction
    await db.ExecuteStatementAsync("COMMIT TRANSACTION");
}

db.Dispose();
db = null;

Dispatcher.BeginInvoke(() =>
    {
        var report = String.Format(
            "Inserted {0:d} records in {1:0.00} seconds", 
            n - 1, (double)sw.ElapsedMilliseconds / 1000);
        MessageBox.Show(report);

        Button_Start.IsEnabled = true;
    });
}

The main – and significant change above – is that the StepAsync() method is now called in a different way:

await statement.StepAsync().AsTask().ConfigureAwait(false);

In addition, the code that ‘touches’ the UI, such as the MessageBox.Show() call, and the code setting the ProgressBar is now called using Dispatcher.BeginInvoke(Action a). This is necessary because the change to how StepAsync() is called has a knock-on effect of causing the code to end up executing on a background thread – I will explain more on this shortly.

What does this do to the timings?:

Test Total Time (seconds) – 128982 records Insert Time per record (ms)
Process records – no insert 7 -
Insert records individually 6307 49
Insert records within a containing transaction 3206 25
Insert individually with ConfigureAwait(false) 2791 21
Insert within a transaction with ConfigureAwait(false) 55 0.43

TransactionWithConfigureAwait

WHOA! What happened there? If you insert records within a transaction and use ConfigureAwait(false), you can achieve insert performance of one insert every 0.43 ms, or 2345 records per second. Now we’re talking! That means the performance of inserting records individually without using ConfigureAwait(false) is more than 144 times slower than the best case.

Why such a dramatic improvement? Well, the Task.ConfigureAwait(bool continueOnCapturedContext) method is used to configure the awaiter used to await the result of the asynchronous operation. An awaiter is an object you don’t see or program directly as a developer, but simply by using the await keyword when calling an asynchronous method, the compiler will generate code that uses an awaiter to handle the suspension and reactivation of your calling method while it waits for some asynchronous operation to complete. When the asynchronous method has completed, the awaiter resumes execution of your code which by default happens on the same context (think ‘thread’) you were on when you called the async method. But if you set ConfigureAwait(false), it doesn’t do that but instead continues on whatever context the async method executed on. There’s a performance advantage to this, which is why the advice to authors of library code is that you should always make async calls within your library code using ConfigureAwait(false) – the caller of your library method can then make the decision on whether to return to their original context by choosing whether or not to use ConfigureAwait(false) on the call to your async library method.

Which is what we are doing here – by setting ConfigureAwait(false) on our call to SqlWinRT:Statement.StepAsync() we are saying “don’t bother continue on the originating context, stay on whatever context you are executing on”. And as you can see, the performance gain is spectacular! To be honest, I was very surprised just how spectacular the performance improvement is. Normally, you would expect a modest performance gain, but I guess that because this call is happening between managed code and a WinRT component, the overhead of ConfigureAwait(true) - the default – is particularly significant.

One consequence of using ConfigureAwait(false) is that our code after the await call resumes on a background thread, so when we want to interact with the UI, such as to set the ProgressBar or to display the message at the end, we have to use the Dispatcher to make sure the code that touches the UI executes back on the UI thread:

Dispatcher.BeginInvoke(() =>
    {
        var report = String.Format("Inserted {0:d} records in {1:0.00} seconds", 
            n, (double)sw.ElapsedMilliseconds / 1000);
        MessageBox.Show(report);

        Button_Start.IsEnabled = true;
    });

If we don’t do that, you get an exception.

SELECT Performance

What about the performance of reading records out of the database? The warnings I gave in the introduction to this piece about having appropriate keys configured if you are doing a SELECT with a filter on a column that is not the primary key are still valid. But even if you are simply reading all the records out of a table, whether you use ConfigureAwait(false) again has a huge impact.

Here’s the code you would probably write to read all the rows in our table and to load them into in-memory objects in an ObservableCollection<T> ready to display on the UI:

private async void Button_Select_Click(object sender, RoutedEventArgs e)
{
    if (db == null)
    {
        db = new SQLiteWinRT.Database(
            ApplicationData.Current.LocalFolder, "sqliteperf.db");

        await db.OpenAsync();
    }


    Stopwatch sw = Stopwatch.StartNew();
    int n = 0;
    Button_Select.IsEnabled = false;

    string sql = @"
        SELECT
            id , 
                Route_ID , 
                Service_ID, 
                Trip_ID, 
                Trip_Headsign,
                Direction_ID, 
                Block_ID, 
                Shape_ID, 
                Wheelchair_Accessible
            FROM TTC";
    var stmt = await db.PrepareStatementAsync(sql);

    ObservableCollection<TransportationTrip> tripCollection =
        new ObservableCollection<TransportationTrip>();

    while (await stmt.StepAsync())
    {
        var trip = new TransportationTrip()
        {
            ID = stmt.GetIntAt(0),
            RouteID = stmt.GetIntAt(1),
            ServiceID = stmt.GetIntAt(2),
            TripID = stmt.GetIntAt(3),
            TripHeadsign = stmt.GetTextAt(4),
            DirectionID = stmt.GetIntAt(5),
            BlockID = stmt.GetIntAt(6),
            ShapeID = stmt.GetIntAt(7),
            WheelchairAccessible = 
                stmt.GetIntAt(8) == 1 ? true : false,
        };

        tripCollection.Add(trip);
        n++;
    }

    var report = String.Format("Selected {0:d} records in {1:0.00} seconds",
        n, (double)sw.ElapsedMilliseconds / 1000);
    MessageBox.Show(report);

    Button_Select.IsEnabled = true;
}

Running this gives the following result:

SELECT-no-Await

2838 seconds! That sucks – big time.

Then do it again, but make these subtle changes:

while (await stmt.StepAsync().AsTask().ConfigureAwait(false))
{
    var trip = new TransportationTrip()
    {
        ID = stmt.GetIntAt(0),
        RouteID = stmt.GetIntAt(1),
        ServiceID = stmt.GetIntAt(2),
        TripID = stmt.GetIntAt(3),
        TripHeadsign = stmt.GetTextAt(4),
        DirectionID = stmt.GetIntAt(5),
        BlockID = stmt.GetIntAt(6),
        ShapeID = stmt.GetIntAt(7),
        WheelchairAccessible = stmt.GetIntAt(8) == 1 ? true:false,
    };

    tripCollection.Add(trip);
    n++;
}

Dispatcher.BeginInvoke(() =>
    {
        var report = String.Format("Selected {0:d} records in {1:0.00} seconds", 
            n, (double)sw.ElapsedMilliseconds / 1000);
        MessageBox.Show(report);

        Button_Select.IsEnabled = true;
    });

Which gives this result:

SELECT-await

That’s some improvement! Comparing the two:

Test Total Time (seconds) – SELECT 128981 records SELECT Time per record (ms) Records per second
SELECT 2838 22 45
SELECT with ConfigureAwait(false) 32.4 0.25 3980

That’s a huge improvement!

[WARNING: The timings quoted here are for running on the emulator. You should expect times on a real device to be very substantially slower.]

Conclusions

Some golden rules emerge from this:

  1. Use ConfigureAwait(false) when calling SqlWinRT async methods, particularly within repetitive looping such as INSERT or SELECT of a significant number of records. Remember that when you do so, your code will not resume on the same context it started on, so take appropriate measures to handle this.
  2. In addition to using ConfigureAwait(false), wrap large numbers of INSERTs in a single transaction to get the best performance.

The last learning from this is not specific to SqlWinRT: if you call async methods on a WinRT component, there is a significant overhead in switching back to the originating context. Try calling such a component using ConfigureAwait(false) - it may yield significant performance benefits.

Lastly – this was a test program. If you ever find yourself trying to read 128000 records into memory, I would suggest you have another think about your app design and whether you really need all those objects in memory Smile .