Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Windows Phone Local Database Tip: Exploring DELETE Performance and a “Bug Alert”

DZone's Guide to

Windows Phone Local Database Tip: Exploring DELETE Performance and a “Bug Alert”

· Mobile Zone
Free Resource

Download this comprehensive Mobile Testing Reference Guide to help prioritize which mobile devices and OSs to test against, brought to you in partnership with Sauce Labs.

In the previous installments 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.


image


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.

image


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.

 

Analysts agree that a mix of emulators/simulators and real devices are necessary to optimize your mobile app testing - learn more in this white paper, brought to you in partnership with Sauce Labs.

Topics:

Published at DZone with permission of Erik Ejlskov Jensen, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}