title: A better alternative to ROW_NUMBER to retrieve a single record per group
body: <p>We are building a data warehouse where we want to capture the price of every single product and keep this data by day. There is a table with the <code>FromDate</code> and the <code>ToDate</code>, both of which can have <code>NULL</code>'s.</p>
  
  <p>The logic to find todays price is:</p>
  
  <ol>
  <li>Ignore any records where the <code>FromDate</code> is in the future.</li>
  <li>Ignore any records where the <code>ToDate</code> is in the past.</li>
  <li>Where there are multiple records that satisfy the above, we want to get the price where the <code>FromDate</code> is the most recent and the <code>ToDate</code> is the closest to the current date.</li>
  <li>The system allows duplicates for the same <code>FromDate</code> and <code>ToDate</code>, so we then order by the primary key descending (newest first).</li>
  </ol>
  
  <p>To tackle this problem, I've used a <code>ROW_NUMBER</code> with a <code>PARTITION</code> to order the records, and only get the first one. This is working, however it is taking around 20 minutes per day (and hitting <code>tempdb</code> quite hard) as we have about 2 million records that will be created from this query. </p>
  
  <p>Are there any better alternatives that may increase the performance of the query?</p>
  
  <p><a href="http://sqlfiddle.com/#!6/f1cce/2" rel="nofollow">SQL Fiddle here</a></p>
  
  <p>Note that the fiddle is an overly simplified example of the data and only contains relevant parts for this question.</p>