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)