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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s