5

The query below is very slow (running over a minute) and I have narrowed down the issue to the OR operator (...OR (EXISTS (SELECT...).

I used live execution to verify that there is a nested loop join between the tables for the OR statement and then records are joined back to the EmailTable in the execution plan.

Basically, EmailTable is being probed twice.

If I add a hint OPTION (MERGE JOIN), the query is finishing within a second.

Please show me how to re-write this query so that the optimizer chooses a better plan by default.

EmailTable and TeamMembers has clustered index on INS_ID. Statistics on the tables are updated often.

DECLARE @a INT
    ,@b BIT
    ,@c INT
    ,@d INT
    ,@e INT;

SELECT [XYZ].[CNT] AS [C]
FROM (
    SELECT COUNT(1) AS [CNT]
    FROM [dbo].[EmailTable] AS [table1]
    WHERE ([table1].[INS_ID] = @a)
        AND ([table1].[ACTIVE] = 1)
        AND ([table1].[QUEUED_TO_SEND] = @b)
        AND ([table1].[OWNER_USER_ID] <> @c)
        AND (
            ([table1].[OWNER_USER_ID] IN (- 1))
            OR (N'Allusers' = [table1].[VISIBLE_TO])
            OR ([table1].[OWNER_USER_ID] = @d)
            OR (
                EXISTS (
                    SELECT 1 AS [C]
                    FROM [dbo].[TeamMembers] AS [table2]
                    WHERE ([table1].[INS_ID] = [table2].[INS_ID])
                        AND ([table1].[VISIBLE_TEAM_ID] = [table2].[TEAM_ID])
                        AND ([table2].[INS_ID] = [table1].[INS_ID])
                        AND ([table2].[MEMBER_USER_ID] = @d)
                        AND ([table2].[TEAM_ID] = [table1].[VISIBLE_TEAM_ID])
                    )
                )
            )
    ) AS [XYZ];
2
  • 2
    Better if you upload the actual plan brentozar.com/pastetheplan
    – DMDM
    Feb 8, 2017 at 8:33
  • 2
    This is likely an artifact of you using local variables (declaring at the top and running the query). What happens when you use a RECOMPILE hint? Feb 8, 2017 at 8:41

1 Answer 1

1

There are some redundant JOINs between the 2 tables, and using too many OR conditions with nested queries sometimes overwhelms the SQL Query Optimiser.

After cleaning up and organising the code, I've come up with the following (hopefully equivalent) alternative:

DECLARE @a INT
    ,@b BIT
    ,@c INT
    ,@d INT
    ,@e INT;

SELECT SUM(CNT) AS [C]
FROM (
    SELECT COUNT(1) AS [CNT]
    FROM [dbo].[EmailTable]
    WHERE 1 = 1
        AND [ACTIVE] = 1
        AND [INS_ID] = @a
        AND [QUEUED_TO_SEND] = @b
        AND [OWNER_USER_ID] <> @c
        AND (
            [OWNER_USER_ID] IN (- 1)
            OR N'Allusers' = [VISIBLE_TO]
            OR [OWNER_USER_ID] = @d
            )

    UNION ALL

    SELECT COUNT(1) AS [CNT]
    FROM [dbo].[EmailTable] AS [table1]
    WHERE 1 = 1
        AND [table1].[ACTIVE] = 1
        AND [table1].[INS_ID] = @a
        AND [table1].[QUEUED_TO_SEND] = @b
        AND [table1].[OWNER_USER_ID] <> @c
        AND EXISTS (
            SELECT 1 AS [C]
            FROM [dbo].[TeamMembers] AS [table2]
            WHERE [table2].[INS_ID] = [table1].[INS_ID]
                AND [table2].[TEAM_ID] = [table1].[VISIBLE_TEAM_ID]
                AND [table2].[MEMBER_USER_ID] = @d
            )
    ) XYZ
OPTION(RECOMPILE);

Let us know how it works and maybe post the Actual Execution Plan, as some commenters requested.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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