Why is my CTE faster than one part of itself?

Question

I created a CTE in SQL Server today that is really baffling me. It works spectacularly, but I do not understand how. I can't provide the exact query, but here's an approximation:

with a as (

    select
         dt.[Date]
        ,p.product_no
        ,[date_start] = cast( RIGHT( MIN( RIGHT( '000000' + cast( p.price as varchar ), 6) + convert( varchar, p.date_start, 112 ) ), 8 ) as date )
        ,[date_expire] = cast( RIGHT( MIN( RIGHT( '000000' + cast( p.price as varchar ), 6) + convert( varchar, isnull(p.date_expire,'1/1/9999'), 112 ) ), 8 ) as date )
        ,[price] = MIN(p.price)
    from pricing p with (nolock) 
        inner join datetable dt with (nolock) on dt.[Date] between p.date_start and isnull(p.date_expire,getDate())
    group by 
         dt.[Date]
        ,p.product_no

), b as (

    select distinct
         a.date
        ,a.product_no
        ,[date_start] = iif( isnull(LAG(a.price) OVER (PARTITION BY a.product_no ORDER BY a.date),-1) <> a.price, a.date, null )
        ,[date_expire] = iif( isnull(LEAD(a.price) OVER (PARTITION BY a.product_no ORDER BY a.date),-1) <> a.price, a.date, null )
        ,a.price
    from a

)

select distinct
     b.product_no
    ,[date_start] = coalesce( b.date_start, LAG(b.date_start) OVER (PARTITION BY b.product_no ORDER BY b.date) )
    ,[date_expire] = coalesce( b.date_expire, nullif(LEAD(b.date_expire) OVER (PARTITION BY b.product_no ORDER BY b.date), cast( getDate() as date ) ) )
    ,b.price
from b
where b.date_start is not null
   or ( b.date_expire is not null 
        and b.date_expire <> cast( getDate() as date ) )
order by 1,2;

This pricing table includes prices for a couple hundred products over 12 years or so and, many times, there are multiple prices active at the same time. These may overlap completely or partially. What I'm trying to do is create a clean table showing the lowest price at any given point in time. I seem to have succeeded.

What I don't understand is this: if you run the definition of a, that first select, it produces millions of rows. So many, in fact, that I've never let it run all the way through. It was clearly well on its way to taking a long time and eating up tons of memory. If I run the whole thing, though? That takes about 25 seconds and produces, as you would expect, only a few thousand rows.

So how does SQL Server pull this off? It's clearly not executing each step in its entirety then moving on to the next step. I wish to understand this voodoo. If this goes some way toward helping me understand when a CTE would be faster than a series of temp tables and vice versa, that would be great. Thanks in advance!

Answer 1

SQL is a declarative, not a procedural, language. When you use a CTE as part of declaring what you want, you are not writing a procedure to get your result. The server's query planner, in your query's case, notices that the date_start and date_expire columns you use for filtering your final result refer back to columns in your original table. It filters the original table, not the generated result of the CTE.

Query planner optimization software is the result of decades of work by thousands of very smart programmers. It does some amazing things in figuring out clever ways to access tables.

For many of us, SQL is the first declarative language we encounter. We're used to describing the result we want by telling the computer how to get it. But we need a different mindset for SQL.

The comments to your question talk about ways to understand what the query plan does for a particular query. If a query is too slow for your app, you can dig into the plan and figure out how to persuade your server to use a faster plan. That often involves adding an index.

Read Marcus Winand's e-book https://use-the-index-luke.com/ to expand your understanding of all this.