Query optimization - unread messages in all channels of a conversation

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 t

Let's try this:

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 = @myId)
SELECT 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
INTO #t
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 = @myId
WHERE seqnum = 1 and (@myId <> u.id 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

SELECT 
(

select sum(m.unseenCount)
from (
 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 = @readerId 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  
) m

) as unseenCount
,s.*
from #t s

StackExchange Database Administrators Q#276091, answer score: 4