Displaying a single record based on DocID

Question

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.

• user34073: Which flavor of SQL are you using?
• 200_success: Not much we can do about performance if the Date and Time columns are not indexed. That's basically all of the WHERE and ORDER BY clauses.
• SIRS: @Hosch250 SQL Server 2000
• Phrancis: This is a very simple query, I don't think the query itself is why it is so slow, 7 seconds to me indicates more of a database issue. Do other queries using the same tables take very long also?
• SIRS: Any query is slow except if I add ORDER BY ID

Answer 1

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; 

• SIRS: This query is still need ~6 sec, without TOP 1 the average is 6 sec too, I think I have no option left except add Index on Date field

Answer 2

I solved this using a workaround by querying last 10000 ID to temporary database, 10000 is 10x the average number of a new record inserted every day, by doing this, I ensure that all record in a given day is returned.

Then after that just query the temporary table using @paparazzo query above.

USE MedicalSql; 
IF OBJECT_ID('tempdb..#Temp_PxStats') IS NOT NULL DROP TABLE #Temp_PxStats;
GO
SELECT TOP 10000 * 
 INTO #Temp_PxStats
 FROM dbo.PxStats
 ORDER BY ID DESC;
GO
SELECT TOP 1 t.Register
 FROM #Temp_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; 
GO

• paparazzo: If ID is identity and no gaps you could filter directly on that.