Windows phone LINQ2SQL provider performance issue/optimization

Hi all 🙂

I was looking at application DB performance and found that there is a query that runs for quite a long time.

This is how it looks like:

image

Usual steps for situations like that are:

1. Get a database from device.

C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v7.1\Tools\IsolatedStorageEx
plorerTool>ISETool.exe ts de {product GUID} c:\

2. Open LINQPAD and create a connection to the database via SQL CE 3.5 provider.

SNAGHTML106936de

3. Copy the query from your code to LINQPAD, execute it and have a look at generated SQL code. At that moment probably you will see some issues in SQL code.

image

4. Next step is to see how this SQL code is executed, for that we should copy SQL to SQL Management Studio and execute it with actual execution plan. Don’t forget to substitute SQL
parameters with values from comments. Sorry for the screenshot of russian version of SSMS .

image

Analyzing execution plan is quite interesting and difficult task. I can hardly describe this process in proper way. The simplest thing that we should avoid in most (but not all) cases is table scan which is quite slow. Usually this is fixed by creating an additional index. But anyway I recommend to google a bit on this topic.

You can play a bit with query and indexes and try to execute query again. When performance gets to good level – you should modify your datacontext in code or regenerate it with SQLMetal. If you’ve made some modifications of query, it can be a challenge to make a projection of new SQL to the LINQ query. I can’t give any recommendations. But usually it is rather simple.

 

DBLog.cs – class that helps logging SQL generated by DataContext on WP7