Left Outer Join in combination with UNION yielding poor result

[Ramya]

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

[Thomas]

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.

[Ramya]

Thank you Thomas

[S. C.Sharp]

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();

 

[Zoran]

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.