4

SQL Server version:

Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64) Nov 2 2020 18:35:09 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

I have one query which is completing in less than a second, with 5 million records in both table used. Actual Execution plan shows Index Seek for both the tables and both table reads 1 or 2 rows.

But, when convert same query to CTE, it is taking around 2min. The Actual Execution Plan shows Index Scan and reads all 5 million rows.

Until now, I was under the impression that for normal query, it doesn't matter if query is within a CTE or not. Even same nested query is also very slow.

What could be affecting it?

Actual query is:

select ca.inst_bs as Base,
    dateadd(mi,ROW_NUMBER() over (partition by i2.idcse,i2.idpln order by i2.dtdue,i2.id),i2.dtdue) as d1,
    i2.id,
    ca.id as idcse,
    i2.dtdue 
from tbl_cse ca with(nolock) 
join tbl_Inst i2 with(nolock) on i2.idcse = ca.id
where i2.idcse = 3169

Query converted to CTE is:

with tt as 
( 
    select ca.inst_bs as Base, 
        dateadd(mi,ROW_NUMBER() over (partition by i2.idcse,i2.idpln order by i2.dtdue,i2.id),i2.dtdue) as d1,
        i2.id,
        ca.id as idcse,
        i2.dtdue 
    from tbl_cse ca with(nolock) 
    join tbl_Inst i2 with(nolock) on i2.idcse = ca.id
)
select * from tt where idcse = 3169

Actual Execution Plan for performant query: Query.sqlplan

Fast Plan visualized on Paste The Plan

Actual Execution Plan for slow query using CTE: CTE.sqlplan

Slow Plan visualized on Paste The Plan

If I convert my normal query to a view and use idcse = 3169 criteria with the view, it is working slow, same as CTE.


For a temporary solution, I created a table-valued function that accepts the criteria and it is working as expected to give an index seek. I need to keep checking for a complete solution because many of my queries and views use a CTE with row_number.

0

2 Answers 2

4

Problems

The optimizer logic that allows a predicate to be pushed past a window function is quite limited. The predicate must reference a constant at runtime and the partitioning clause of the window function must contain the same column as the predicate.

Your database is using forced parameterization, so the constant value you supply in your query is replaced with a parameter marker @0. This prevents the pushdown as described in the linked Q & A just above.

parameter

You also need to ensure the column you are filtering on is the same one used in the partitioning clause of the ROW_NUMBER. It is not enough that the columns have the same name or alias, they must be the same reference.

In your query:

with tt as 
( 
    select ca.inst_bs as Base, 
        dateadd(mi,ROW_NUMBER() over (partition by i2.idcse,i2.idpln order by i2.dtdue,i2.id),i2.dtdue) as d1,
        i2.id,
        ca.id as idcse,
        i2.dtdue 
    from tbl_cse ca with(nolock) 
    join tbl_Inst i2 with(nolock) on i2.idcse = ca.id
)
select * from tt where idcse = 3169
  • The ROW_NUMBER is partitioned by i2.idcse
  • The outer test on idcse refers to ca.id (aliased as idcse)

Those are not the same reference, they just have the same exposed name.

They do have the same value due to the join on i2.idcse = ca.id, but that extra step of reasoning is enough to prevent the optimizer applying the transformation.

Solution

You can fix this in multiple ways. The easiest might be to return i2.idcse from the CTE rather than ca.id:

with tt as 
( 
    select ca.inst_bs as Base, 
        dateadd(mi,ROW_NUMBER() over (partition by i2.idcse,i2.idpln order by i2.dtdue,i2.id),i2.dtdue) as d1,
        i2.id,
        i2.idcse, -- CHANGED from ca.id as idcse
        i2.dtdue 
    from tbl_cse ca with(nolock) 
    join tbl_Inst i2 with(nolock) on i2.idcse = ca.id
)
select * from tt where idcse = 3169

To workaround the forced parameterization issue, add OPTION (RECOMPILE) to the query.

As I said in my Stack Overflow answer though, I'd probably stick will the table-valued function replacement. That gives you much more control over where the predicate appears.

Related reading:

User charlieface provided a db<>fiddle demo you might like to play around with.

1
  • Yes Parameterization is Forced in my DB and when i converted to Simple on Development DB, it has been changed to Index Seek. Unfortunately can't change it on Live. Thanks for focusing that point.
    – par
    Feb 23, 2022 at 13:07
1

These are two very different queries and I suspect you have made a mistake.

Your first one can apply the where i2.idcse = 3169 filter before working out ROW_NUMBER() (because that's how it's written). Your second one can not because some of the rows that don't match your filter could impact the result of that expression - you need to visit them all and calculate this before you are able to filter.

Your filter in the second query is against idcse = 3169 which is actually coming from ca.id as idcse. If this expression was involved in your partition by clause then it would be legal for the predicate to be pushed to before row_number is calculated.

Perhaps you meant to filter on the same column as the original filter:

with tt as 
( 
    select ca.inst_bs as Base, 
        dateadd(mi,ROW_NUMBER() over (partition by i2.idcse,i2.idpln order by i2.dtdue,i2.id),i2.dtdue) as d1,
        i2.id,
        ca.id as idcse,
        i2.dtdue ,
        i2.idcse as idcse_filter
    from tbl_cse ca with(nolock) 
    join tbl_Inst i2 with(nolock) on i2.idcse = ca.id
)
select * from tt where idcse_filter = 3169

This legally will allow the filter to be pushed, whether it gets pushed in your SQL Server version is another question.

6
  • This appears to be one of those cases where the compiler is not deducing the commutation of i2.idcse = ca.id, and therefore the exact column used in the PARTITION BY clause makes all the difference Feb 23, 2022 at 1:39
  • @Andrew To expand on that: Your answer is essentially correct but the queries are not semantically different. Those parts of your answer are in error currently.
    – Paul White
    Feb 23, 2022 at 6:25
  • @andrew-sayer , Thanks for explanation. I tried your modified query but no luck. As far as i know, SQL query optimizer doesn't work as per query written by developer. It optimize query before compiling it. but here issue is surely with row_number(). If i remove that line, it is running in a second.
    – par
    Feb 23, 2022 at 7:59
  • @PaulWhite Ah, I had missed the join filter. I've run some tests against SQL Server 2017 and it doesn't recognise the equivalency allows the predicate to be pushed. Presumably it is testing out the two transformations independently (transitive closure and predicate push down) Feb 23, 2022 at 10:00
  • @par I have tried this out on my own system and it should work fine as long as your filter is against the same column as in your partition by clause. In your case, you could stick with the original filter and change the partition by clause to include that column instead (or as well). I've tested on SQL Server 2017 going as low as QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100. If you're still having problems then maybe sharing an updated query plan would be useful Feb 23, 2022 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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