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

Windows Phone Local Database tip: Exploring multiple UPDATEs and rowversion impact

DZone's Guide to

Windows Phone Local Database tip: Exploring multiple UPDATEs and rowversion impact

Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

According to the Local Database best practices, it is recommended to use a rowversion column in your local database table, if you are performing many updates. This blog post will examine the performance and changes to UPDATE handling that are caused by the presence of a rowversion column.

But first, how to add a rovversion column: If you are using code first, this simple column declaration will suffice:

[Column(IsVersion=true)]
private Binary _version; 

If you are using database first, simply add a rowversion column like so:

ALTER TABLE [Artist]
ADD [Version] rowversion NOT NULL

Let us run the following test code which updates 100 rows with and without a rowversion column. I am using a prepopulated Chinook database, added as en embedded resource to the project. See this blog post for details on how to do this.

//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 arists = db.Artist.Take(100);

                    foreach (var artist in arists)
                    {
                        artist.Name = Guid.NewGuid().ToString();
                    }

                    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());
                }
            }

Now lets us see what the SQL statements executed look like without a rowversion column on the table:

UPDATE [Artist]
SET [Name] = @p2
WHERE ([ArtistId] = @p0) AND ([Name] = @p1)

As you can see, a standard UPDATE statement is executed, including a WHERE clause referring to all columns in the table, in order to trap concurrency exceptions. The time to do 100 updates is about 320 ms.

Now let us add the rowversion column to the Artist table, recreate the DataContext so it contains the Version column, and run the same code. Now the SQL statements look like so, indicating that the query processor is being bypassed:

-- CURSOR BASED INDEX UPDATE [Artist].[PK_Artist] (

--     ArtistId: [99]

--    )

-- EQUALITY CHECK [Version], [System.Byte[]] = [System.Byte[]]

-- [Name] <= [5ad7e0d2-925e-44f7-bcd7-c3bca32de745]

-- AUTOSYNC [Version] <= [System.Byte[]]

The time to do 100 updates is now about 110 ms, a significant improvement from 320 ms. So if you expect to do UPDATEs on your local Windows Phone database, add a rowversion column to your tables!

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

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.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}