I was reading Scott Guthrie’s excellent series of posts on LINQ when I came to the following post that doesn’t quite work the way you would think and will actually kill the performance of your ASP.NET Web Application:
LINQ to SQL (Part 5 – Binding UI using the ASP:LinqDataSource Control)
When you select the asterisk when configuring the LinqDataSource it only relates to columns not relationships. So, when I select the “*” when configuring the LinqDataSource, the Blog Class for which each Category has a 1:1 relationship will not be loaded during a select. Only all the columns are grabbed, no relationships.
If you then display all the categories in a GridView and then replace the foreignkey BlogId with say the title of the Blog as a Template Column:
<asp:TemplateField HeaderText=“Blog“ SortExpression=“Blog.Title“>
<ItemTemplate>
<%# Eval(“Blog.Title“) %>
</ItemTemplate>
</asp:TemplateField>
You are traversing a relationship which will be demand loaded at that moment, causing another roundtrip to the database for each category being displayed in the GridView. So, what might seem like 1 database roundtrip to populate your GridView will actually be 51 roundtrips to the database if you are displaying 50 categories in the GridView. Checking this out with Sql Server Profiler verifies this quite easily.
If you definitely want to replace those foreign keys with more sensible values, you cannot use that “*”, at least not in Visual Studio 2008 beta 2. You will need to select the columns you want to display including the relationship to avoid all the extra roundtrips.
The above will now only cause 1 rountrip to the database to fill out the GridView.
Of course, since we did not choose “*” we lose the option of enabling edits and deletes via the Visual Designer(s), which brings up a whole other problem. The LinqDataSource could use a few enhancements to make all this a bit more intuitive when released.
by David Hayden
David,
That is actually a more difficult problem, because now you need to fetch data from association tables along the way.
It makes the query more complicated, and the way to specify such constraint is extremely important.
Because it has such an affect on perf.
Ayende,
I would assume that you just traverse the relationships as you mention. I don’t have anything to test and verify, but your conclusion seems logical since that is how you do it for 2.
I figured it out Scott. It was driving me crazy:
http://davidhayden.com/blog/dave/archive/2007/08/03/LinqDataSourceHighPerformanceQueriesDataLoadOptionsAvoidingDatabaseRoundtrips.aspx
I look forward to your more precise take on the solution if it is similar to mine.
My next blog post is going to be covering the DataContext shaping support with LINQ to SQL.
You can use this to override the default lazy load semantics that you might have indicated in your model.
Because this can be configured per DataContext usage, it ends up being pretty flexible. You can configure this both via code as well as when using the LinqDatasource.
Stay tuned to my blog for a post in the next few days on this.
Thanks,
Scott
That is the classic SELECT N+1 in ORM
That is a somewhat reasonable decision to make, since otherwise you would run into the other issue, The Entire Database In Memory.
But how would you handle three levels?
Category.Blog.Author.Name ?