Optimising this query. Relevant for DBA's working on a social network/community type website

Question

I suppose this is quite a common SP present in socialnetworks and community type websites. I have this SP that returns all of a user's friends on their 'friends' page order by those currently online, then alphabetically. It's taking quite a while to load and I am looking to speed it up.

I remember reading somewhere on SO that breaking up multiple joins into smaller result sets might speed it up. I haven't tried this yet but I am curious to see what other recommendations SO might have on this procedure.

DECLARE @userID INT -- This variable is parsed in
DECLARE @lastActivityMinutes INT

SET @lastActivitytMinutes = '15'

SELECT 
    Active = CASE WHEN DATEDIFF("n", b.LastActivityDate ,GETDATE()) < @lastActivityMinutes THEN 1 ELSE 0 END, 
    a.DisplayName, a.ImageFile, a.UserId, b.LastActivityDate
FROM 
    Profile AS a 
     INNER JOIN aspnet_Users as b on b.userId = a.UserId  
     LEFT JOIN Friend AS x ON x.UserID = a.UserID
     LEFT JOIN Friend AS z ON z.FriendID = a.UserID
WHERE   ((x.FriendId = @userID AND x.status = 1) -- Status = 1 means friendship accepted
     OR (z.UserID = @userID AND z.Status = 1))
GROUP BY a.userID, a.DisplayName, a.ImageFile, a.UserId, b.LastActivityDate
ORDER BY Active DESC, DisplayName ASC

I am not sure how to clip in my execution plan but the main bottle neck seems to be occurring on a MERGE JOIN (Right Outer Join) that's costing me 29%. At various stages, Parallelism is also costing 9%, 6%, 5% and 9% for a total of 29% as well.

My initial thoughts are to first return the JOINED results from the Profile and aspnet tables with a CTE and then do LEFT JOINS to the Friends table.

Answer 1

You are joining Friend twice, using a LEFT JOIN, then you are removing the NULL's returned by the LEFT JOIN by WHERE condition, then using GROUP BY to get rid on distincts.

This is not the best query possible.

Why don't you just use this:

SELECT  Active = CASE WHEN DATEDIFF("n", b.LastActivityDate ,GETDATE()) < @lastActivityMinutes THEN 1 ELSE 0 END,
        a.DisplayName, a.ImageFile, a.UserId, b.LastActivityDate
FROM    (
        SELECT  FriendID
        FROM    Friends
        WHERE   UserID = @UserId
                AND status = 1
        UNION
        SELECT  UserID
        FROM    Friends
        WHERE   FriendID = @UserId
                AND status = 1
        ) x
INNER JOIN
        Profile AS a 
ON      a.UserID = x.FriendID
INNER JOIN
        aspnet_Users as b
ON      b.userId = a.UserId
ORDER BY
        Active DESC, DisplayName ASC