Recreating the tables
CREATE TABLE dbo.Messages(MessageID BIGINT NOT NULL PRIMARY KEY,
MessageThreadID bigint not null,
Deleted bit null,
FromUserID bigint null,
ToUserId bigint null,
Message nvarchar(max) not null,
SendDate Datetime not null,
ReadDate datetime null);
CREATE TABLE dbo.MessageThreads (
MessageThreadID bigint not null PRIMARY KEY,
FromUserHasArchived bit not null,
ToUserHasArchived bit not null,
Created datetime not null,
ThreadStartedBy bigint null,
ThreadSentTo bigint null,
Subject varchar(50) not null,
CanReply bit not null,
FromUserDeleted bit not null,
ToUserDeleted bit not null);
Recreating the Data-ish
DECLARE @message nvarchar(max)
SET @message = REPLICATE(CAST(N'B' as nvarchar(max)),200)
INSERT INTO Dbo.Messages WITH(TABLOCK)
(MessageID,MessageThreadID,Deleted,FromUserID,ToUserId,Message,SendDate,ReadDate)
SELECT TOP(1000000)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
0,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) % 10000,
(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + 1000) % 10000,
@message,
DATEADD(Second,- ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),getdate()),
DATEADD(Second,- ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),getdate())
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;
INSERT INTO dbo.MessageThreads
SELECT TOP(400000)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
0,
0,
DATEADD(Second,- ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),getdate()),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
'bla',
0,
0,
0
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;
UPDATE TOP(20000) Messages
SET ToUserId= 9999
UPDATE TOP(20000) Messages
SET FromUserID = 9999
Querying
With some parts matching your original query:
Using the offset method still shows the spill on the hash match & other problems
SET STATISTICS IO ON; /* And turn on the Actual Excecution Plan */
declare @UserId bigint
set @UserId = 9999
DECLARE @PageNumber int = 1
DECLARE @RowsPerPage int = 11
; WITH cte AS (
SELECT
MT.MessageThreadId,
MT.FromUserHasArchived,
MT.ToUserHasArchived,
MT.Created,
MT.ThreadStartedBy,
MT.ThreadSentTo,
MT.[Subject],
MT.CanReply,
MT.FromUserDeleted,
MT.ToUserDeleted,
LM.MessageId,
LM.Deleted,
LM.FromUserId,
LM.ToUserId,
LM.[Message],
LM.SendDate,
LM.ReadDate
FROM MessageThreads MT
-- join the most recent non-deleted message where this user is the sender or receiver
LEFT OUTER JOIN
(
SELECT RANK() OVER (PARTITION BY MessageThreadId ORDER BY SendDate DESC) r, *
FROM [Messages]
WHERE (FromUserId=@UserId OR ToUserId=@UserId)
AND (Deleted=0)
) LM ON (LM.MessageThreadId = MT.MessageThreadId AND LM.r = 1)
--WHERE MT.ThreadSentTo=@UserId OR MT.ThreadStartedBy=@UserId
)
SELECT
cte.*
FROM cte
ORDER BY SendDate DESC
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;
SQL Server Execution Times: CPU time = 2170 ms, elapsed time =
2402 ms.
A side note, changing the LEFT OUTER JOIN to an INNER JOIN reduces the cpu time & elapsed time to
CPU time = 609 ms, elapsed time = 745 ms.
But that is probably not possible but gives us a first hint at the optimization needed.
As a next step, you could look into removing the RANK() and using MAX() with GROUP BY to work with less columns on the problem part of your query.
SET STATISTICS IO,TIME ON; /* And turn on the Actual Excecution Plan */
declare @UserId bigint
set @UserId = 9999
DECLARE @PageNumber int = 1
DECLARE @RowsPerPage int = 11
; WITH cte AS (
SELECT
MT.MessageThreadId,
MT.FromUserHasArchived,
MT.ToUserHasArchived,
MT.Created,
MT.ThreadStartedBy,
MT.ThreadSentTo,
MT.[Subject],
MT.CanReply,
MT.FromUserDeleted,
MT.ToUserDeleted,
LM.SendDate
FROM MessageThreads MT WITH(INDEX([IX_MessageThreadId_SendDate]))
-- join the most recent non-deleted message where this user is the sender or receiver
LEFT OUTER JOIN
(
SELECT MAX(SendDate) as SendDate,MessageThreadId
FROM [Messages]
WHERE (FromUserId=@UserId OR ToUserId=@UserId)
AND (Deleted=0)
GROUP BY MessageThreadId
) LM ON (LM.MessageThreadId = MT.MessageThreadId)
--WHERE MT.ThreadSentTo=@UserId OR MT.ThreadStartedBy=@UserId
)
SELECT
cte.*,
LM.MessageId,
LM.Deleted,
LM.FromUserId,
LM.ToUserId,
LM.[Message]
FROM cte
LEFT JOIN [Messages] LM
ON cte.MessageThreadID = LM.MessageThreadId
AND cte.SendDate = LM.SendDate
ORDER BY SendDate DESC
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;
This does remove the hash match spill on my end, but the timings are still high
SQL Server Execution Times:
CPU time = 1950 ms, elapsed time = 1223 ms.
We can then remove one of the key lookups by explicitly writing the OR() into two parts:
SET STATISTICS IO,TIME ON; /* And turn on the Actual Excecution Plan */
declare @UserId bigint
set @UserId = 9999
DECLARE @PageNumber int = 1
DECLARE @RowsPerPage int = 11
; WITH cte AS (
SELECT
MT.MessageThreadId,
MT.FromUserHasArchived,
MT.ToUserHasArchived,
MT.Created,
MT.ThreadStartedBy,
MT.ThreadSentTo,
MT.[Subject],
MT.CanReply,
MT.FromUserDeleted,
MT.ToUserDeleted,
LM.SendDate
FROM MessageThreads MT WITH(INDEX([IX_MessageThreadId_SendDate]))
-- join the most recent non-deleted message where this user is the sender or receiver
LEFT OUTER JOIN
(
SELECT MAX(SendDate) as SendDate,MessageThreadId
FROM
(SELECT SendDate,MessageThreadId
FROM [Messages]
WHERE (FromUserId=@UserId )
AND (Deleted=0)
UNION
SELECT SendDate,MessageThreadId
FROM [Messages]
WHERE ToUserId=@UserId
AND (Deleted=0)) AS A2
GROUP BY MessageThreadId
) LM ON (LM.MessageThreadId = MT.MessageThreadId)
--WHERE MT.ThreadSentTo=@UserId OR MT.ThreadStartedBy=@UserId
)
SELECT
cte.*,
LM.MessageId,
LM.Deleted,
LM.FromUserId,
LM.ToUserId,
LM.[Message]
FROM cte
LEFT JOIN [Messages] LM
ON cte.MessageThreadID = LM.MessageThreadId
AND cte.SendDate = LM.SendDate
ORDER BY SendDate DESC
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;
And adding these two indexes:
CREATE INDEX IX_Messages_FromUserId_MessageThreadId_SendDate
ON Dbo.Messages(FromUserId,MessageThreadId,SendDate)
INCLUDE(Deleted)
WHERE Deleted = 0;
CREATE INDEX IX_Messages_ToUserID_MessageThreadId_SendDate
ON Dbo.Messages(ToUserID,MessageThreadId,SendDate)
INCLUDE(Deleted)
WHERE Deleted = 0;
Execution time:
SQL Server Execution Times:
CPU time = 1747 ms, elapsed time = 1050 ms.
This is still not an ideal end result, which is why in the next part we will go over filtering on the messagethread table, with the filter you specified in the question.
Filtering on the messagethread table
Previous created query will be used together with the where clause you specified:
WHERE MT.ThreadSentTo=@UserId
OR MT.ThreadStartedBy=@UserId
Updates for a dataset matching yours:
UPDATE TOP (20000) MessageThreads
SET ThreadSentTo = 9999
FROM MessageThreads;
UPDATE TOP (20000) MessageThreads
SET ThreadStartedBy = 9999
FROM MessageThreads;
Full query with the WHERE filter added
SET STATISTICS IO,TIME ON; /* And turn on the Actual Excecution Plan */
declare @UserId bigint
set @UserId = 9999
DECLARE @PageNumber int = 1
DECLARE @RowsPerPage int = 11
--WHERE MT.ThreadSentTo=@UserId OR MT.ThreadStartedBy=@UserId
; WITH cte AS (
SELECT
MT.MessageThreadId,
MT.FromUserHasArchived,
MT.ToUserHasArchived,
MT.Created,
MT.ThreadStartedBy,
MT.ThreadSentTo,
MT.[Subject],
MT.CanReply,
MT.FromUserDeleted,
MT.ToUserDeleted,
LM.SendDate
FROM MessageThreads MT
-- join the most recent non-deleted message where this user is the sender or receiver
LEFT OUTER JOIN
(
SELECT MAX(SendDate) as SendDate,MessageThreadId
FROM
(SELECT SendDate,MessageThreadId
FROM [Messages]
WHERE (FromUserId=@UserId )
AND (Deleted=0)
UNION
SELECT SendDate,MessageThreadId
FROM [Messages]
WHERE ToUserId=@UserId
AND (Deleted=0)) AS A2
GROUP BY MessageThreadId
) LM ON (LM.MessageThreadId = MT.MessageThreadId)
WHERE MT.ThreadSentTo=@UserId
OR MT.ThreadStartedBy=@UserId
)
SELECT
cte.*,
LM.MessageId,
LM.Deleted,
LM.FromUserId,
LM.ToUserId,
LM.[Message]
FROM cte
LEFT JOIN [Messages] LM
ON cte.MessageThreadID = LM.MessageThreadId
AND cte.SendDate = LM.SendDate
ORDER BY SendDate DESC
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;
The execution plan then looks a lot cleaner, even with the LEFT OUTER JOIN
Execution time:
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 221 ms.
We still do have a residual predicate which can be removed by these two indexes:
CREATE INDEX IX_ThreadSentTo_MessageThreadId
ON MessageThreads(ThreadSentTo,MessageThreadId)
INCLUDE
(
FromUserHasArchived,
ToUserHasArchived,
Created,
ThreadStartedBy,
[Subject],
CanReply,
FromUserDeleted,
ToUserDeleted);
CREATE INDEX IX_ThreadStartedBy_MessageThreadId
ON MessageThreads(ThreadStartedBy,MessageThreadId)
INCLUDE
(
FromUserHasArchived,
ToUserHasArchived,
Created,
ThreadSentTo,
[Subject],
CanReply,
FromUserDeleted,
ToUserDeleted);
But the performance drops from ~200ms elapsed time to ~ 800ms elapsed time when adding the indexes on my end.
Execution plan without added indexes on messagethread (~200ms elapsed time)
Execution plan with added indexes on messagethread (~800ms elapsed time)
https://gist.github.com/timgabrhel/1383ff9362567fdf41ba011dead63ceb
