TSQL优化

Question

我有下面的查询，执行时间为2秒，因为两个表中有大量行(每行100多万行)，我想知道还有什么可以做的来优化查询。

表

tblInspection.ID bigint (主键)

tblInspection.IsPassedFirstTime位(非聚集索引)

tblInspectionFailures.ID bigint (主键)

tblInspectionFailures.InspectionID bigint (非聚集索引)

查询

SELECT TOP 1 tblInspection.ID FROM tblInspection 
                INNER JOIN tblInspectionFailures ON tblInspection.ID = tblInspectionFailures.InspectionID 
                WHERE (tblInspection.IsPassedFirstTime = 1)

执行计划

​

我可以看到，我正在对索引进行聚集搜索，但仍然需要一些时间。

Answer 1

我唯一能想到的就是

    SELECT  i.ID FROM 
    (select TOP 1 id from tblInspection 
     WHERE IsPassedFirstTime = 1) i
                    INNER JOIN tblInspectionFailures ON 
i.ID = tblInspectionFailures.InspectionID 

Answer 2

试一试

SET ROWCOUNT 1

SELECT tblInspection.ID FROM tblInspection 
            INNER JOIN tblInspectionFailures ON tblInspection.ID = tblInspectionFailures.InspectionID 
            WHERE (tblInspection.IsPassedFirstTime = 1)

这基本上是一样的，但是告诉sql在第一个行之后停止返回行。