Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
6  
My wonder is why you have Year as float. Sorry, no, that makes sense for Stardates. But Month as float? Really buffles me. –  ypercube Jan 22 at 23:42
5  
Can you supply the execution plans? –  Martin Smith Jan 23 at 1:14
    
@ypercube :) This data behind this came from Access (which is still the front-end). It was migrated from using the Access-to-SQL-Server migration wizard which likes floats. –  CaptainSlock Jan 23 at 15:34
    
@MartinSmith Execution plans added. –  CaptainSlock Jan 23 at 15:34
    
What is the result of moving your sub-query from the where clause into the body of the query as a inner join? select a.month, count(*) from SubQueryTest a join (select max(year) as [year] from SubQueryTest b) as b on a.year = b.year group by a.month –  Mr.Brownstone Jan 23 at 15:42

1 Answer 1

up vote 3 down vote accepted

The slow plan isn't calculating the MAX for each row in the outer query.

In fact it never explicitly calculates it at all.

It gives a plan similar to

WITH CTE
     AS (SELECT TOP(1) WITH TIES *
         FROM   SubqueryTest
         WHERE year IS NOT NULL
         ORDER  BY year desc)
SELECT month,
       count(*)
FROM   CTE
GROUP  BY month 

Slow Plan (Estimated Row Counts)

enter image description here

You have a non covering index on year asc so it scans that backwards to get the rows in the first year (shows as a seek because of the implicit IS NOT NULL predicate).

Unfortunately it doesn't seem to differentiate between TOP 1 and TOP 1 WITH TIES when estimating row counts.

In this case it makes a huge difference. (estimated 2 key lookup vs actual 4,424,803) so you get an inappropriate plan.

Slow Plan (Actual Row Counts)

enter image description here

You could consider adding month into the index on year either as a key or included column to make the index covering. The benefit of adding it as a secondary key column would be that it could then feed into a stream aggregate without an additional sort (though for only 12 distinct values a hash aggregate would be fine anyway).

A non covering index on such a non selective column is really pretty useless for the vast majority of queries. The index is totally ignored by the "fast" plan which ends up doing a parallel scan on the whole table and evaluating the predicate on all 27,445,400 rows (in preference to performing the huge number of lookups).

enter image description here

share|improve this answer
    
Frankly, this seems like a performance/optimizer bug to me. It's allowed to assume a static, stable result from the subquery and cache it, so why doesn't it just always do that, irrespective of the estimated row counts? When is the plan its chosen ever better? –  RBarryYoung Jan 23 at 16:15
    
@RBarryYoung - Well the plan with the variable isn't great either! If there were only a handful of duplicates for the TOP 1 this would be the best plan. The bug to me is that it doesn't look at average selectivity for that column when estimating rows for TOP 1 WITH TIES –  Martin Smith Jan 23 at 16:17
    
Hmmm, this is still confusing/weird. Why I'm looking at the slow plan and some of the "Estimated subtree cost"s are a lot less than the individual IO and CPU costs. Maybe my brain just isn't working today, but that seems impossible to me...? –  RBarryYoung Jan 23 at 16:31
    
@RBarryYoung - Because it is under a TOP 1 so they get scaled down for a row goal. SQL Server estimates that the TOP will stop requesting rows after the first row is received. In fact as the first 4,424,803 rows out of the index scan have the same year it takes many more than that. –  Martin Smith Jan 23 at 16:32
1  
@RBarryYoung - Paul White looks at this specifically in his answer here. Does that help? –  Martin Smith Jan 23 at 16:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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