sql-server - 如果一个查询具有较低的逻辑读取但另一个查询具有较低的相对查询成本，则哪个查询执行得更好？

Question

我有两个查询给出相同的结果。

第一个查询：

Select c1.CustomerID, o1.CustomerID
From Customers As c1 
Left Join Orders As o1 On c1.CustomerID = o1.CustomerID
Where o1.CustomerID Is Null

第二个查询：

Select c1.CustomerID, Null As CustomerID 
From Customers As c1
Where c1.CustomerID Not In (Select CustomerID From Orders)

两者都返回相同的结果。但是时间和 IO 的统计数据是不同的。

> 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.  
> SQL Server parse and compile time:     CPU time = 10 ms, elapsed time = 10 ms.
> 
> (2 rows 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
> 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead
> reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
> reads 0. Table 'Customers'. Scan count 1, logical reads 5, physical
> reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
> 0, lob read-ahead reads 0.
> 
> (1 row affected)
> 
>  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 180
> ms.
> 
> (2 rows affected) Table 'Orders'. Scan count 1, logical reads 510,
> physical reads 0, read-ahead reads 0, lob logical reads 0, lob
> physical reads 0, lob read-ahead reads 0. Table 'Customers'. Scan
> count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob
> logical reads 0, lob physical reads 0, lob read-ahead reads 0.
> 
> (1 row affected)
> 
>  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 150
> ms. 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.

以下是查询计划：

执行计划

第一次查询的相对查询成本为 62%，第二次查询的相对查询成本为 38%。但是，第一种情况下的逻辑读取是 22+5 = 27，而第二种情况是 515。

那么哪个查询被认为表现更好？

标签： sql-serverperformance