[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Multiple sub queries becomes slow

Posted on 2011-09-23
5
Medium Priority
?
296 Views
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

            Try
                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
                                      }
                                  ).ToList,
                                  .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
                                      }
                                  ).ToList
                               }
                           ).ToList
            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.
0
Comment
Question by:McLyd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 83

Accepted Solution

by:
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.

http://msdn.microsoft.com/en-us/library/Bb386917(v=VS.100).aspx
0
 

Author Comment

by:McLyd
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!?
0
 

Assisted Solution

by:McLyd
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.
0
 
LVL 83

Assisted Solution

by:CodeCruiser
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.

0
 

Author Closing Comment

by:McLyd
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.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

649 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