Using Linq-to-SQL preload without joins

Like many people, I am trying to squeeze the best performance out of my app while keeping the code simple and readable as possible. I am using Linq-to-SQL and am really trying to keep my data layer as decl开发者_Python百科arative as possible.

I operate on the assumption that SQL calls are the most expensive operations. Thus, I try to minimize them in quantity, but try to avoid crazy complex queries that are hard to optimize.

Case in point: I am using DataLoadOptions with my DataContext -- its goal is to minimize the quantity of queries by preloading related entities. (Aka, eager loading vs lazy loading.)

Problem: Linq uses joins to achieve the goal. As with everything, it's a trade-off. I am getting fewer queries, but those joined queries are more complex and expensive. Going into SQL Profiler makes this clear.

So, I'd like an option in Linq to preload without joins. Is this possible? Here's what it might look like:

I have a Persons table, an Items table, and a PersonItems table to provide a many-to-many relationship. When loading a collection of Persons, I'd like to have all their PersonItems and Items eagerly loaded as well.

Linq currently does this with one large query, containing two joins. What I'd rather it do is three non-join queries: one for Persons, one for all the PersonItems relating to those Persons, and one for all Items relating to those PersonItems. Then Linq would automagically arrange them into the related entities.

Each of these would be a fast, firehose-type query. Over the long haul, it would allow for predictable, web-scale performance.

Ever seen it done?

I believe what you describe where three non-join queries are done is essentially what happens under the hood when a single join query is performed. I could be wrong but if this the case the single query will be more efficient as only one database query is involved as opposed to three. If you are having performance issues I'd make sure the columns you are joining on are indexed (you should see no table scans in SQL profiler). If this is not enough you could write a custom stored procedure to get just the data you need (assuming you don't need every column of every object, this will allow you to make use of index seeks which are faster than index scans), or alternately you could denormalise (duplicate data across your tables) so that no joining occurs at all.