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 declarative 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?