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
    Better if you upload the actual plan brentozar.com/pastetheplan – jesijesi Feb 8 '17 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? – Erik Darling Feb 8 '17 at 8:41
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, privacy policy and cookie policy

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