sql-server - 如何在 SQL Server 中使这个 TOP Query 快速？

Question

这是我的查询：

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

执行计划：

我试过的：

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 

执行计划： 问题是两个查询都需要 0:0:1 秒。
wsm_WorkOrderSchedule 中的 2493073 行和 TSP_TSR_Job
brentozar.com/pastetheplan/?id=ByXUickKQ 执行计划中的 524444

> 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.

---

标签： sql-serverquery-performance

Answer 1

首先，创建一个索引来去除聚集索引扫描：

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

此外，更改您的TSP_TSR_JobId索引以包含列。查看键查找运算符以找出需要哪些列