How to make this TOP Query fast in SQL Server?

Question

This is my query:

 SELECT top 1 w.WONumber, * FROM TSP_TSR_Job t  left join
 wsm_WorkOrderSchedule w on w.tsrjobid = t.JobId WHERE t.JobID=325809

Execution plan:

What I have tried:

DECLARE @i INT  
SET @i=1  
SELECT TOP (@i) ObjectType='Job',w.WONumber,*
FROM TSP_TSR_Job t
 left HASH  join wsm_WorkOrderSchedule w 
on w.tsrjobid = t.JobId
 WHERE t.JobID=325809

Execution plan: Problem is that it's taking 0:0:1 sec for both queries.
2493073 rows in wsm_WorkOrderSchedule and 524444 in TSP_TSR_Job
brentozar.com/pastetheplan/?id=ByXUickKQ Execution plan

> SQL Server parse and compile time:     CPU time = 0 ms, elapsed time =
> 0 ms.
> 
>  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.
> Warning: The join order has been enforced because a local join hint is
> used. SQL Server parse and compile time:     CPU time = 5 ms, elapsed
> time = 5 ms.
> 
> (1 row(s) affected) Table 'Workfile'. Scan count 0, logical reads 0,
> physical reads 0, read-ahead reads 0, lob logical reads 0, lob
> physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan
> count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob
> logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table
> 'wsm_WorkOrderSchedule'. Scan count 1, logical reads 6, physical reads
> 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
> read-ahead reads 0. Table 'TSP_TSR_Job'. Scan count 0, logical reads
> 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob
> physical reads 0, lob read-ahead reads 0.
> 
> (1 row(s) affected)
> 
>  SQL Server Execution Times:    CPU time = 16 ms,  elapsed time = 11
> ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
> time = 1 ms.
> 
>  SQL Server Execution Times:    CPU time = 1 ms,  elapsed time = 1 ms.

user_3680098 · Answer

First of all, create an index to remove clustered index scan:

CREATE NONCLUSTERED INDEX IX_wsm_WorkOrderSchedule_tsrjobid
    ON wsm_WorkOrderSchedule (tsrjobid)
    INCLUDE (WONumber);

Also, alter your TSP_TSR_JobId index to have included columns. Look at key lookup operator to find out which columns are needed

user_1266737 · Answer

I suggest you add a nonclustered index on the wsm_WorkOrderSchedule (which it seems you have):

CREATE NONCLUSTERED INDEX NC_wsm_WorkOrderSchedule_tsrjobid 
ON wsm_WorkOrderSchedule (tsrjobid);

By adding this index, you'll get the best possible execution plan for your particular query (where you seem to really need all the columns, as you say).

Now, your expectation is that your query should take less than 1 second (which you think it does at this point).

But actually, by looking at the information from your STATISTICS TIME output, your query only takes about 18ms to execute (and there is am important distinction to be made).

The rest up to the 1 second you're presumably looking at in SQL Server Management Studio on the bottom right of the screen, is actually the time required for the data to pass through the network and for SSMS to render it.

Edit:

If you're curious where I came up with the 18ms, it's easy to just go to statisticsparser.com and post in the text output of your STATISTICS TIME and IO in there and parse. You'll get a nice visual brakedown of your times and other resource usage by table.

How to make this TOP Query fast in SQL Server?

2 Answers