Best Practices For Local Databases

Mango From Scratch

I’ve recently written two posts about coding for SQL CE in Mango (the next release of Windows Phone).  Sean McKenna, who knows more about this than anyone walking the planet, dropped me an email to remind me about a couple best-practices that I ought to be showing in my examples (and will from now on!)

Version Column

Sean pointed out that one of the easiest performance optimizations you’re ever likely to see is to add a version control column to your class.  Here’s all you have to do:

[Table]
public class Book
{
    [Column( IsPrimaryKey = true )]
    public string BookID { get; set; }

    [Column]
    public string Title { get; set; }

    [Column]
    public string Publisher { get; set; }

    [Column]
    public DateTime PublicationDate { get; set; }

    [Association( OtherKey = "AuthorID" )]
    public EntitySet<Author> BookAuthors { get; set; }

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

 

Doing this will get you as much as a 7 times performance improvement on a single update of 100 rows!  Here’s why (and I steal liberally from Sean’s explanation…)

LINQ-to-SQL is based on optimistic concurrency, which means that possible inconsistencies caused by concurrent users updating the database is checked for only when you submit the transaction (there is no prophylactic record locking).  By default, LINQ to SQL submits your changes to the database query processor, which does the consistency check.

The performance enhancement comes by ignoring the query processor and working directly with the tables.  But this means that there has to be another way to determine if the record has changed since the previous query. Enter the version column; if this has not changed, then the entire transaction is safe. If it has changed, of course, then LINQ to SQL will throw a ChangeConflictException, but it would have done that anyway. 

In short, by adding a version column, only one column needs to be tested, and performance takes a big boost.

INotifyPropertyChanging

LINQ To SQL performs change tracking by keeping the original version of your data entity as well as the modified version.  By doing so, LINQ to SQL can determine which columns to update in the record when the transaction is submitted.

By implementing INotifyPropertyChanging, you provide the DataContext with the information it needs so that it can make a copy of the original.  If you don’t provide this notification then DataContext makes a duplicate of all of your entities, which is very wasteful as few will be changing at any time.

INotifyPropertyChanging is almost identical to the INotifyPropertyChanged interface that most developers know from Silverlight data binding. The difference is that you should fire this event before the property actually changes to let the data context know that it needs to make a copy of the old version.

About Jesse Liberty

Jesse Liberty has three decades of experience writing and delivering software projects and is the author of 2 dozen books and a couple dozen online courses. His latest book, Building APIs with .NET will be released early in 2025. Liberty is a Senior SW Engineer for CNH and he was a Senior Technical Evangelist for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS. He is a Microsoft MVP.
This entry was posted in Data, Essentials, Linq, Mango, Mini-Tutorial, Patterns & Skills, WindowsPhone and tagged . Bookmark the permalink.

11 Responses to Best Practices For Local Databases

  1. Chris says:

    Jesse,

    I know this is an old post, but I thought I would mention something that I ran into. Whenever, I add:

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

    to my [Table] class, the entire program crashes (can’t even “catch” any error) when I implement:

    Context.[Table].DeleteOnSubmit(entity);
    Context.SubmitChanges();

    As soon as I hit the SubmitChanges line, it closes immediately. If I comment out the “version” column, everything starts working again. I have no clue why.

  2. avs says:

    Hi Jesse,

    Thanks for all the great WP7 posts! They’ve helped me out quite a bit–especially with regard to MVVM Light.

    So, while I’m excited about the advent of SQL CE in Mango, I’m a bit curious as to what you recommend that we do until then–in terms of both best practices and pragmatism.

    I’m posing my question with the following assumptions in place (please correct me if you disagree):
    (1) Program against an interface for local data storage
    (2) For the time being, provide concrete implementation using X, Y, and/or Z
    (2) When Mango comes out, switch concrete implementation to SQL CE

    So, for the time being, should we rely on Isolated Storage, SQLite for WP7 (by Dan Ciprian Ardelean), and/or Sterling OODB (by Jeremy Likness)? If it’s a combination of two or more, then which scenarios go with which local storage technology?

    And, last but not least, once Mango is released and we have SQL CE, should we still leverage Isolated Storage for anything?

    Thanks again for all the great info on what’s coming Mango! Looking forward to your hearing your take on what to do about local storage in the interim.

  3. Ian says:

    I must be missing something…

    I thought on WP7 an application can only access files (and databases) in its local store, therefore how it is possible for a row to have been changed by another user/application?

    • Ian,

      Now I’m missing something. Who said anything about another application changing anything on the local store? If you mean INotifyPropertyChanged, remember that the data may have been changed by interacting with a WebService and we want to keep the display updated.

    • There are two scenarios: 1) You have a multithreaded application and each thread can operate on the DB independently. 2) You have an Agent which is running concurrently with your foreground App.

      Note that if you don’t care about concurrency control, you can always mark all your properties with UpdateCheck.Never attribute. If they are all marked with this attribute, you should slightly better performance than even having an explicit version column.

  4. dbj says:

    On SQL CE certainly “…there is no prophylactic record locking” that is true.
    But it exists on SQL SVR 2008 R2 (AFAIK any vaersion).
    It LINQ2SQL is ot using it on the latter, then well … one should not use LINQ2SQL for best possible performance.

    Please, correct me if I am wrong?

Comments are closed.