Advertisements

SQLite for WP7 – Proof of Concept – Improved? (Updated)


Inspired by Dan Ardelean’s SQLite for WP7 Series – Proof of Concept post and the fact that a potential client wants to do a Windows Phone 7 project with SQLite I decided to take a look and see if I can’t find anyway for the solution to be feasible. So I took his initial project, edited the insert so it would insert 19 rows and then added a button to select them back. I also put a rough timer on each of those operations to see what would happen.

The results were not good. Inserting 19 rows took 92(!) seconds and selecting them back took 7.5 seconds.

Looking around on the interwebs I found a lot of comments on poor Isolated Storage performance and chalked it up to that. I finally had some time to look into the issue today what I found gave me hope.

I realized that Dan wasn’t trying to come up with the solution to the SQLite question but merely to prove that it could be done. After digging through the code for the SQLite library (with which I had zero experience) I found this in a method that was being called 23(!)(again) times during my one select statement:

using (var theStore = IsolatedStorageFile.GetUserStoreForApplication()) {    if (flags == SQLITE_ACCESS_EXISTS) {        pResOut = theStore.FileExists(zFilename) ? 1 : 0;        return SQLITE_OK;    }    .    .    .}

When stepping through the code I noticed that getting the IsolatedStorageFile object out of Isolated Storage was an expensive operation (as would it turns out verifying the file exists). So out of curiosity I put the IsolatedStorageFile object into a class level static variable:

private static IsolatedStorageFile theStore = null;

set the value once and tested it out. The results were encouraging. My select statement now only took 1.4 seconds.

It wasn’t great but at least it was a step in the right direction. I then decided to skip verifying that the file exists (for now) just to see what that does to performance:

pResOut = 0;//pResOut = theStore.FileExists(zFilename) ? 1 : 0;

Now I was getting somewhere. 153 milliseconds.

So what does any of this mean? To me this means that running SQLite in IsolatedStorage just might work. Of course the insert still takes forever and a day to execute but the fact I can make a select query go from 7.5s to 153ms just by hacking through the library for a couple hours makes me think that somebody out there somewhere just might be able to make this a feasible solution to Microsoft’s refusal to include relational database support in this version of WP7.
I’ll post the updated code here later (assuming anyone even finds this post).
Update (7/19/2010):

I ran the code again against the updated beta version of the tools and my select now just takes 7ms so obviously performance improvements have been made to the latest version of the platform.

 

Advertisements