title: Query performance: CTE using ROW_NUMBER() to select first row
body: <p>We have three environments and when I run my SQL query in two of them just takes 30 or 38 seconds to run but in the other environment running is not completed and I should cancel it. Query is based on two parts, a CTE and a very simple select from a table, in both CTE and select I'm using the same table.</p>
  <p>Could you please tell me why it takes so long time? how can I improve the query?</p>
  <pre><code>ALTER VIEW [fact].[vPurchase] 
  AS
      WITH VKPL AS 
      (
          SELECT * 
          FROM
              (SELECT 
                   iv.[Delivery_FK],
                   1 AS column2,
                   ROW_NUMBER() OVER(PARTITION BY [Delivery_FK] ORDER BY iv.UpdateDate) AS rk     
               FROM 
                   [fact].[KRMFact] iv   
               LEFT JOIN 
                   [dimension].[Product] pr ON iv.Product_FK =pr.Product_SK
               LEFT JOIN 
                   [dimension].[Delivery] le ON le.Delivery_FK = iv.Delivery_FK 
               WHERE 
                   pr.Product_Key = '740') X
          WHERE 
              rk = 1
      )
      SELECT 
           -- ....  here are some columns
           Delivery_FK,
           Product_FK,
           CAST(column2 AS VARCHAR) AS column2,
           f.[UpdateDate] AS [Update date]
       FROM 
           [fact].[KRMFact] f
       LEFT JOIN 
           VKPL v ON f.Delivery_FK = v.Delivery_FK
  </code></pre>