The query below already has indexes based its execution plan but it is still under performant with larger datasets. Are the additional null checks needed and why? Do you see anything that can be done to optimize the overall execution time? The execution plans shows clustered index seeks and no additional indexes are recommended.

WITH L2EDWithSubDupes
                            m1.[Id] AS [OriginalEvent_Id]
                            , m2.[Id] AS [PotentiallyDuplicateEvent_Id]
                            , CASE
                                WHEN (m1.[GrpMessageViewers_Hash] IS NULL AND m2.[GrpMessageViewers_Hash] IS NULL) OR 
                                    (m1.[GrpMessageViewers_Hash] = m2.[GrpMessageViewers_Hash])
                                THEN 1 ELSE 0 END [ViewersEqual]
                            , gc.[HasHardEvidence]                      
                            [ced].[Message] m1 -- m1: master/original
                                INNER JOIN [ced].[Message] m2 ON -- m2: potential duplicate
                                    m1.[GrpChat_Id] = @GrpChatId
                                    AND m2.[GrpChat_Id] = @GrpChatId
                                    AND m1.[UtcTimestamp] = m2.[UtcTimestamp]
                                    AND ((m1.[Sender_GrpParticipant_Id] IS NULL AND m2.[Sender_GrpParticipant_Id] IS NULL)
                                        OR (m1.[Sender_GrpParticipant_Id] = m2.[Sender_GrpParticipant_Id]))
                                    AND ((m1.[MessageText_Hash] IS NULL AND m2.[MessageText_Hash] IS NULL)
                                        OR (m1.[MessageText_Hash] = m2.[MessageText_Hash]))
                                    AND ((m1.[SystemMessageType] IS NULL AND m2.[SystemMessageType] IS NULL)
                                        OR (m1.[SystemMessageType] = m2.[SystemMessageType]))
                                INNER JOIN [ced].[GrpChat] gc ON
                                    m1.[GrpChat_Id] = gc.[Id]
                                LEFT JOIN [ced].[GrpAttachmentName_Hash] anh1 ON
                                    m1.[Id] = anh1.[Message_Id]
                                LEFT JOIN [ced].[GrpAttachmentName_Hash] anh2 ON
                                    m2.[Id] = anh2.[Message_Id]
                                LEFT JOIN [ced].[MessageThread] mt1 ON
                                    m1.[Id] = mt1.[Id]
                                LEFT JOIN [ced].[MessageThread] mt2 ON
                                    m2.[Id] = mt2.[Id]
                                LEFT JOIN [ced].[MessageChange] mc1 ON
                                    m1.[Id] = mc1.[Id]
                                LEFT JOIN [ced].[MessageChange] mc2 ON
                                    m2.[Id] = mc2.[Id]
                                m1.[SourceItem_Id] < m2.[SourceItem_Id]
                                OR (
                                    m1.[SourceItem_Id] = m2.[SourceItem_Id]
                                    AND (
                                        m1.[OrdinalPosition] < m2.[OrdinalPosition]
                                        OR (
                                            m1.[OrdinalPosition] = m2.[OrdinalPosition] 
                                            AND m1.[Id] < m2.[Id]
                            AND m1.[Id] <> m2.[Id]
                            --AND m1.[Id] NOT IN (SELECT [Id] FROM [ced].[MessageDuplicateStatus])
                            --AND m2.[Id] NOT IN (SELECT [Id] FROM [ced].[MessageDuplicateStatus])
                            AND NOT EXISTS (SELECT 1 FROM [ced].[MessageDuplicateStatus] (NOLOCK) WHERE Id = m1.[Id]) --use not exists
                            AND NOT EXISTS (SELECT 1 FROM [ced].[MessageDuplicateStatus] (NOLOCK) WHERE Id = m2.[Id]) --use not exists
                            AND ((mt1.[ReplyTo_MessageThread_Hash] IS NULL AND mt2.[ReplyTo_MessageThread_Hash] IS NULL) 
                                OR (mt1.[ReplyTo_MessageThread_Hash] = mt2.[ReplyTo_MessageThread_Hash]))
                            AND ((mc1.[SourceVersionID] IS NULL AND mc2.[SourceVersionID] IS NULL) OR (mc1.[SourceVersionID] = mc2.[SourceVersionID]))
                            AND ((anh1.[GrpAttachmentName_Hash] IS NULL AND anh2.[GrpAttachmentName_Hash] IS NULL)
                                OR (anh1.[GrpAttachmentName_Hash] = anh2.[GrpAttachmentName_Hash]))
                    L2ED -- ensure that only first message is listed in the OriginalEvent_Id column
                        -- in case we have multiple duplicates 
                            , l2sd.[OriginalEvent_Id]
                            , l2sd.[ViewersEqual]
                            , l2sd.[HasHardEvidence]
                            L2EDWithSubDupes l2sd
                            l2sd.[OriginalEvent_Id] NOT IN 
                                    L2EDWithSubDupes l2sd1

                    INSERT INTO [ced].[MessageDuplicateStatus] (
                        , [DuplicateOf_Message_id]
                        , [Type]
                        , [Request_Guid]
                        , t.[OriginalEvent_Id]
                        , 1 as [Type]
                        , @RequestGuid as [Request_Guid]
                        L2ED t 
                        t.[ViewersEqual] = 1
                        OR t.[HasHardEvidence] = 1
                    OPTION (Label = 'DA_AdoCaseRepository_SQL_L2DExact')
  • \$\begingroup\$ Does your indentation actually look like that? That's rather odd. \$\endgroup\$
    – Reinderien
    Oct 2 at 2:32
  • 2
    \$\begingroup\$ You're going through a whole lot of hoops to characterise messages as duplicates. Can you not just delete the duplicates? \$\endgroup\$
    – Reinderien
    Oct 2 at 2:37
  • 3
    \$\begingroup\$ What is the purpose of this query? (Not just what it does, but why does it do it) \$\endgroup\$
    – Blaž Mrak
    Oct 3 at 9:52
  • 1
    \$\begingroup\$ Please follow our guidelines: codereview.stackexchange.com/help/how-to-ask \$\endgroup\$
    – BCdotWEB
    Oct 4 at 9:14
  • \$\begingroup\$ I don't understand [a query] already has indexes based its execution plan. \$\endgroup\$
    – greybeard
    Oct 5 at 7:51

"Are the additional null checks needed and why?" Nobody knows, only you know about the data. We don't know if these checks are needed or not. They may be useless and slowing you down.

It's hard to advise without knowing the data, and the table structure and what indexes are already present and effectively being used in your query. You need to look at the execution plan carefully, and you could have posted it, along with details about table structure.

Some subselects may have a higher cost, for example:

AND NOT EXISTS (SELECT 1 FROM [ced].[MessageDuplicateStatus] (NOLOCK) WHERE Id = m1.[Id]) --use not exists
AND NOT EXISTS (SELECT 1 FROM [ced].[MessageDuplicateStatus] (NOLOCK) WHERE Id = m2.[Id]) --use not exists

again, depends on indexes and the size of the data. But you could perhaps combine this into one single request:

    SELECT 1
    FROM [ced].[MessageDuplicateStatus] (NOLOCK)
    WHERE Id IN (m1.[Id], m2.[Id])
) --use not exists

in order to avoid a double table scan, in case there is no eligible index found to speed up the query. Not sure this will improve the situation a lot though.

  • 3
    \$\begingroup\$ If the question is lacking details required for answering it, please stte so in a comment. This gives the author the chance to improve the post without invalidating existing answers. \$\endgroup\$
    – Mast
    Oct 4 at 4:05

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