Solved

Multiple sub queries becomes slow

Posted on 2011-09-23
5
287 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
  • 3
  • 2
5 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 50 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 50 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now