Linq to SQL : Optimizing queries with Sql Server Compact Edition

Question

Hi

I've been using Linq to SQL against Sql Server CE.

Database is read only, so I can have several assumptions.

In order to refrain from accessing the file system, my initial approach was to cache needed entities to application memory and using linq to objects against them.

While it works well for limited queries, directly using Linq to SQL is superior than Linq to Objects when joins where needed.

Back to starting point, I want to optimize my performances, my thought now is to enforce loading the entire file to the RAM, and using Linq to SQL against it.

Any thoughts of how do that? Any more ideas?

Ariel

Answer 1

Did you try Linq to DataSet?

you can load everything in memory and you can use Linq query

I have no idea if the join will work like you want

Answer 2

It seems like your problem is sql index/foreign key performance vs. collection iterator performance.

Linq to Datasets does offer a way to preserve relationships (more specifically, ADO.net Datasets have a relationship collection) and can enforce primary key, foreign key, and unique constraints. So, their join performance should be similar to Linq to SQL but will run in memory.

From my understanding of lite sql implementations, transactions can have a big impact on performance, so just starting your connection out with

BEGIN TRANSACTION

could have significant impact if you can leave the connection open for all of your queries. Benchmarking is probably the only sure way to answer this though.