How can I improve the performance of LinqToSql queries that use EntitySet properties?

Question

I'm using LinqToSql to query a small, simple SQL Server CE database.

I've noticed that any operations involving sub-properties are disappointingly slow.

For example, if I have a Customer table that is referenced by an Order table, LinqToSql will automatically create an EntitySet<Order> property. This is a nice convenience, allowing me to do things like Customer.Order.Where(o => o.ProductName = "Stopwatch"), but for some reason, SQL Server CE hangs up pretty bad when I try to do stuff like this. One of my queries, which isn't really that complicated takes 3-4 seconds to complete.

I can get the speed up to acceptable, even fast, if I just grab the two tables individually and convert them to List<Customer> and List<Order>, then join then manually with my own query, but this requires a lot of extra code. LinqToSql generates these EntitySet<T> properties automatically--I'd like to use them.

So, how can I improve the performance? For example, are there any DataContext options that would help?

Note: My database in its initial state is only about 250K and I don't expect it to grow to more than 1-2Mb. So, it's not like there are a lot of records.

---

Update

Here are the table definitions for the example I used in my question:

create table Order
(
    Id int identity(1, 1) primary key,
    ProductName ntext null,
    Quantity int null
)

create table Customer
(
    Id int identity(1, 1) primary key,
    OrderId int null references Order (Id)
)

Answer 1

I've not used SQL Server CE with Linq to SQL before, but with such a small DB, my gut tells me the performance issue has more to do with poor query optimization than disk access.

Try getting the SQL query from your Linq to SQL objects to see what might be happening. Perhaps run those queries manually against the CE database to see how they perform.

It's very possible that just adding the correct indexes will solve the problem.

You might also try LinqToSQL Profiler. http://l2sprof.com/