Behaviour of sub-query in CTE

Question

Platform - SQL Server 2008 R2 This is part of a complex stored procedure, that was taking more than 5 minutes to execute and I was asked to help troubleshoot

;WITH FilteredOrders AS 
    (
        --CTE definition   
    ), 
    PagedOrders AS    
    (    
      SELECT  * FROM    
      (    
        SELECT ROW_NUMBER() OVER ( order by OrderNumber asc ) AS Row,
        --Column List from FilteredOrders
        FROM FilteredOrders    
      ) AS NumberedOrders    
      WHERE NumberedOrders.Row BETWEEN 1 AND 500
    )
 SELECT * FROM PagedOrders

I eliminated the sub-query in the second CTE and recommended this

;WITH FilteredOrders AS 
    (
        --CTE definition   
    )
    SELECT ROW_NUMBER() OVER ( order by OrderNumber asc ) AS Row,
    --Column List from FilteredOrders
    INTO #PagedOrders
    FROM FilteredOrders

SELECT  * 
FROM #PagedOrders   
WHERE #PagedOrders.Row BETWEEN 1 AND 500

Now the query executes in 2 seconds. Though I hate to admit it, the fact is that I do not fully understand the massive performance gain that the second query gave. Why am I seeing so much of a difference?

Possibly related to materialization? http://stackoverflow.com/a/1531994/314291 — StuartLC, Feb 20 '14 at 04:52
sorry i don't agree that your second query is massive performance gain instead of first.why don't you put row_number inside FilteredOrders then no need of PagedOrders . — KumarHarsh, Feb 20 '14 at 05:03

score 0 · Answer 1 · answered Feb 20 '14 at 05:03

I believe the query of CTE is evaluated everytime it is called. This is similar to views. However, when you insert the data into a hash table, its a one time process and avoids multiple executions.

This is the same reason why it is suggested to used indexed views as the data is actually stored physically and not evaluated at run time. Same case happens with CTE and hash table.

CTEs are also avoided in joins for the same reason until you are sure that the CTE query is quick enough to not impact the performance.

score 0 · Answer 2 · answered Feb 20 '14 at 05:46

Method 1,though how many rows are involve in first place

WITH FilteredOrders AS 
    (
        select blah,blah,   ROW_NUMBER() OVER ( order by OrderNumber asc ) AS Row
from blah
    ), 

      select * FROM FilteredOrders Row BETWEEN 1 AND 500

Method 2,

SELECT blah,blah, ROW_NUMBER() OVER ( order by OrderNumber asc ) AS Row,
    --Column List from FilteredOrders
    INTO #PagedOrders
    FROM [use join logic of FilteredOrders here  ]

Behaviour of sub-query in CTE

2 Answers2