title: Help with Improving Performance of SQL Server 2008 CTE
body: <p>I have a SQL Server 2008 CTE which is responsible for returning the <strong>Top Review</strong> for a location.</p>
  
  <p>The CTE is wrapped in a UDF (Table-Valued), and joined on the <em>LocationId</em> field, so i can get the top review for each location.</p>
  
  <p><strong>Cardinalities</strong>: </p>
  
  <p><strong>1</strong> Location has <strong>0-many</strong> PostLocations
  <br/>
  <strong>1</strong> PostLocation has <strong>1</strong> Post
  <br/>
  <strong>1</strong> Post has <strong>1</strong> Review</p>
  
  <p>Here's the UDF:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[Review_HighestRated_Aggregated_ByLocation]
  (   
  
  )
  RETURNS TABLE 
  AS
  RETURN 
  (
      WITH [RankedLocations] AS
      (
          SELECT      PL.LocationId, 
                      R.Rating, 
                      P.PostID, 
                      P.UniqueUri, 
                      P.Content, 
                      ROW_NUMBER() OVER (PARTITION BY PL.LocationId ORDER BY R.Rating DESC, P.LocationTypeId, P.CreatedOn DESC) As ScoreRank
  
          From        dbo.PostLocations As PL
          INNER JOIN  dbo.Posts As P
          ON          P.PostId = PL.PostId
          INNER JOIN  dbo.Reviews As R
          ON          R.PostId = P.PostId
  
          WHERE       R.ReviewTypeId &lt;&gt; 5
          AND         P.Content IS NOT NULL
      )
  
      SELECT  LocationId, Rating, PostID, UniqueUri, Content
      FROM    RankedLocations
      WHERE   ScoreRank = 1
  )
  </code></pre>
  
  <p>Here's an example of how i'm using it:</p>
  
  <pre><code>select l.LocationId, l.Name, l.UniqueUri, r.UniqueUri, r.Content
  from @Locations l -- temp table containing around 18 location ids
  inner join dbo.Review_HighestRated_Aggregated_ByLocation() r 
  on l.LocationId = r.LocationId
  </code></pre>
  
  <p>The above query is taking <strong>15 seconds</strong> to execute, which is unacceptable. Without the join to the UDF it's takes 0 seconds.</p>
  
  <p>Any ideas on how i can improve it?</p>
  
  <p>If i look at the execution plan, it's the <strong>SORT</strong> that is taking up 98% of the execution cost. The IO/subtree cost of this operation is ~300.</p>
  
  <p>I was hoping the execution plan would give me a hint to an index i could create to improve the cost, but i get nothing.</p>
  
  <p>Any ideas?</p>