CODEWITHSUNDEEP
sql-server
StevieB
StevieB

Reputation: 6533

SQL Server with Order by and select top 1 very slow

This query without the TOP 1 works in a second with the top 1, it takes 8 seconds. Any ideas how I can improve this ?

SELECT top 1  bw.id as workflow_id,bw.status_id workflow_status_id, bo. record_id,bo.addr_line_1, bw.created_by 
from dbo.input_Oct bo  left outer join dbo.onebyone_workflow bw on bw.record_id = bo.record_id and (job_id = 18) 
where (bw.id IS NULL)
order by bo.match_result asc ,bo.unique_address desc, bo.record_id asc

Upvotes: 2

Views: 758

Answers (2)

Karl Gjertsen
Karl Gjertsen

Reputation: 4928

Outer Joins are expensive. I'd try moving the AND from the Left Outer Join and putting it in the where. I'm not sure which table it is in, so you might need to change the same slightly.

SELECT top 1 bw.id as workflow_id, bw.status_id workflow_status_id,
             bo. record_id,bo.addr_line_1, bw.created_by 
FROM dbo.input_Oct bo  
LEFT OUTER JOIN dbo.onebyone_workflow bw on bw.record_id = bo.record_id and 
WHERE bw.id IS NULL AND job_id = 18
ORDER BY bo.match_result asc, bo.unique_address desc, bo.record_id asc

Upvotes: 0

Roman Marusyk
Roman Marusyk

Reputation: 24579

Please try to use ROW_NUMBER()

;WITH Resultaat AS (select bw.id, ....
                   , ROW_NUMBER() OVER (order by 
                      bw.id ASC
                   ) AS RM 
                   from ....
) select * from Resultaat  where RN = 1

Upvotes: 1

Related Questions