CODEWITHSUNDEEP
sql-server
Michael B
Michael B

Reputation: 7567

Why does using a variable cause the query to never complete?

I have a table of ~10 million bigints offset that are larger than a known constant.

I'd like to know how many numbers are within a range of the constant. (The actual query doesn't really matter). When I use the constant converted to a bigint the performance is acceptable (1 second). When I store the constant in a variable or a parameter the query never finishes. Here is the script to generate a sample table:

IF OBJECT_ID('bigints', 'u') IS NOT NULL
  DROP TABLE bigints;

WITH t
     AS (SELECT *
         FROM  (VALUES(1),(1),(1),
                      (1),(1),(1),
                      (1),(1),(1),
                      (1))f(n))
SELECT num = CONVERT(BIGINT, 123456789012)
             + ABS(BINARY_CHECKSUM(NEWID()))
INTO   bigints
FROM   t a,
       t b,
       t c,
       t d,
       t e,
       t f,
       t g

And here is an example of the failing query:

DECLARE @const BIGINT = CONVERT(BIGINT, 123456789012);

WITH t
     AS (SELECT *
         FROM  (VALUES(1),(1),(1),
                      (1),(1),(1),
                      (1),(1),(1),
                      (1))f(n)),
     tally(n)
     AS (SELECT @const
                + row_number() OVER (ORDER BY (SELECT NULL))
         FROM   t a,
                t b,
                t c,
                t d)
SELECT COUNT(*)
FROM   Tally t
       JOIN bigints b
         ON t.n = b.num

If you replace @const + row_number() with the expression CONVERT(BIGINT, 123456789012) + row_number() the query will complete. Why does the introduction of the variable cause the query to run forever?

This was done on Sql Server 2012 and Sql Server 2014 both cause the same issue.

Upvotes: 0

Views: 756

Answers (1)

Martin Smith
Martin Smith

Reputation: 453047

The Fast plan gets 10,000 rows going into the hash join on bigints with a bitmap filter.

The slow plan gets 10,000 rows going into a nested loops join onto a 10,000,000 table with an intermediate table spool.

10,000 executions against that spool are going to be slow.

It looks like the query optimiser doesn't give itself any choice but to use nested loops though.

Attempting to force the issue with an INNER HASH JOIN hint produces

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Indeed this issue can be seen in much simpler queries by adding a reference to a variable as below (also fails) 

DECLARE @S VARCHAR(1) = '';

SELECT *
FROM   master..spt_values T1
       INNER HASH JOIN master..spt_values T2
                    ON T1.name = (T2.name + @S);

This additional reference to a variable certainly shouldn't prevent a hash (or merge) join on a query with an equality predicate and this is a fixed bug (Relevant KB article - Performance issues occur when the join predicate in your query has outer reference columns in SQL Server 2005 or in SQL Server 2008)

SYMPTOMS

You run a query in Microsoft SQL Server 2005 or in Microsoft SQL Server 2008, and the join predicate in the query has outer reference columns. In this case, you may experience performance issues, and the query cannot be completed.

Note This issue does not occur in Microsoft SQL Server 2000.

CAUSE

The problem that is described in the "Symptoms" section occurs because the database engine cannot generate a merge join or a hash join. Therefore, a loop join is used instead. Using a loop join causes the performance problem.

however you must be running on one of the builds (or later) described in the hotfix link (SQL Server 2005 SP3 CU7, 2008 RTM CU9, 2008 SP1 CU6) and must be running with trace flag 4199 enabled to take advantage of the fix (even up to and including SQL Server 2014).

Other less satisfactory work arounds are on 2008 CU5+ the parameter embedding optimization solves things with OPTION (RECOMPILE).

The following will get recompiled on each invocation treating the variable as though it was a constant and compilation succeeds with the desired faster plan.

DECLARE @const BIGINT = CONVERT(BIGINT, 123456789012);

WITH t
     AS (SELECT *
         FROM  (VALUES(1),(1),(1),
                      (1),(1),(1),
                      (1),(1),(1),
                      (1))f(n)),
 tally(n)
     AS (SELECT @const
                + row_number() OVER (ORDER BY (SELECT NULL))
         FROM   t a,
                t b,
                t c,
                t d)
SELECT COUNT(*)
FROM   Tally t
       INNER HASH JOIN bigints b
                    ON t.n = b.num
OPTION (RECOMPILE);

Or even the following defeats the issue and allows the main join to use hash or merge at the cost of some extra plan complexity.

DECLARE @S VARCHAR(1) = '';

SELECT *
FROM   master..spt_values T1
       INNER JOIN master..spt_values T2
                    ON T1.name = (T2.name + (SELECT MAX(@S)));

Upvotes: 3

Related Questions