1
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ Which flavor of SQL are you using? \$\endgroup\$ – Hosch250 Apr 11 '18 at 1:35
  • \$\begingroup\$ 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. \$\endgroup\$ – 200_success Apr 11 '18 at 1:45
  • \$\begingroup\$ @Hosch250 SQL Server 2000 \$\endgroup\$ – SIRS Apr 11 '18 at 2:35
  • \$\begingroup\$ 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? \$\endgroup\$ – Phrancis Apr 11 '18 at 3:06
  • \$\begingroup\$ Any query is slow except if I add ORDER BY ID \$\endgroup\$ – SIRS Apr 11 '18 at 3:39
3
\$\begingroup\$

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; 
\$\endgroup\$
  • \$\begingroup\$ 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 \$\endgroup\$ – SIRS Apr 13 '18 at 0:45
0
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ If ID is identity and no gaps you could filter directly on that. \$\endgroup\$ – paparazzo Apr 18 '18 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.