Geek asks and answers

Subquery Behavior in CTE

Platform - SQL Server 2008 R2 This is part of a complex stored procedure that took more than 5 minutes to complete, 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 removed the subquery 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 request is executed after 2 seconds. Although I do not want to admit this, the fact is that I do not quite understand the massive increase in productivity that the second request gave. Why do I see such a big difference?

+3
source share
2 answers

I believe that a CTE request is evaluated every time it is called. It is like a presentation. However, when you insert data into a hash table, this is a one-time process and avoids multiple executions.

, , . CTE -.

CTE , , CTE , .

0

1,

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

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  ]
0

More articles:


All Articles