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.