why is lazy loading faster than prefetching ??? RRS feed

  • Question

  • Hi everybody!

    I am evaluating linq to sql at the moment and experiencing some strange speed issues. (VS 2008, SqlServer 2008, Win 7 Ultimate 64 Bit)
    I have 3 tables: [Sport] with 1 :n realtion to [Competitor] with 1:n relation to [Text].
    In my testscenario there is 1 Sport record, 30 related competitors and 2 texts for each competitor.

    When I load my test-form in my application than the sport record is shown together with all competitors and 1 text-line for each competitor.
    I use a performancecounter which measures the time from the constructor of the form until the form-shown event.

    When I use standard lazy loading it takes around 110ms to open the form and show all data. From the Sql Profiler trace I can see that each record is queried and loaded into my application by a seperate query to the DB. Aha, I thought, lets speed this up by querying the rows together.

    So I set DeferredLoadingEnabled to false and set up some dataloadoptions to fetch all the rows together.
    dlo.LoadWith<Sport>(sp => sp.Competitor);
    dlo.LoadWith<Competitor>(c => c.SportText);
    dlo.AssociateWith<Competitor>(c => c.SportText.Where(t => t.toSportLanguageSN == 1));
    When I run my application now I can see that there are really only two commands performed to the DB to receive all (and only the displayed) data, BUT it takes ~125ms to open the form now.

    I really do not understand why the lazy loading mechanism is faster, can anybody explain this to me please?

    Friday, March 12, 2010 10:54 AM

All replies

  • There can be a number of factors involved, but my guess is network latency may play a role here.

    Using LoadWith to eager-load data is great for 1:1 relationships, but for 1:n it will cause the same data to be retrieved and transferred from the db server to the client multiplied by the total number of records.

    1 sport x 30 competitors x 2 texts = 60 records. If sport has a large record size then the LoadWith eager loading method will cause that field to be returned once for each recoed in the related tables...

    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework: (add-in with new features for the L2S and EF designers in VS2008 and VS2010) (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Friday, March 12, 2010 1:58 PM
  • Ok, I should have said that in my test scenario the win forms test applications runs locally on the Db-Server machine, so network latency should not play a role here.
    But you are right, the speed results should differ when i test it via network connections.
     (That would be a much better test anyway)

    In my example I used this line
    dlo.AssociateWith<Competitor>(c => c.SportText.Where(t => t.toSportLanguageSN == 1));
    to tell the context to load only one of the 2 SportText-rows for each competitor.
    So it was 1 x 30 x 1, but thanks for the info about the cross join overhead.
    By the way, somehow linq was so clever that it used 1 query for the single sport-record, and 1 seperate query for the 1:1 relation between the competitor and the SportText line.  

    So I still do not understand why lazy loading is faster here but I found another approach which seems to work best.
    I used 3 direct sql (ExecuteQuery<.....) commands, one for the single Sport record, the second for the 30 competitors and the third for the 30 SportText rows.
    Then I used the Assign() Method to build the relationships between those records so that the graph is consistent again.
    Obviously a bit more coding is required here but the form opens in ~90ms now.
    Friday, March 12, 2010 2:32 PM