TOP 1比不加TOP慢的疑惑

问题描述：

有一个查询如下，去掉 TOP 1 的时候，很快就出来结果了，但加上 TOP 1 的时候，一般要 2~3 秒才出数据，何解？

SELECT TOP 1

A . INVNO

FROM A , B

WHERE A . Item = B . ItemNumber

AND B . OwnerCompanyCode IS NOT NULL

在使用 TOP 1 的时候， SQL Server 会尽力先找出这条 TOP 1 的记录，这就导致它采用了与不加 TOP 时不一致的扫描算法， SQL Server 查询优化器始终认为，应该可以比较快的找到匹配的第 1 条记录，所以一般是使用嵌套循环的联接，则不加 TOP 1 时， SQL Server 会根据结构和数据的统计信息决策出联接策略。 嵌套循环一般适用于联系的两个表，一个表的数据较大，而另一个表的数据较小的情况 ，如果查询匹配的值出现在扫描的前端，则在取 TOP 1 的情况下，是符合嵌套循环联系的使用条件的，但当匹配的数据出现在扫描的后端，或者是基本上没有匹配的数据时，则嵌套循环要扫描完成两个大表，这显然是不适宜的，也正是因为这种情况，导致了 TOP 1 比不加 TOP 1 的效率慢很多

USE tempdb

GO

SET NOCOUNT ON

--======================================

-- 创建测试环境

--======================================

RAISERROR ( ' 创建测试环境 ' , 10 , 1 ) WITH NOWAIT

-- Table A

CREATE TABLE [dbo] . A (

[TranNumber] [int] IDENTITY ( 1 , 1 ) NOT NULL,

[INVNO] [char] ( 8 ) NOT NULL,

[ITEM] [char] ( 15 ) NULL DEFAULT ( '' ),

PRIMARY KEY ( [TranNumber] )

)

CREATE INDEX [indexONinvno] ON [dbo] . A ( [INVNO] )

CREATE INDEX [indexOnitem] ON [dbo] . A ( [ITEM] )

CREATE INDEX [indexONiteminnvo] ON [dbo] . A ( [INVNO] , [ITEM] )

GO

-- Table B

CREATE TABLE [dbo] . B (

[ItemNumber] [char] ( 15 ) NOT NULL DEFAULT ( '' ),

[CompanyCode] [char] ( 4 ) NOT NULL,

[OwnerCompanyCode] [char] ( 4 ) NULL,

PRIMARY KEY ( [ItemNumber] , [CompanyCode] )

)

CREATE INDEX [ItemNumber] ON [dbo] . B ( [ItemNumber] )

CREATE INDEX [CompanyCode] ON [dbo] . B ( [CompanyCode] )

CREATE INDEX [OwnerCompanyCode] ON [dbo] . B ( [OwnerCompanyCode] )

GO

--======================================

-- 生成测试数据

--======================================

RAISERROR ( ' 生成测试数据 ' , 10 , 1 ) WITH NOWAIT

INSERT [dbo] . A ( [INVNO] , [ITEM] )

SELECT LEFT( NEWID (), 8 ), RIGHT( NEWID (), 15 )

FROM syscolumns A , syscolumns B

INSERT [dbo] . B ( [ItemNumber] , [CompanyCode] , [OwnerCompanyCode] )

SELECT RIGHT( NEWID (), 15 ), LEFT( NEWID (), 4 ), LEFT( NEWID (), 4 )

FROM syscolumns A , syscolumns B

GO

--======================================

-- 进行查询测试

--======================================

RAISERROR ( ' 进行查询测试 ' , 10 , 1 ) WITH NOWAIT

DECLARE @dt DATETIME , @id int , @loop int

DECLARE @ TABLE (

id int IDENTITY ,

[TOP 1] int ,

[WITHOUT TOP] int )

SET @loop = 0

WHILE @loop < 10

BEGIN

SET @loop = @loop + 1

RAISERROR ( 'test %d' , 10 , 1 , @loop ) WITH NOWAIT

SET @dt = GETDATE ()

SELECT TOP 1

A . INVNO

FROM A , B

WHERE A . Item = B . ItemNumber

AND B . OwnerCompanyCode IS NOT NULL

INSERT @([TOP 1] ) VALUES ( DATEDIFF ( ms , @dt , GETDATE ()))

SELECT @id = SCOPE_IDENTITY (), @dt = GETDATE ()

SELECT --TOP 1

A . INVNO

FROM A , B

WHERE A . Item = B . ItemNumber

AND B . OwnerCompanyCode IS NOT NULL

UPDATE @ SET [WITHOUT TOP] = DATEDIFF ( ms , @dt , GETDATE ())

WHERE id = @id

END

SELECT * FROM @

UNION ALL

SELECT NULL, SUM ( [TOP 1] ), SUM ( [WITHOUT TOP] ) FROM @

GO

DECLARE @value char ( 15 ), @value1 char ( 15 )

SELECT

@value = LEFT( NEWID (), 15 ),

@value1 = LEFT( NEWID (), 15 )

UPDATE A

SET Item = @value

FROM A

INNER JOIN(

SELECT TOP 1

[TranNumber]

FROM (

SELECT TOP 20 PERCENT

[TranNumber]

FROM A

ORDER BY [TranNumber]

) AA

ORDER BY [TranNumber] DESC

) B

ON A . [TranNumber] = B . [TranNumber]

UPDATE B

SET ItemNumber = @value

FROM B

INNER JOIN(

border-right: medium none; padding-right: 0