Improving performance Linq to Sql Compact Edition

Question

Hi I'm writing a WPF client app, using Linq to Sql with Sql Compact edition. The db is relatively small (3MB) and read-only.

Bottom line is that The performance are not as good as I hoped them to be, and I'm looking for tips and practical ways to increase that.

More facts: The schema contains around a dozen of entities with extensive relations between them.

Profiling the app found out that the query is being run quite fast but building the c# Entities is the the process that take the most time (could be up to 8 seconds). Mostly I believe because we have used LoadWith, and the DataContext got no choice but to build the objects graph in memory.

I can provide additional information, if needed.

EDIT:

1. As I mentioned the db is read-only so DataContext is not tracking changes.
2. We are making use of static queries on reoccurring queries. The problem is when the application is initializing and we prefetch many objects to memory to be served as cache.

Thanks for your help.

Ariel

Answer 1

Well, you might find that making use of lazy loading (rather than eager loading) might help increase the performance (i.e. avoid using LoadWith) since the entities won't need memory allocated for the relationship chains (or deep loading of the object graph) and instead they will be populated on demand.

However, you'll need to be focused in your design to support this (otherwise you will simply move the performance bottleneck to become overly "chatty" with regard to SQL statements being executed against the SQL CE database.

The DataContext can also start to bloat (memory) as it tracks changes. You might need to consider your approach to how you use Data Contexts (for instance, you can attach them to new contexts provided the original context has been disposed).

Answer 2

A very simple solution is to use staticly declared compiled linq queries. This is of course not that practical, but it will improve performance as the expression trees will only need to be built once, during compile-time, instead of being dynamically created every time the query is called for execution.

This might help:

http://msmvps.com/blogs/omar/archive/2008/10/27/solving-common-problems-with-compiled-queries-in-linq-to-sql-for-high-demand-asp-net-websites.aspx