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

Comments are closed.