Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

LINQ To SQL Query Tuning for Performance Optimization

In my last post:


LinqDataSource And How You Can *Maximize* Roundtrips to Your Database


I discussed how you can easily misuse LINQ To SQL and bring your application to a crawl by causing performance issues via too many roundtrips to the database when accessing lazy-loaded properties. Most people familiar with O/R Mappers will recognize that classic problem.


I continued exploring how one can better tune the database queries and limit database roundtrips this weekend which resulted in the discovery of DataLoadOptions in LINQ To SQL and several posts:



The DataContext in LINQ To SQL has a property called LoadOptions which gives you the ability to fine tune your queries and decide when to prefetch properties and associations that are normally lazy-loaded.


For example, if one wants to grab a Blog Entity and prefetch the Categories Associated with it that are normally lazy-loaded, you have the option of specifying the prefetch via the LoadOptions Property and a Lambda Expression. In this case we are fetching BlogId = 1 and prefetching the Categories for the Blog at the same time:


 

using (BlogDataContext context = new BlogDataContext())
{
DataLoadOptions options
= new DataLoadOptions();
options.LoadWith
<Blog>(c => c.Categories);

context.LoadOptions = options;

Blog blog = context.Blogs.Single<Blog>(c => c.BlogId == 1);

}


 


I discovered one can also do this with the LinqDataSource by hooking into the ContextCreated Event and specifying load options there as such:


 

protected void Page_Init(object sender, EventArgs e)
{
LinqDataSource1.ContextCreated
+= new
EventHandler
<LinqDataSourceStatusEventArgs>
(LinqDataSource1_ContextCreated);
}

void LinqDataSource1_ContextCreated(object sender,
LinqDataSourceStatusEventArgs e)
{
DataLoadOptions options
= new DataLoadOptions();
options.LoadWith
<Blog>(Blog => Blog.Categories);
(e.Result
as BlogDataContext).LoadOptions = options;
}


 


The end result is that via DataLoadOptions you can do some serious query tuning for performance optimization in your applications. The whole process has made me more thrilled about LINQ To SQL than ever before. Awesome stuff!


 


by David Hayden


 

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

5 Responses to LINQ To SQL Query Tuning for Performance Optimization

  1. Jostein says:

    While LINQ seems cool and all that, in cases like this (poor implementation or not) I can’t really get too exited about this.

    Let me explain: You just wrote 9 lines of code to make sure LINQ used one join instead of N seperate queries.

    While I agree that mean that the main point of O/R-mapping should be to make your code more structured and clean, you have to consider at what expense this is achieved.

    If this is the way you will need to handle things with LINQ, I’m almost wondering if using regular SQL wont be a better, simpler, cleaner, and more compact solution with all the code in -one- place.

    Having to declare a delegate and handle a event to specify how a single query should be performed does NOT impress me. Especially not if this is all in the UI-layer where everything will be handled as text-data anyway.

    Am I the only one worried about these things?

  2. FransBouma says:

    Darn, I posted the complete wrong url in my first post. Here is a better one:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1968984&SiteID=1&mode=1

  3. FransBouma says:

    Strange isn’t it? I mean, they already had spans in objectspaces, and it’s not as if it’s rocketscience to optimize the living daylights out of graph fetching in the first place.

  4. David Hayden says:

    Frans,

    I knew it had to be too good to be true. I just duplicated the scenario and there the nasty multiple queries and database roundtrips resurface again :) This is the kind of thing many new developers won’t catch and will put into production wondering why performance has gone to hell.

    As the post says, this may be built in as I am not sure how to tweak things to get better results. This will take some playing with :)

  5. FransBouma says:

    Don’t get too excited:
    http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=1960584&SiteID=1

    For one, I can’t get it to work at all as in: it creates a truckload of queries. What’s worse: from that thread you’ll learn they have implemented it rather poorly.

    Oh well. :)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>