title: Optimizing ROW_NUMBER() in SQL Server
body: <p>We have a number of machines which record data into a database at sporadic intervals.  For each record, I'd like to obtain the time period between <em>this</em> recording and the <em>previous</em> recording.</p>
  
  <p>I can do this using ROW_NUMBER as follows:</p>
  
  <pre><code>WITH TempTable AS (
      SELECT *, ROW_NUMBER() OVER (PARTITION BY Machine_ID ORDER BY Date_Time) AS Ordering
      FROM dbo.DataTable
  )
  
  SELECT [Current].*, Previous.Date_Time AS PreviousDateTime
  FROM TempTable AS [Current]
  INNER JOIN TempTable AS Previous 
      ON [Current].Machine_ID = Previous.Machine_ID
      AND Previous.Ordering = [Current].Ordering + 1
  </code></pre>
  
  <p>The problem is, it goes <em>really</em> slow (several minutes on a table with about 10k entries) - I tried creating separate indicies on Machine_ID and Date_Time, and a single joined-index, but nothing helps.</p>
  
  <p>Is there anyway to rewrite this query to go faster?</p>