問題描述：
有一個查詢如下，去掉 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
發表評論
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
評論