How can I improve the performance of LinqToSql queries that use EntitySet properties?
- by DanM
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 is throwing out a lot of what makes LinqToSql so appealing.
So, I'm wondering if I can somehow get the whole database into RAM and just query that way, then occasionally save it. Is this possible? How? If not, is there anything else I can do to boost the performance besides resorting to doing all the joins manually?
Note: My database in its initial state is about 250K and I don't expect it to grow to more than 1-2Mb. So, loading the data into RAM certainly wouldn't be a problem from a memory point of view.
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
)
create table Customer
(
Id int identity(1, 1) primary key,
OrderId int null references Order (Id)
)
