Multiple sub queries becomes slow

In my project I retrieve all contacts (around 1300) using LinqToSql. In the database the contacts table (tblContacts) is associated with the tblContactsTypes and also with tblContactsChildren.

The associations are:
tblContact.IDContact > tblContactsTypes.IDContact
tblContact.IDContact > tblContactsChildren.IDContact_Company

Getting only the contacts works great and fast. Getting the contacts and also the contact types OR the contact children also works great and fast. But when I try to get all the contacts and contact types AND contact children at the same time things become really crippled and slow.

In the example below I have set the code up to get all the information i want, but this example doesn't work. If I remove either the contact types or the contact children code, it works.

I have figured out that when I use both contact types and contact children, the contact children is retrieved from the sql server once for every contact. If I switch place of the contact types and contact children in the code, it's still the contact children that is requested from the sql server each time.

Why is this happening?

Public Function GetContacts(ByVal SelectedCompanyID As Integer) As List(Of Contact)
        Using DataContext As New DataClassesDataContext
            Dim result = Nothing

                result = (From c In DataContext.tblContacts
                            Where c.IDCompany = SelectedCompanyID
                            Select New Contact With
                                  .IDContact = c.IDContact,
                                  .ContactsTypes =
                                  (From ct In c.tblContactsTypes
                                   Select New ContactsTypes With
                                         .IDContactType = ct.IDContactType,
                                         .IDType = ct.IDType,
                                         .IDContact = ct.IDContact
                                  .ContactsChildren =
                                  (From cc In c.tblContactsChildrens
                                   Select New ContactsChild With
                                         .IDContactChild = cc.IDContactChild,
                                         .IDCompany = cc.IDCompany,
                                         .IDContact_Company = cc.IDContact_Company,
                                         .IDContact_Person = cc.IDContact_Person
            Catch ex As Exception

            End Try

            Return result
        End Using
    End Function

Open in new window

the project runs in Visual Studio 2010 under .NET 4. The database server is a MS SQL 2008 R2.

Let me know if you need more information. I'd even send the whole project to you if you think that could help.
Who is Participating?
CodeCruiserConnect With a Mentor Commented:
Try using the LoadOptions of the DataContext. I would strongly advise you to use stored procedures. I used similarly complex queries in one of my projects and the performance kept going down and eventually I had to remove all such queries and replace these with stored procedures.
McLydAuthor Commented:
Thank you for answering, I started thinking this was imposible to solve.

Unfortunately though, the LoadOptions didn't help. I thought tables that are associated automatically got loaded when accessed through their associated tables!?

Won't using stored procedures cause a lot more data being sent? In Linq I create an object where one contact can inclue lots and lots of contact types and contact children. If I want this in a stored procedure, wouldn't the same contact info be produced for every one of the contact types and contact childrens? Or maybe I'm not thinking clearly here!?
McLydConnect With a Mentor Author Commented:
I have no idea why this problem occurs but I can't afford any more time on it. A totally different solution has been implemented where a very slimmed down list of contacts are fetched (the Contact object in the question up top is a condensed version).

I have requested to have this question closed.
CodeCruiserConnect With a Mentor Commented:
LINQ implements what's called Lazy Loading so associated objects are only loaded when requested. This means that you generate a lot of SQL queries to load your table. On the other hand, stored procedure is compiled sql which executes pretty efficiently and you results are returned straight away.

McLydAuthor Commented:
I can't see any reason why one of the sub-queries qould load from the SQL server for each request when the other sub-query doesn't.

No solution has been found to that problem.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.