LinqToSQL query loadwith performance

Question

I found this link which explains my problem and has an answer, but don't seem to be able to make it work.

Here's what I have for DataLoadOptions:

        options.LoadWith<Support>(p => p.PostBase);
        options.LoadWith<Support>(p => p.PostMaterial);
        options.LoadWith<Support>(p => p.PostPosition);
        options.LoadWith<Support>(p => p.PostSize);
        options.LoadWith<Support>(p => p.PostType);
        options.LoadWith<Support>(p => p.Signs);
        options.LoadWith<Support>(p => p.SupportComments);
        options.LoadWith<Support>(p => p.SupportInspections);
        options.LoadWith<Support>(p => p.SupportPhotos);
        options.LoadWith<Sign>(p => p.SignBacking);
        options.LoadWith<Sign>(p => p.SignComments);
        options.LoadWith<Sign>(p => p.SignCondition);
        options.LoadWith<Sign>(p => p.SignDelineator);
        options.LoadWith<Sign>(p => p.SignFace);
        options.LoadWith<Sign>(p => p.SignIllumination);
        options.LoadWith<Sign>(p => p.SignToSignObstructions);
        options.LoadWith<Sign>(p => p.UniformTrafficControlCode);
        options.LoadWith<SignToSignObstruction>(p => p.SignObstruction);

I think that will give a good explanation of my object graph. I'm trying to query for Support objects that match a certain search criteria (perhaps someone wants supports with post type of blah).

If I try just pulling back all Supports, I get about 2200 Supports and it takes 17k queries.

I attempted the grouping solution mentioned in the other question, but I wonder if either I'm doing it wrong or my situation is just too complex. I removed the search criteria and just tried returning all Supports. This results in about 21k queries and pulls back about 3000 Supports. Here is my query:

                var group =
                from support in roadDataContext.Supports
                join sign in roadDataContext.Signs on support.SupportID equals sign.SupportID
                group sign by sign.Support
                into signGroup
                select signGroup;

Am I just missing something simple? Thanks.

Why are you trying to pull back *all that data* on a search query? You should use `LoadWith` after you've *found* the `Support` you're looking for. If you actually need to return a denormalized table as part of your search results, you should use the `Select` operator and return a `x => new { x.PostBase.XXX, etc. }` with the specific data you want to display. Also consider pagination -- 3000 rows is inappropriate no matter how you're fetching the data. — Kirk Woll, Nov 17 '10 at 18:53
I would start from the beginning again: what happens if you remove all the LoadWith's: what is the SQL that is being generated at that time? And after that start tweaking — Pleun, Feb 22 '11 at 17:59

score 2 · Answer 1 · answered Mar 23 '11 at 20:51

We made the same mistake with our L2S data layer. Our load options are ridiculous in some cases. It was a hard lesson learned.

This is known as the SELECT N+1 problem. 1 for the parent entity, and N for the number of associated entities being eager-loaded. You'd expect L2S to just be smart enough and get it all in one giant query, but this is unfortunately not the case. It will create one giant query, which tells it the IDs of the associations to load, then one by one retrieves those associations.

Perhaps the best work-around is to use projection so your LINQ query returns a new object, rather than an entity. For example:

var fooDtos = from foo in db.Foo
              where foo.bar == "What a great example"
              select new fooDTO { FooName = foo.Name, FooBar = foo.Bar };

This query returns an IEnumerable<FooDTO> instead of IQueryable<Foo>. This has two benefits. First of all you're instructing L2S specifically which columns to retrieve, so it doesn't do a SELECT *. Also, you don't need DataLoadOptions anymore because you can query any table you want in the query and select from any table to generate the DTO.

LinqToSQL query loadwith performance

1 Answers1