Displaying a single record based on DocID I have a table with ~900,000 records. I need to display a single record based on DocID using this query. The record is the latest record that got modified by another program.

This piece of query run ~7 seconds:

SELECT        TOP 1 t.Register
FROM            PxStats AS s INNER JOIN Therapy AS t ON s.ID = t.ID
WHERE        (s.Date = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) AND (t.DocID = 1)
ORDER BY s.Time DESC


I do not have access to modify the tables design, the table is indexed by ID, but I can't filter the record based on ID because the latest iD is not guaranteed the latest record modified.
Starting with SQL 2008 there is a window function that does this nicely. 

900,000 records even with lack of index that seems like it should be faster.

I doubt moving the condition into the join will fix the performance but it is worth a try. 

SELECT TOP 1 t.Register
  FROM PxStats AS s 
 INNER JOIN Therapy AS t 
    ON s.ID = t.ID
   AND s.Date = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 
   AND t.DocID = 1
 ORDER BY s.Time DESC;  


If you can post the query plan.  

If you remove the top 1 on average how many records would be returned?  

A reach but try

declare @today datetime = (select DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())));

SELECT TOP 1 t.Register
  FROM PxStats AS s 
 INNER JOIN Therapy AS t 
    ON s.ID = t.ID
   AND s.Date = @today 
   AND t.DocID = 1
 ORDER BY s.Time DESC;