This project is read-only.

Performance

Nov 14, 2013 at 5:09 PM
Edited Nov 14, 2013 at 5:12 PM
Hi Andy,

I'm using the sqlite-winrt wrapper for a while now. It helped me to port an application originally using sqlce on pocket pcs. Great work!

Everything is working fine, but the performance was bad when more than a few records are to be fetched from the database.

Some explanation:
I read the records from the database into my viewmodel, and view it using an longlistselector.
Example: Displaying 600 customer records this way on a phone took about 100 seconds until all records where read.
To get this faster my first change was, to add the records in chunks to the longlistselectors data source collection. In the end, I add the first 20 records one by one (to get the first page of the longlistselector filled), and then add them in chunks of 50 records. This way I got the time for loading and displaying all records down to 15 seconds.

For testing purpose I than removed all the code for filling the model, and adding to the longlistselecor. All that was left was while(await statement.StepAsync()) {} This loop took about 14 seconds for the 600 records!

I checked the StepAsync code, and because I'm completely new to the "async pattern", I wrote a second function "StepSync", only difference: removed the async code (return create_async).
I changed the above loop to while(statement.StepSync()) {} and this time the loopo for the 600 records took about 450ms!

Right now I ended up with a helper function which calls StepAsync only every n records, and mostly calls StepSync. This way, the gui is still smoothley updated (even if I call the StepAsync only every 100 records). Loading and displaying this 600 records is now down to 1.5 seconds :)

As I said I'm new to the async pattern and windows phone programming, and I'm not a c++ programmer. My solution works for me, but I hope that you will find the time to look into this and maybe find a more common solution for this. As far as I can say awaiting every single StepAsync is not needed, and has dramatically performance impacts.

Best regards
Thomas
Nov 18, 2013 at 7:30 AM
Hi Thomas;

Can you post your code, to show us how you record your SQLite database.
I m curious to see the initial code (which make so long times)
And by the way, you solution also :)

Sebastien
Nov 18, 2013 at 2:28 PM
I have been doing some work looking at performance and will publish a blog post in the next few days showing some great tips.

The main tip is that when you are calling StepAsync() to loop through the results from a SELECT, do it like this:

while (await statement.StepAsync().AsTask().ConfigureAwait(false) ) { ... }

You will see a SPECTACULAR increase in performance! I will explain why in the blog.

Only thing to be careful off is that when doing this, your calling code ends up executing on some background thread, so you need to be careful after you have got your results - for example, use the Dispatcher to execute subsequent code back on the UI thread.

Andy

Sent from my Windows Phone