CODEWITHSUNDEEP
sqlsql-server
Dan Burgener
Dan Burgener

Reputation: 836

SQL query takes several minutes when using variable in subselect

I have a SQL query that takes forever when using a variable instead of hard coded value. I have found a "better" way to do this query, but just want to understand why this happens.

When running the query using the variable (1 = @DontNeedToCheckSubSubTable), it takes several minutes to return. If I don't use the variable (1 = 0) it returns in 1 second. Why is this?

-- This query has had the actual table/column names changed
DECLARE @DontNeedToCheckSubSubTable AS BIT;
SET @DontNeedToCheckSubSubTable = 0;

SELECT
    COUNT(*)
FROM
    MainTable MT WITH (NOLOCK)
INNER JOIN 
    SubTable ST WITH (NOLOCK) ON ST.MainTable_ID = MT.ID
WHERE   
    EXISTS (SELECT TOP 1 'x' AS [Exists]
            FROM SubSubTable SST WITH (NOLOCK)
            WHERE 1 = @DontNeedToCheckSubSubTable -- 1 = 0 ????
               OR (SST.MainTable_ID = MT.ID));

EDIT: Execution Plan with Variable Execution Plan with Variable Execution Plan without Variable Execution Plan without Variable

Upvotes: 1

Views: 144

Answers (3)

Martin Smith
Martin Smith

Reputation: 453298

Gordon's answer changes the semantics.

However the changed semantics are probably what you need anyway.

In your original query the WHERE EXISTS will evaluate to false when SubSubTable is empty even if @DontNeedToCheckSubSubTable = 1. This is no longer the case in the rewritten version.

You may well find that things can be improved further by simply splitting out the two cases in separate queries though (or adding OPTION (RECOMPILE) if this query is not executed overly frequently). This way SQL Server doesn't need to compile a plan that will work for either case.

An example is below.

Staistics IO results on SQL Server 2016 (logical reads)

+---------------------------------+------------+-----------+----------+-----------+
|                                 | spt_values | WorkTable | WorkFile | WorkTable |
+---------------------------------+------------+-----------+----------+-----------+
| @DontNeedToCheckSubSubTable = 0 |            |           |          |           |
| Original                        |        124 |    30,212 |        0 |         0 |
| Gordon's                        |      5,123 |         0 |        0 |           |
| Gordon's + OPTION (RECOMPILE)   |         42 |         0 |        0 |           |
+---------------------------------+------------+-----------+----------+-----------+
| @DontNeedToCheckSubSubTable =1  |            |           |          |           |
| Original                        |         83 |         0 |        0 |         0 |
| Gordon's                        |         28 |         0 |        0 |           |
| Gordon's + OPTION (RECOMPILE)   |         28 |         0 |        0 |           |
+---------------------------------+------------+-----------+----------+-----------+

For the query

SELECT COUNT(*)
FROM   master..spt_values MT WITH (NOLOCK)
       INNER JOIN master..spt_values ST WITH (NOLOCK)
         ON ST.number = MT.number
WHERE  EXISTS (SELECT TOP 1 'x' AS [Exists]
               FROM   master..spt_values SST WITH (NOLOCK)
               WHERE  1 = @DontNeedToCheckSubSubTable -- 1 = 0 ????
                       OR ( SST.number = MT.number ));

Upvotes: 1

Wendy
Wendy

Reputation: 660

Will it be any difference on query output or performance for these 2 queries?

A.

SELECT COUNT(*)
FROM MainTable MT INNER JOIN
    SubTable ST
    ON ST.MainTable_ID = MT.ID
WHERE (1 = @DontNeedToCheckSubSubTable) OR
  EXISTS (SELECT *
          FROM SubSubTable SST
          WHERE SST.MainTable_ID = MT.ID
         );

B.

SELECT COUNT(*)
FROM MainTable MT INNER JOIN
     SubTable ST
     ON ST.MainTable_ID = MT.ID
WHERE (1 = @DontNeedToCheckSubSubTable) OR
  EXISTS (SELECT TOP 1 'x' AS [Exists]
          FROM SubSubTable SST
          WHERE SST.MainTable_ID = MT.ID
         );

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

That OR throws off all optimization. Move it outside the subquery:

SELECT COUNT(*)
FROM MainTable MT INNER JOIN
     SubTable ST
     ON ST.MainTable_ID = MT.ID
WHERE (1 = @DontNeedToCheckSubSubTable) OR
      EXISTS (SELECT TOP 1 'x' AS [Exists]
              FROM SubSubTable SST
              WHERE SST.MainTable_ID = MT.ID
             );

This should allow the subquery to make use of an index on SubSubTable(MainTable_ID). It is possible that a union all may actually be required to get the optimizer to do the right thing.

I removed the WITH NOLOCK. That option is irrelevant to the question and a bad habit unless really needed.

Upvotes: 3

Related Questions