title: How to optimize for both scenarios when a join is faster than dense_rank only when the result set is small?
body: <p>When unfiltered, <code>dense_rank</code> on FeedDeliveryNutrients.NutrientID over 150,000 rows is 3.5x faster than joining to Nutrients with <code>row_number</code> on Nutrients.ID and using the joined row number. When filtered to a specific flock, joining with <code>row_number</code> is 9x faster.</p>
  
  <p>Is there any optimization technique that could get the best of both worlds <strong>in a single query</strong>?</p>
  
  <p><br></p>
  
  <p>Fastest when unfiltered (150,000 rows returned):</p>
  
  <pre><code>select
      FeedDeliveries.FlockID,
      FeedDeliveryID,
      DeliveryLb,
      Bin,
      DeliveryDate,
      FormulaID,
      FeedEnergy,
      Nutrient1, Nutrient2, Nutrient3, Nutrient4, Nutrient5, Nutrient6, Nutrient7, Nutrient8, Nutrient9, Nutrient10, Nutrient11, Nutrient12, Nutrient13, Nutrient14, Nutrient15
  from (
      select
          FeedDeliveryID,
          sum(case when dense_rank = 1 then Amount end) as Nutrient1,
          sum(case when dense_rank = 2 then Amount end) as Nutrient2,
          sum(case when dense_rank = 3 then Amount end) as Nutrient3,
          sum(case when dense_rank = 4 then Amount end) as Nutrient4,
          sum(case when dense_rank = 5 then Amount end) as Nutrient5,
          sum(case when dense_rank = 6 then Amount end) as Nutrient6,
          sum(case when dense_rank = 7 then Amount end) as Nutrient7,
          sum(case when dense_rank = 8 then Amount end) as Nutrient8,
          sum(case when dense_rank = 9 then Amount end) as Nutrient9,
          sum(case when dense_rank = 10 then Amount end) as Nutrient10,
          sum(case when dense_rank = 11 then Amount end) as Nutrient11,
          sum(case when dense_rank = 12 then Amount end) as Nutrient12,
          sum(case when dense_rank = 13 then Amount end) as Nutrient13,
          sum(case when dense_rank = 14 then Amount end) as Nutrient14,
          sum(case when dense_rank = 15 then Amount end) as Nutrient15
      from (select *, dense_rank() over (partition by FeedDeliveryID order by NutrientID) as dense_rank from dbo.FeedDeliveryNutrients) n
      group by FeedDeliveryID
  ) pvt
  join dbo.FeedDeliveries on FeedDeliveries.ID = FeedDeliveryID
  </code></pre>
  
  <p><br></p>
  
  <p>Fastest when filtered by dbo.FeedDeliveries.FlockID (~100 rows returned):</p>
  
  <pre><code>select
      FeedDeliveries.FlockID,
      FeedDeliveryID,
      DeliveryLb,
      Bin,
      DeliveryDate,
      FormulaID,
      FeedEnergy,
      Nutrient1, Nutrient2, Nutrient3, Nutrient4, Nutrient5, Nutrient6, Nutrient7, Nutrient8, Nutrient9, Nutrient10, Nutrient11, Nutrient12, Nutrient13, Nutrient14, Nutrient15
  from (
      select
          FeedDeliveryID,
          sum(case when n.row_number = 1 then Amount end) as Nutrient1,
          sum(case when n.row_number = 2 then Amount end) as Nutrient2,
          sum(case when n.row_number = 3 then Amount end) as Nutrient3,
          sum(case when n.row_number = 4 then Amount end) as Nutrient4,
          sum(case when n.row_number = 5 then Amount end) as Nutrient5,
          sum(case when n.row_number = 6 then Amount end) as Nutrient6,
          sum(case when n.row_number = 7 then Amount end) as Nutrient7,
          sum(case when n.row_number = 8 then Amount end) as Nutrient8,
          sum(case when n.row_number = 9 then Amount end) as Nutrient9,
          sum(case when n.row_number = 10 then Amount end) as Nutrient10,
          sum(case when n.row_number = 11 then Amount end) as Nutrient11,
          sum(case when n.row_number = 12 then Amount end) as Nutrient12,
          sum(case when n.row_number = 13 then Amount end) as Nutrient13,
          sum(case when n.row_number = 14 then Amount end) as Nutrient14,
          sum(case when n.row_number = 15 then Amount end) as Nutrient15
      from dbo.FeedDeliveryNutrients
      join (select *, row_number() over (order by ID) as row_number from dbo.Nutrients) n on n.ID = NutrientID
      group by FeedDeliveryID
  ) pvt
  join dbo.FeedDeliveries on FeedDeliveries.ID = FeedDeliveryID
  </code></pre>