This query runs in ~21 seconds (execution plan):
select
a.month
, count(*)
from SubqueryTest a
where a.year = (select max(b.year) from SubqueryTest b)
group by a.month
When the subquery is replaced with a variable, it runs in <1 second (execution plan):
declare @year float
select @year = max(b.year) from SubqueryTest b
select
month
, count(*)
from SubqueryTest where year = @year group by month
Judging from the execution plan, the "select max..." sub-select is run for each of the millions of rows in "SubqueryTest a:, which is why it takes so long.
My question: Since the sub-select is scalar, deterministic and not correlated, why doesn't the query optimizer do what I did in my second example and run the subquery once, store the result, then use it for the main query? I'm sure there's just a hole in my understanding of SQL Server, but I'd really like help filling it - a couple hours with google haven't helped.
The table is just over 1gb with almost 28 million records:
CREATE TABLE SubqueryTest(
[pk_id] [int] IDENTITY(1,1) NOT NULL
, [Year] [float] NULL
, [Month] [float] NULL PRIMARY KEY CLUSTERED ([pk_id] ASC))
CREATE NONCLUSTERED INDEX idxSubqueryTest ON SubqueryTest ([Year] ASC)
Year
as float. Sorry, no, that makes sense for Stardates. ButMonth
as float? Really buffles me. – ypercubeᵀᴹ Jan 22 '14 at 23:42