3

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 

enter image description here

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: enter image description here

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:

enter image description here

6
  • could you please upload your execution plan from the attached picture? - brentozar.com/pastetheplan – NikitaSerbskiy Sep 25 '20 at 15:54
  • @Nikita here brentozar.com/pastetheplan/?id=B1tRfqoBw – Matěj Štágl Sep 25 '20 at 15:55
  • is statistics updated? Estimates on IX_ChatMessages_recipientId_channelId_Id looks very inaccurate. Do you have index on ChatGroupChannels(groupId)? – NikitaSerbskiy Sep 25 '20 at 16:01
  • @Nikita I've rebuild all stats yesterday. Added that index you've mentioned – Matěj Štágl Sep 25 '20 at 16:36
  • do you update stats with fullscan? please show the results of this query SELECT TOP 10 RecipientId, COUNT(1) FROM ChatMessages GROUP BY RecipientId ORDER BY COUNT(1) DESC and the same for ORDER BY COUNT(1) ASC – NikitaSerbskiy Sep 25 '20 at 17:00
3

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
4
  • Execution plan of your query looks really amazing tho, as that branch I've highlighted in my question is now gone! – Matěj Štágl Sep 25 '20 at 17:27
  • @MatějŠtágl you can just drop this temp table after execution (don't try to substitute it with table variable). I just copied your code so I'm not sure what do you mean when write "select s.groupId instead of s.recipientId" :) – NikitaSerbskiy Sep 25 '20 at 17:36
  • @MatějŠtágl now your query is splitted in two selects and the highlighted part should be the second one. But it may look different because now SQL Server may better estimate the amount of rows using data from temporary table – NikitaSerbskiy Sep 25 '20 at 17:41
  • Gotcha. Well I really appreciate all your help. The perf is now much better. Thanks! – Matěj Štágl Sep 25 '20 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.