問題描述：

有一個查詢?nèi)缦拢サ? TOP 1 的時候，很快就出來結(jié)果了，但加上 TOP 1 的時候，一般要 2~3 秒才出數(shù)據(jù)，何解？

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 的記錄，這就導(dǎo)致它采用了與不加 TOP 時不一致的掃描算法， SQL Server 查詢優(yōu)化器始終認(rèn)為，應(yīng)該可以比較快的找到匹配的第 1 條記錄，所以一般是使用嵌套循環(huán)的聯(lián)接，則不加 TOP 1 時， SQL Server 會根據(jù)結(jié)構(gòu)和數(shù)據(jù)的統(tǒng)計(jì)信息決策出聯(lián)接策略。 嵌套循環(huán)一般適用于聯(lián)系的兩個表，一個表的數(shù)據(jù)較大，而另一個表的數(shù)據(jù)較小的情況 ，如果查詢匹配的值出現(xiàn)在掃描的前端，則在取 TOP 1 的情況下，是符合嵌套循環(huán)聯(lián)系的使用條件的，但當(dāng)匹配的數(shù)據(jù)出現(xiàn)在掃描的后端，或者是基本上沒有匹配的數(shù)據(jù)時，則嵌套循環(huán)要掃描完成兩個大表，這顯然是不適宜的，也正是因?yàn)檫@種情況，導(dǎo)致了 TOP 1 比不加 TOP 1 的效率慢很多

USE tempdb

GO

SET NOCOUNT ON

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

-- 創(chuàng)建測試環(huán)境

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

RAISERROR ( ' 創(chuàng)建測試環(huán)境 ' , 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

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

-- 生成測試數(shù)據(jù)

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

RAISERROR ( ' 生成測試數(shù)據(jù) ' , 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

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

-- 進(jìn)行查詢測試

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

RAISERROR ( ' 進(jìn)行查詢測試 ' , 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