Windows Phone Local Database tip: Exploring DELETE performance and a “Bug Alert”

In the previous instalments in this series of Windows Phone Local Database tips, I have been exploring SELECT, INSERT, UPDATE, CREATE DATABASE and Encryption. Now the time has come to have a closer look at DELETE, and investigate how they can be optimized (if possible). I will look at deleting multiple rows in a single call to SubmitChanges.

To test this, I will be using the code below, and as usual use the Chinook sample database.

//New database from embedded resource
using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    if (db.DatabaseExists())
        db.DeleteDatabase();

    db.CreateIfNotExists();
}

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{

    try
    {
        //db.LogDebug = true;

        var list = db.InvoiceLine.Take(100);

        db.InvoiceLine.DeleteAllOnSubmit(list);

        System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
        sw.Start();

        db.SubmitChanges();

        sw.Stop();
        System.Diagnostics.Debug.WriteLine(sw.ElapsedMilliseconds);
    }
    catch (Exception ex)
    {
        System.Diagnostics.Debug.WriteLine(ex.ToString());
    }
}

Running this code using the InvoiceLines table from the Chinook sample database will result in 100 calls to a DELETE statement like this:

DELETE FROM [InvoiceLine] WHERE ([InvoiceLineId] = @p0) AND ([InvoiceId] = @p1) AND ([TrackId] = @p2) AND ([UnitPrice] = @p3) AND ([Quantity] = @p4)
-- @p0: Input Int32 (Size = 4; Prec = 0; Scale = 0) [100]
-- @p1: Input Int32 (Size = 4; Prec = 0; Scale = 0) [19]
-- @p2: Input Int32 (Size = 4; Prec = 0; Scale = 0) [581]
-- @p3: Input Decimal (Size = 19; Prec = 10; Scale = 2) [0.99]
-- @p4: Input Int32 (Size = 4; Prec = 0; Scale = 0) [1]

Notice that all columns are included in the WHERE statement, to check for concurrency conflicts (you can argue if this is really required when deleting). The 100 deletes runs in 320 ms on the emulator.

Now let us try to add a rowversion column to the InvoiceLines table, like we did for the UPDATE testing, and see if this has any effect.

You can also use the new feature in the SQL Server Compact Toolbox, which will allow you to add rowversion columns to all your tables, for the benefit of UPDATE and maybe also DELETE statements.

Let us try to run the same test code again.

Now the SQL statement looks like this, indicating that the query processor is bypassed, unlike in the statements above.

-- CURSOR BASED INDEX DELETE [InvoiceLine].[PK_InvoiceLine] (
--     InvoiceLineId: [1]
--    )
-- EQUALITY CHECK [versioncolumn], [System.Byte[]] = [System.Byte[]]

The time to perform the 100 DELETE statements is now down from 320 ms to under 70 ms, a significant improvement.

BUG ALERT!

If you attempt to take advantage of rowversion columns in an existing database, make sure that the table in question does not have more indexes that cover the primary key columns. If you have that, your SubmitChanges statement will “bomb out” your app.

For example the Album table in Chinook has a PK_Album index and PFK_Album index, that both are indexes on the AlbumId column. DROP the PFK_Album index in this case (you can use the new Index context menu option in the Toolbox to do that).

Hope you found this useful.