Left Outer Join in combination with UNION yielding poor result

5 posts, 0 answers
  1. Ramya
    Ramya avatar
    8 posts
    Member since:
    Nov 2009

    Posted 23 Mar 2010 Link to this post

    I have used LINQ to SQL to build a query that includes a left outer join, a select sub query and a union. Though this does not throw any logical exception the query execution always timed out. While debugging I noticed that the foreach loop on the result set causes to spew a lot of select statements and other execution statements in the output window, which is slowing down the process and hence causes time out.

    I had to use a stored procedure instead to get it working.

    Here is the linq to sql expression that I used.

                        var recentFriendMessages = (from messages in scope.Extent<Message>()
                                                 join groupInfo in
                                                     (from grpMessages in scope.Extent<GroupsMessage>()
                                                      join grps in scope.Extent<Group>() on grpMessages.GroupID equals grps.Id
                                                      select new { GroupID = grpMessages.GroupID, MessageID = grpMessages.MessageID, GroupName = grps.Name })
                                                 on messages.Id equals groupInfo.MessageID into messageGroups
                                                 from message in messageGroups.DefaultIfEmpty()
                                                 join users in scope.Extent<User>() on messages.UserID equals users.Id
                                                 where messages.UserID != userID
                                                 && usersFriendIds.Contains(messages.UserID)
                                                 orderby messages.Posted descending
                                                 select new
                                                 {
                                                     users.UName,
                                                     UserID = users.Id,
                                                     GroupID = message == null ? -1 : message.GroupID,
                                                     messages.Posted,
                                                     GroupName = message == null ? "" : message.GroupName,
                                                     MessageID = messages.Id,
                                                     messages.Title
                                                 }).Take(3);
                        long messageIdFiller = 0;
                        string messageTitleFiller = string.Empty;
                        var recentFriendsGroupMembers = (from usrGrps in scope.Extent<UsersGroup>()
                                                         join users in scope.Extent<User>() on usrGrps.UserID equals users.Id
                                                         join grps in scope.Extent<Group>() on usrGrps.GroupID equals grps.Id
                                                         where usrGrps.UserID != userID && usersFriendIds.Contains(usrGrps.UserID)
                                                         orderby usrGrps.Date1 descending
                                                         select new
                                                         {
                                                             users.UName,
                                                             UserID = users.Id,
                                                             usrGrps.GroupID,
                                                             Posted = usrGrps.Date1,
                                                             GroupName = grps.Name,
                                                             MessageID = messageIdFiller,
                                                             Title = messageTitleFiller
                                                         }).Take(3);


                        var result= recentFriendMessages.Union(recentFriendsGroupMembers);
                        result = result.OrderByDescending(s => s.Posted);
                        result = result.Take(3);
                        foreach (var res in result) //as soon as the this statement is hit, output window gets filled with a large chunk of statements
                            {
                            }



    Please suggest if this query can be optimized.

    Thanks,
    Ramya
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 24 Mar 2010 Link to this post

    Hello Ramya,

    quite a complex query, I need a bit time to look into this. What I can tell you is that Union() operations are not pushed to the server, so that will be done in memory, causing at least 2 queries. Also, you might want to consider simplifying it a bit by using references and following them in the queries; this can avoid the joins that you otherwise need.

    Regards,
    Thomas
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  3. DevCraft banner
  4. Ramya
    Ramya avatar
    8 posts
    Member since:
    Nov 2009

    Posted 24 Mar 2010 Link to this post

    Thank you Thomas
  5. S. C.Sharp
    S. C.Sharp avatar
    6 posts
    Member since:
    Apr 2009

    Posted 19 May 2010 Link to this post

    What release of OpenAccess are you two talking about?  My issue is with a project that was deliveried with 09Q2 but now we find that we have a problem with Union()s I was doing in 09Q3 (I have not even tried 2010 Q1.. and can't on this project).

    The 3 unions of Actor + From + To now fails in 09Q3 (last line below) with "The last accepted query piece is not from the this query".
     (in Telerik.OpenAccess.Query.QueryContext.createQuery() line 1499)

     

    var Actorlinq = (from rteStep in scope.Extent<TblRouteTemplate>()

     

     

    where rteStep.RouteTemplateID == templateID

     

     

    select new { typeID = rteStep.ActorGroupTypeID }).Distinct();

     

     

     

    var Fromlinq = (from rteStep in scope.Extent<TblRouteTemplate>()

     

     

    where rteStep.RouteTemplateID == templateID

     

     

    select new { typeID = rteStep.FromGroupTypeID }).Distinct();

     

     

     

    var Tolinq = (from rteStep in scope.Extent<TblRouteTemplate>()

     

     

    where rteStep.RouteTemplateID == templateID

     

     

    select new { typeID = rteStep.ToGroupTypeID }).Distinct();

     

     

    var Unioned = Actorlinq.Union(Fromlinq).Union(Tolinq).Distinct();

     

  6. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 25 May 2010 Link to this post

    Hello Scott CSharp,

    The Union query call is not currently processed at the database server. It is processed on the client side instead, but this is only part of the later versions of  OpenAccess. In order to get this working in the version that you are using would be to explicitly call the ToList method to execute the Union operation on the clint side.

     

    var Unioned = Actorlinq.ToList().Union(Fromlinq.ToList()).Union(Tolinq.ToList()).Distinct();




    Greetings,
    Zoran
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
Back to Top
DevCraft banner