TOP 1比不加TOP慢的疑惑 (转)

[@more@]

来源:http://blog.csdn.net/zjcxc/archive/2006/07/10/898861.aspx

问题描述：

有一个查询如下，去掉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(

SELECT TOP 1

[ItemNumber], [CompanyCode]

FROM(

SELECT TOP 20 PERCENT

[ItemNumber], [CompanyCode]

FROM B

ORDER BY [ItemNumber], [CompanyCode]

)BB

ORDER BY [ItemNumber] DESC, [CompanyCode] DESC

)B1

ON B.[ItemNumber] = B1.[ItemNumber]

AND B.[CompanyCode] = B1.[CompanyCode]

GO

测试说明：

1． 在刚建立好测试环境的时候，是没有任何匹配项的，这时候，TOP 1会扫描两个表的所有数据，运行“速度测试脚本”可以看到此时有无TOP 1的效率差异：TOP 1明显比不加TOP 慢

2． 修改“测试数据的变更脚本”中，红色的20，让匹配的数据出现在扫描的顶端、中间和尾端，分别使用“速度测试脚本”测试，可以看到，匹配的值靠近扫描的前端的时候，TOP 1比不加TOP 快，随着匹配数据很后端的推移，这种效率差异会越来越小，到后面就变成TOP 1

附：联接的几种方式

1． 嵌套循环联接

嵌套循环联接也称为“嵌套迭代”，它将一个联接输入用作外部输入表（显示为图形执行计划中的顶端输入），将另一个联接输入用作内部（底端）输入表。外部循环逐行处理外部输入表。内部循环会针对每个外部行执行，在内部输入表中搜索匹配行。

最简单的情况是，搜索时扫描整个表或索引；这称为“单纯嵌套循环联接”。如果搜索时使用索引，则称为“索引嵌套循环联接”。如果将索引生成为查询计划的一部分（并在查询完成后立即将索引破坏），则称为“临时索引嵌套循环联接”。查询优化器考虑了所有这些不同情况。

如果外部输入较小而内部输入较大且预先创建了索引，则嵌套循环联接尤其有效。在许多小事务中（如那些只影响较小的一组行的事务），索引嵌套循环联接优于合并联接和哈希联接。但在大型查询中，嵌套循环联接通常不是最佳选择。

2． 合并联接

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7764484/viewspace-901030/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7764484/viewspace-901030/