SQL Server Query Optimization – Unread Messages in All Channels

I have a slow query which I'm trying to tune. Tables in DB (mssql):

• Users, ChatGroups, ChatMessages, ChatGroupChannels, ChatMessageSeenFast

The query:

WITH messages_ranked AS( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.RecipientId ORDER BY p.dATE DESC) AS seqnum FROM ChatMessages p JOIN ChatGroupMemberships as g ON (p.recipientId = g.groupId and g.deleted <> 'true') WHERE g.userId = @userId)
SELECT (
    select count(*)
    from ChatMessages m
    left join ChatGroupChannels ch on ch.groupId = s.recipientId and ch.id = m.channelId
    left join ChatMessageSeenFast f on f.userId = @userId and f.groupId = s.recipientId and f.channelId = ISNULL(ch.Id, 0)
    where m.recipientId = s.recipientId and m.channelId = ISNULL(ch.Id, 0) and m.id > ISNULL(f.lastSeenMsgId, 0)

) as unseenCount, task.flag as taskFlag, task.note as taskNote, s.id as id, s.seen as seenByReader, MAX(s.date) as seenByReaderDate, s.textFlags as messageTextFlags, MAX(s.date) as messageSentDate, s.text as messageText, s.userId as messageSenderId, s.type as messageType, s.recipientId as messageRecipientId, s.recipientType as messageRecipientType, g.name as groupName, g.imageMip64x64 as groupImageMip64,  g.id as groupId, g.groupType as groupType, g.groupFlag as groupFlag, u.profilePictureMip64 as mbyOtherUserProfilePicMip64, u.name as mbyOtherUserName, uu.name as senderName
FROM messages_ranked s 
join ChatGroups g on s.recipientId = g.id
join Users u on u.id = g.coreMemberId1 or u.id = g.coreMemberId2
join Users uu on uu.id = s.userId
left join ChatGroupTasks task on task.groupId = g.Id and task.userId = @userId
WHERE seqnum = 1 and (u.id <> @userId or g.groupType > 0)
group by task.flag, task.note, s.id, s.seen, s.textFlags, s.text, s.userId, s.type, s.recipientId, s.recipientType, g.name, g.imageMip64x64, g.id, g.groupType, g.groupFlag, u.profilePictureMip64, u.name, uu.name
ORDER BY MAX(s.date) DESC 
OFFSET @skip ROWS 
FETCH NEXT @take ROWS ONLY

To break it up:

My first step is to get pairs ChatGroup - LastMessageInThatGroup. I'm using CTE to do that:

WITH messages_ranked AS( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.RecipientId ORDER BY p.dATE DESC) AS seqnum FROM ChatMessages p JOIN ChatGroupMemberships as g ON (p.recipientId = g.groupId and g.deleted <> 'true') WHERE g.userId = @userId)
select g.id as ChatGroupId, s.id as LastMessageInThisGroupId
FROM messages_ranked s 
join ChatGroups g on s.recipientId = g.id
join Users u on u.id = g.coreMemberId1 or u.id = g.coreMemberId2
WHERE seqnum = 1 and (u.id <> @userId or g.groupType > 0)
group by s.id, g.id
ORDER BY MAX(s.date) DESC 

This means that the last message in any group where given user is a member has id 86823 and id of respecive group is 6901.

To explain (u.id <> @userId or g.groupType > 0) – chat groups have following groupTypes:

• 1 to 1 conversation = 0. In this case group.coreMemberId1 is either our user or the other member. group.coreMemberId2 is the other of them.
• Real group conversation = 1+ – here both group.coreMemberId1 and group.coreMemberId2 are equal to id of the user who created this group.

My second step is to join on other tables to get some supplementary information:

join ChatGroups g on s.recipientId = g.id
join Users u on u.id = g.coreMemberId1 or u.id = g.coreMemberId2
join Users uu on uu.id = s.userId
left join ChatGroupTasks task on task.groupId = g.Id and task.userId = @userId

So far so good. My problem lies in the last step – getting count of unread messages by our user across all channels of the conversation:

SELECT (
    select count(*)
    from ChatMessages m
    left join ChatGroupChannels ch on ch.groupId = s.recipientId and ch.id = m.channelId
    left join ChatMessageSeenFast f on f.userId = @userId and f.groupId = s.recipientId and f.channelId = ISNULL(ch.Id, 0)
    where m.recipientId = s.recipientId and m.channelId = ISNULL(ch.Id, 0) and m.id > ISNULL(f.lastSeenMsgId, 0)

) as unseenCount

A group can have any number of channels. To save space a "virtual" channel (referred to as ID = 0) in the query above "exists" for each chat group. As most users don't use channels it would be a waste of resources for me to create a channel for each group by default. Hence all messages by default are written to this special channel. That should explain ISNULL(ch.Id, 0)

ChatMessageSeenFast holds information about:

groupId - userId - channelId (can be null) - lastMessageSeenId - lastUpdateDatetime

The logic behind this step is:

In order to get count of all unseen messages, I will get count of all unseen messages for each channel and then sum it up.

As I'm using s.recipientId from the subquery it slows the entire thing down by a great factor. Highlighted branch is added to the execution plan:

Before channels existed I could get the count of unseen messages by simply joining on ChatMessageSeenFast assigned to our user in the conversation but now that would yield me usneen messages for only one (selected) channel.

I've created indexes suggested my mssms, it sped up the execution but I'm still not happy with the memory needed to process the query and time to do so. How could I speed this up?

To put entire question in a context, I'm using these data to create conversations list in my web app: