Multiple sub queries becomes slow

Posted on 2011-09-23
Medium Priority
Last Modified: 2012-05-12
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.
Question by:McLyd
  • 3
  • 2
LVL 83

Accepted Solution

CodeCruiser earned 200 total points
ID: 36593799
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.


Author Comment

ID: 36598031
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!?

Assisted Solution

McLyd earned 0 total points
ID: 36598721
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.
LVL 83

Assisted Solution

CodeCruiser earned 200 total points
ID: 36601932
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.


Author Closing Comment

ID: 36895927
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.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question