none
DataLoadOptions and generated SQL are problematic with large datasets RRS feed

  • Question

  • To stress test my system I dropped all data from tables and added a lot of dummy data, 250,000 rows into one table in particular that I wanted to really put to the test.  Using DataLoadOptions I included 2 relationship properties and it generated this SQL...

    SELECT TOP (10) [t0].[ID], [t0].[UserID], [t0].[CategoryID], [t0].[VoteCount], [t0].[CommentCount], [t0].[Title], [t0].[Description], [t0].[CreatedAt] 
    , [t0].[UniqueName], [t0].[Url], [t0].[LastActivityAt], [t1].[ID] AS [ID2], [t1].[ShortName], [t1].[Name], [t2].[ID] AS [ID3], [t2].[Username], [t2].[ 
    Password], [t2].[Email], [t2].[CreatedAt] AS [CreatedAt2], [t2].[LastActivityAt] AS [LastActivityAt2] 
    FROM [dbo].[Stories] AS [t0] 
    INNER JOIN [dbo].[Categories] AS [t1] ON [t1].[ID] = [t0].[CategoryID] 
    INNER JOIN [dbo].[Users] AS [t2] ON [t2].[ID] = [t0].[UserID] 
    ORDER BY [t0].[LastActivityAt] DESC 

    This may look ok at first glance but what it's doing is an 2 inner joins on 250k rows AND THEN sorting it, this is nasty and took 53 seconds to run on a dedicated box with a 2.2ghz quad-core CPU and 2GB of RAM on a dedcated HDD.

    I was wondering if there's a way to get it to run this query...

    select * from  
        SELECT TOP(10) id, categoryID, userID 
        FROM Stories 
        ORDER BY Stories.LastActivityAt 
    ) s 
    INNER JOIN Stories ON Stories.ID = s.id 
    INNER JOIN Categories ON Categories.ID = s.CategoryID 
    INNER JOIN Users ON Users.ID = s.UserID 

    This is much more efficient and only takes 25ms to run on the server.  I'd like to find a way to be able to do normal LINQ to SQL queries without all kind of crazy hacks and workarounds to get this to happen.  If not I can figure out a workaround, in fact I already did.  I'm just trying to avoid having to use one of there's a better way to do it.

    Thanks so much,
    Chad


    Wednesday, March 4, 2009 4:51 AM

Answers

  • No, dataloadoptions does its own thing. It is nice if you are retrieving a single (or handful) master records and want to also include a handful of child records. E.g. "get single order and related order details". As soon as multiple tables are involved, or the records are large, or the number of records are large it quickly becomes inefficient due to the joins.

    Multiple roundtrips + Assign or add is another option. E.g.:

    Broker brk = dc.Brokers.Single(b => b.ID == brokerID);  
     
    if (includeAccounts)  
    {  
        IEnumerable<BrokerAccount> bkacc   
           = from bac in dc.BrokerAccounts  
             where bac.BrokerID == brokerID  
             select bac;  
        brk.BrokerAccounts.Assign(bkacc.ToList());  
    }  
     
    //load contact details  
    if (includeContacts)  
    {  
        var bkcon  
           = from bkc in dc.BrokerContacts  
             from per in dc.Persons   
             where bkc.BrokerID == brokerID  
               && per.ID == bkc.ContactPersonID   
             select new {bkc, per};  
       
       foreach (var contact in bkcon)  
        {  
            contact.bkc.Person = contact.per;  
            brk.BrokerContacts.Add(contact.bkc);  
        }  
    }  
     

    Kristofer - Huagati Systems Co., Ltd. - web: www.huagati.com - blog: blog.huagati.com - twitter: twitter.com/KristoferA
    • Marked as answer by Chad Moran Wednesday, March 4, 2009 6:54 AM
    Wednesday, March 4, 2009 6:52 AM
    Answerer

All replies

  • It is not totally clear to me what you're trying to achieve, but your second query is roughly equal to:

    from s in (  
      from sto in dc.Stories  
      orderby sto.LastActivityAt  
      select new { id, categoryID, userID }  
    ).Take(10)  
    join st2 in dc.Stories on s.id equals st2.id  
    join cat in dc.Categories on s.CategoryID equals cat.ID  
    join usr in dc.Users on s.userid equals usr.userid  
    select new { st2, cat, usr }; 

    Kristofer - Huagati Systems Co., Ltd. - web: www.huagati.com - blog: blog.huagati.com - twitter: twitter.com/KristoferA
    Wednesday, March 4, 2009 5:48 AM
    Answerer
  • I'm trying to avoid an anonymous type projection if possible.
    Wednesday, March 4, 2009 5:49 AM
  • Chad Moran said:

    I'm trying to avoid an anonymous type projection if possible.


    select new Story(st2, cat, usr);

    public partial class Story

    {

        public Story(Story story, Category category, User user)

        {

            ...

        }

    }


    Kristofer - Huagati Systems Co., Ltd. - web: www.huagati.com - blog: blog.huagati.com - twitter: twitter.com/KristoferA
    Wednesday, March 4, 2009 6:40 AM
    Answerer
  • Was hoping there was something I didn't know about L2S or DataContext to be able to extent it to take control of the SQL generated but yeah that'll work it was what I already had anyway.

    Thanks Kristof.
    Wednesday, March 4, 2009 6:44 AM
  • No, dataloadoptions does its own thing. It is nice if you are retrieving a single (or handful) master records and want to also include a handful of child records. E.g. "get single order and related order details". As soon as multiple tables are involved, or the records are large, or the number of records are large it quickly becomes inefficient due to the joins.

    Multiple roundtrips + Assign or add is another option. E.g.:

    Broker brk = dc.Brokers.Single(b => b.ID == brokerID);  
     
    if (includeAccounts)  
    {  
        IEnumerable<BrokerAccount> bkacc   
           = from bac in dc.BrokerAccounts  
             where bac.BrokerID == brokerID  
             select bac;  
        brk.BrokerAccounts.Assign(bkacc.ToList());  
    }  
     
    //load contact details  
    if (includeContacts)  
    {  
        var bkcon  
           = from bkc in dc.BrokerContacts  
             from per in dc.Persons   
             where bkc.BrokerID == brokerID  
               && per.ID == bkc.ContactPersonID   
             select new {bkc, per};  
       
       foreach (var contact in bkcon)  
        {  
            contact.bkc.Person = contact.per;  
            brk.BrokerContacts.Add(contact.bkc);  
        }  
    }  
     

    Kristofer - Huagati Systems Co., Ltd. - web: www.huagati.com - blog: blog.huagati.com - twitter: twitter.com/KristoferA
    • Marked as answer by Chad Moran Wednesday, March 4, 2009 6:54 AM
    Wednesday, March 4, 2009 6:52 AM
    Answerer