I come from a background of using ORMs to do data access when developing for the full version of .Net. I'm very use to leveraging the power of these tools to do what I see as an infrastructure concern - repetitive and time consuming code to get data in and out of a persistence store. Now we are writing apps for WP7 and some require the use of databases, specifically sqlite. This required me to return to writing a DAL for several of the applications Rich and I are building.
Obviously the support for a full blown ORM on a phone device probably can't be done and why would you want too? I believe most apps requiring a database are going to have a read only nature (or mostly read with few writes) and have a simple relational model with few tables & constraints. Personally I wouldn't use a database to store a score table or user setting for a game\app, I'd more likely use isolated storage with a serialized object structure.
Rich & I've been porting an iPhone app recently, it makes use of a sqlite database. This is well supported on the iPhone platform and fortunately for us sqlite is also supported for WP7, see here. We decided to see if there was an support on the platform for a simple ORM tool (also known as ORM lite) to help simplify any DAL code we had to write. If you're wondering what an ORM lite is check out Dapper.Net or Massive written by Rob Corney of SubSonic fame.
Unfortunately most of these tools are not going to be used on the platform - they require the use of system.data namespace. But there is one which does support sqlite on WP7 - Vici Project. They have a simple ORM library called CoolStorage. This library is written on top of the sqlite support for WP7. Shown below is how easy it can be to access data in your database along with a screenshot of the database schema. This code reads all the postcodes from a test database into a POCO array:
As you can see I can do the data access in 2 lines of code :) I definitely like the approach and it gives me the ability to populate the model classes with ease. The model here is the PostCode class it is no more than a bindable POCO class with 4 settable properties.
Now I'm to old to expect to get anything for nothing, those 2 lines of code surely must have an associated cost. After all this library is built on top of the WP7 implementation for sqlite database access. The next step was to do some simple tests to compare performance.
We decided to CoolStorage to writing the DAL code explicitly. The first test was to get all the post codes:
CoolStorage
SQL -'SELECT postCode AS Value, district AS PostDistrict, latitude AS Latitude, longitude AS Longitude FROM PostCodes'
Explicit DAL Code - I was a bit rusty writing this :)
SQL - 'SELECT postCode, district, latitude, longitude FROM PostCodes'
I used a simple WP7 app to run this code and produce the following results, what you can see is the explicit DAL is faster than CoolStorage, by an approximate factor of 1.5 to return a recordset of 2821 rows.
CoolStorage average = 322 ms
Explicit DAL average = 253 ms
I negated the first debug output as this includes JIT'ing time for any session classes.
I'm not surprised by the results, as I said you don't get anything for free. What is surprising is my thoughts about moving away from using CoolStorage. Normally I would do anything to improve app performance but right now we aren't in any hurry to swap out the code, why?
We are currently in a UAT phase for the app and unless the performance of the search is flagged up as an issue it won't be changed. It would delay the release and for what, this isn't a trading system...
Obviously the support for a full blown ORM on a phone device probably can't be done and why would you want too? I believe most apps requiring a database are going to have a read only nature (or mostly read with few writes) and have a simple relational model with few tables & constraints. Personally I wouldn't use a database to store a score table or user setting for a game\app, I'd more likely use isolated storage with a serialized object structure.
Rich & I've been porting an iPhone app recently, it makes use of a sqlite database. This is well supported on the iPhone platform and fortunately for us sqlite is also supported for WP7, see here. We decided to see if there was an support on the platform for a simple ORM tool (also known as ORM lite) to help simplify any DAL code we had to write. If you're wondering what an ORM lite is check out Dapper.Net or Massive written by Rob Corney of SubSonic fame.
Unfortunately most of these tools are not going to be used on the platform - they require the use of system.data namespace. But there is one which does support sqlite on WP7 - Vici Project. They have a simple ORM library called CoolStorage. This library is written on top of the sqlite support for WP7. Shown below is how easy it can be to access data in your database along with a screenshot of the database schema. This code reads all the postcodes from a test database into a POCO array:
private IEnumerableGetAllPostCodes() { CSConfig.SetDB(databaseFile, SqliteOption.None); return CSDatabase.RunQuery (viciCoolStorageSql); }
As you can see I can do the data access in 2 lines of code :) I definitely like the approach and it gives me the ability to populate the model classes with ease. The model here is the PostCode class it is no more than a bindable POCO class with 4 settable properties.
Now I'm to old to expect to get anything for nothing, those 2 lines of code surely must have an associated cost. After all this library is built on top of the WP7 implementation for sqlite database access. The next step was to do some simple tests to compare performance.
We decided to CoolStorage to writing the DAL code explicitly. The first test was to get all the post codes:
CoolStorage
SQL -'SELECT postCode AS Value, district AS PostDistrict, latitude AS Latitude, longitude AS Longitude FROM PostCodes'
private void CoolStorageExecute() { var stopwatch = new Stopwatch(); stopwatch.Start(); CSConfig.SetDB(databaseFile, SqliteOption.None); var results = CSDatabase.RunQuery(viciCoolStorageSql); stopwatch.Stop(); Debug.WriteLine("Vici CoolStorage duration: {0} ms, results count - {1}", stopwatch.ElapsedMilliseconds, results.Length); }
Explicit DAL Code - I was a bit rusty writing this :)
SQL - 'SELECT postCode, district, latitude, longitude FROM PostCodes'
private void ExplicitDalExecute() { var stopwatch = new Stopwatch(); stopwatch.Start(); var results = new List<PostCode>(); using (var conn = new SqliteConnection("Version=3,uri=file:" + databaseFile)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = csharpSqliteSql; using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult)) { while (reader.Read()) { var postCode = new PostCode { Value = reader.GetString(0), District = reader.GetString(1), Latitude = reader.GetDouble(2), Longitude = reader.GetDouble(3), }; results.Add(postCode); } } conn.Close(); } } stopwatch.Stop(); Debug.WriteLine("Explicit CSharp Sqlite duration: {0} ms, results count - {1}", stopwatch.ElapsedMilliseconds, results.Count); }
I used a simple WP7 app to run this code and produce the following results, what you can see is the explicit DAL is faster than CoolStorage, by an approximate factor of 1.5 to return a recordset of 2821 rows.
CoolStorage average = 322 ms
Explicit DAL average = 253 ms
I negated the first debug output as this includes JIT'ing time for any session classes.
I'm not surprised by the results, as I said you don't get anything for free. What is surprising is my thoughts about moving away from using CoolStorage. Normally I would do anything to improve app performance but right now we aren't in any hurry to swap out the code, why?
We are currently in a UAT phase for the app and unless the performance of the search is flagged up as an issue it won't be changed. It would delay the release and for what, this isn't a trading system...
I'm using this sqlite library in my wp7 app, and for me performance is critical as I may be loading tens of thousands of smallish objects from the db.
ReplyDeleteORMs-lite may be fine if you don't expect huge datasets. Otherwise, learn the native sqlite API from sqlite.org and use that, there are all sorts of optimizations specific to sqlite that you can use to wring out that last drop of performance, and I'm sure the ORM is not that smart, and you will never know about these optimizations without digging through the sqlite docs.
I appreciate performance is critical for you, but our usage scenario it isn't yet. I also appreciate the comments about sqlite.org and the tweeks available.
ReplyDeleteOut of interest, why are you possibly loading tens of thousands objects, surely some form of pagination solves this problem?
I admit my situation is unusual. In a nutshell, a user may have 10k widgets, but only a small subset are of interest TODAY. The widgets are lazy-loaded, but to figure out today's subset, I need to analyze the "stats" object for each widget.
ReplyDeleteI'll pre-calculate this offline once mango is widespread and I can use background agents, but for now its a load screen and fighting tooth and nail for every millisecond.
So are you using a port of the sqlite code on google code?
ReplyDeleteAnd does an in memory caching pattern not speed up the access?
I guess once you open the database you keep the connection open all the time the app is running.
Yup,
ReplyDeleteYup - but you lose the cache as soon as the app tombstones, trying to preserve it turned out to be too heavy an op and would occasionally throw on tombstone.
Yup
Interest, we built an isolated cache provider for the WP7Contrib so it can survive tombstoning but as you stated reviving the cache requires time. We also have an in memory version which doesn't survive tombstoning.
ReplyDeleteOllie Riches
ReplyDeleteHere is an ORM that works with Sqlite
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx