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.
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.
PS – I am using the new Mango WP7 developer tools.
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.
I don’t know enough about SQLLite for WP7 to have an opinion on how to balance that with Sterling. I’ve used Sterling a lot and like it a lot and that would be my db story until Mango. After Mango…. I’m not sure. There may still be a role for Sterling (I’m sure Jeremy will be blogging about that and I’ll have back him on Yet Another Podcast to discuss it).
Even with Mango, there is good reason to go to Isolated Storage rather than to any db solution: performance. If you need to store key/value data, then Iso Storage is a much better, much more performant solution.
Cool, thanks for the quick response!
The decision between IsoStore and L2S will most likely be driven by the following factors:
1) What % of the data needs to be resident in memory in order to draw a given page of an application?
2) How are updates to that data structured?
3) How creative will the developer get at trying to optimized a flat-file based approach tailored specifically to their data model?
4) How much can they leverage the ACID guarantees provided by a local RDBMS?
Essentially, it is the same tradeoff that is made for local databases in any application. In-memory access will always be faster, but on a resource constrained device loading 100MB of state into memory is just not feasible. Also, developers willing to invest lots of time in a custom storage solution that meets their exact application needs will outperform a general solution. Turnkey storage solutions like L2S should help to lower the bar for developers to build these kinds of applications.
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.
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?
This posting was specific to implementation on Windows Phone Mango, and not other versions of SQL Server. I apologize for any confusion.