9

I have spent the last couple days searching, watching videos, and I think I've gotten as far as I can get just fumbling my way through. I am looking for more specific direction given my example below.

I have two tables I'm working with. MessageThreads (400k records) & Messages (1M records). Their schemas are shown below.

messages table enter image description here

MessageThreads indexes

https://gist.github.com/timgabrhel/0a9ff88160ebc9e40559e1e10ecc7ee4

Messages indexes

https://gist.github.com/timgabrhel/d649074cbe82016e8a90f918c58c4764

I am trying to improve the performance of our primary "inbox" query. Think of your email provider's inbox. You'll see a list of threads, some new, some read, sorted by date, and also gives you a preview of the most recently sent message, whether it was to or from you. Finally, there is an element of paging on this query. By default, we want 11 items. 10 for the page to display, and +1 to know if there's more on the next page.

For some of our long time users, they can have up to 40K messages.

This query has seen many different forms over the last few days, but this is where I've gotten to. I've given OUTER APPLY a try, but I'm seeing worse execution time & statistics.

SET STATISTICS IO ON; /* And turn on the Actual Excecution Plan */

declare @UserId bigint
set @UserId = 9999

; WITH cte AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY SendDate DESC) AS RowNum, 
        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.*,
    UserFrom.FirstName AS UserFromFirstName, 
    UserFrom.LastName AS UserFromLastName, 
    UserFrom.Email AS UserFromEmail,                  
    UserTo.FirstName AS UserToFirstName, 
    UserTo.LastName AS UserToLastName, 
    UserTo.Email AS UserToEmail  
FROM cte
LEFT OUTER JOIN Users AS UserFrom ON cte.FromUserId=UserFrom.UserId 
LEFT OUTER JOIN Users AS UserTo ON cte.ToUserId=UserTo.UserId 
WHERE RowNum >= 1 
AND RowNum <= 11   
ORDER BY RowNum ASC

Statistics for above query (execution time ~2 seconds in SSMS). This execution time is acceptable, but the statistics feel less than desirable, and even more so upon reviewing the actual execution plan. query stats

The execution plan is linked here https://gist.github.com/timgabrhel/f8d919d5728e965623fbd953f7a219ef

One huge hiccup I've spotted is the 400k row Index Scan on the MessageThreads table. Presumably this is because the primary SELECT X FROM MessageThreads query doesn't have a filter on it. When I apply a predicate to it (uncomment the WHERE from the query), the statistics greatly improve (below), but the time jumps from ~2 seconds to ~18 seconds in SSMS.

query stats 2

The problem area on the query is the MessageThreads predicate

Execution plan https://gist.github.com/timgabrhel/1383ff9362567fdf41ba011dead63ceb

Thank you in advance!

  • 1
    Can you make schema optimizations as well, or are you just looking for query syntax and indexing tweaks? – SQLRaptor Dec 9 '19 at 17:27
4

A few thoughts:

  1. Your WHERE clause needs a supporting index

WHERE MT.ThreadSentTo=@UserId OR MT.ThreadStartedBy=@UserId really needs two indexes to be efficient - one on the ThreadSentTo field, and one on the ThreadStartedBy field. Otherwise, the SQL engine will be performing a full table scan to retrieve the correct threads.

  1. Use OFFSET... NEXT N ROWS ONLY instead of ROW_NUMBER()

Starting in SQL 2012, a new construct was added to SQL Server for handling paging. This works like this:

DECLARE @PageNumber int = 20
DECLARE @RowsPerPage int = 15

SELECT *
FROM MyTable T
INNER JOIN MyDetailTable D
    ON T.MyTableID = D.MyTableID
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

In this case, the query will skip the first 285 ((20-1)*15) rows, and retrieve the next 15 rows. This is a faster paging method than the older RowNumber() filter for normal paging.

| improve this answer | |
4
+100

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:

enter image description here

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.

enter image description here

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

 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

enter image description here

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)

| improve this answer | |
  • thank you for the in depth analysis and guidance. – Tim Gabrhel Dec 17 '19 at 14:25
  • @TimGabrhel no problem, happy to help! Thanks for getting back to me! Let me know if something has to be adapted / considerations / feedback /... . Good luck! – Randi Vertongen Dec 17 '19 at 14:27
1

Existing Index of Message table is not upto mark.

Main Area of concern is 2 Window Function on large table which is not require.

declare @UserId bigint
set @UserId = 9999

DECLARE @PageNumber int = 20
DECLARE @RowsPerPage int = 15

-- In #Temp table define all require column with same data type.
--
Create #Temp Table (MessageId,MessageThreadId,FromUserId,ToUserId
,Deleted,Message,SendDate,ReadDate)


;With CTE as
(
SELECT MessageThreadId,max(MessageId)MessageId
        FROM [Messages] 
        WHERE FromUserId=@UserId  
        AND Deleted=0
        group by MessageThreadId
        union all

        SELECT MessageThreadId,max(MessageId)MessageId
        FROM [Messages] 
        WHERE ToUserId=@UserId
        AND Deleted=0
        group by MessageThreadId

)
    insert into #Temp(mention require column)
    select M.* --- do not use *,mention require column
    From dbo.Message M
    where exists(select 1 from CTE C 
where c.MessageId=M.MessageId 
and c.MessageThreadId=M.MessageThreadId)

    -- In #Temp only MessageThreadId with LM.r = 1 logic
    --if #Temp contains more than 100  record then create CI index MessageThreadId

    SELECT
        --ROW_NUMBER() OVER (ORDER BY SendDate DESC) AS RowNum, 
        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,
        UserFrom.FirstName AS UserFromFirstName, 
    UserFrom.LastName AS UserFromLastName, 
    UserFrom.Email AS UserFromEmail,                  
    UserTo.FirstName AS UserToFirstName, 
    UserTo.LastName AS UserToLastName, 
    UserTo.Email AS UserToEmail 
    FROM MessageThreads MT 
    left join #Temp LM ON (LM.MessageThreadId = MT.MessageThreadId ) 
    LEFT OUTER JOIN dbo.Users AS UserFrom ON LM.FromUserId=UserFrom.UserId 
    LEFT OUTER JOIN dbo.Users AS UserTo ON LM.ToUserId=UserTo.UserId 
        OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
        FETCH NEXT @RowsPerPage ROWS ONLY

According to current query

NONCLUSTERED INDEX [nci_wi_MessageThreads_4AE42CECCF44AA0519F913BAF59A3CFA] ON [dbo].[MessageThreads] not require

ALTER TABLE [dbo].[MessageThreads] ADD  CONSTRAINT [PK_MessageThreads] PRIMARY KEY CLUSTERED 
(
    [MessageThreadId] DESC
)
GO

It should be DESC because mostly you are looking for recent record

Similarly

ALTER TABLE [dbo].[Messages] ADD  CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 
(
    [MessageId] DESC
)
GO


CREATE NONCLUSTERED INDEX [ix_Messages_MessageThreadId] ON [dbo].[Messages]
(
    [MessageThreadId] ASC,
    [ToUserId],
    FromUserId,
    Deleted
)
include(SendDate,ReadDate)
where Deleted=0
GO

I don't think there is any benefit in including NVARCHAR(MAX) like Message.

Am I Right ?

ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [UsersId] ASC
)
GO

In my Script notice , SendDate is not use in predicate.So no index on it. Playing with INT and Index on INT is more safe.

Also this is one important query and where Deleted=0 will be use in most of the query.So it is better to Create Filtered Index on it.

If this improve by Leap and Bound and then with latest Execution Plan we can further improve LEFT OUTER JOIN dbo.Users

| improve this answer | |

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.