Optimization of this request. Relevant for a DBA running on a social network / community website

Question

I assume this is a fairly common SP that is present on social networks and community sites. I have this joint venture, which returns all the friends of the user in the order of their "friends" to those who are online now, and then in alphabetical order. It will take quite a while to download, and I want to speed it up.

I remember reading SO somewhere that breaking multiple compounds into smaller result sets can speed it up. I have not tried this yet, but I am curious to see what other recommendations may 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

, , , , MERGE JOIN (Right Outer Join), 29%. Parallelism 9%, 6%, 5% 9% 29%.

, JOINED aspnet CTE, LEFT JOINS .

Answer 1

Friend , LEFT JOIN, NULL, LEFT JOIN by WHERE, GROUP BY, .

.

:

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