問題描述： 有一個查詢如下，去掉 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

的時候，很快就出來結果了，但加上

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

比不加

TOP 1

慢。

注意：

每次變更數據，并且完成“速度測試腳本

”測試后，需要修改“測試數據的變更腳本

”中，紅色的

@

value

為

@value1

，讓剛才設置匹配的數據再變回為不匹配

?

附：聯接的幾種方式

1．????

嵌套循環聯接

嵌套循環聯接也稱為

“

嵌套迭代

”

，它將一個聯接輸入用作外部輸入表(顯示為圖形執行計劃中的頂端輸入)，將另一個聯接輸入用作內部(底端)輸入表。外部循環逐行處理外部輸入表。內部循環會針對每個外部行執行，在內部輸入表中搜索匹配行。

最簡單的情況是，搜索時掃描整個表或索引；這稱為

“

單純嵌套循環聯接

”

。如果搜索時使用索引，則稱為

“

索引嵌套循環聯接

”

。如果將索引生成為查詢計劃的一部分(并在查詢完成后立即將索引破壞)，則稱為

“

臨時索引嵌套循環聯接

”

。查詢優化器考慮了所有這些不同情況。

如果外部輸入較小而內部輸入較大且預先創建了索引，則嵌套循環聯接尤其有效。在許多小事務中(如那些只影響較小的一組行的事務)，索引嵌套循環聯接優于合并聯接和哈希聯接。但在大型查詢中，嵌套循環聯接通常不是最佳選擇。

?

2．????

合并聯接

合并聯接要求兩個輸入都在合并列上排序，而合并列由聯接謂詞的等效

(ON)

子句定義。通常，查詢優化器掃描索引(如果在適當的一組列上存在索引)，或在合并聯接的下面放一個排序運算符。在極少數情況下，雖然可能有多個等效子句，但只用其中一些可用的等效子句獲得合并列。

由于每個輸入都已排序，因此

Merge Join

運算符將從每個輸入獲取一行并將其進行比較。例如，對于內聯接操作，如果行相等則返回。如果行不相等，則廢棄值較小的行并從該輸入獲得另一行。這一過程將重復進行，直到處理完所有的行為止。

合并聯接操作可以是常規操作，也可以是多對多操作。多對多合并聯接使用臨時表存儲行。如果每個輸入中有重復值，則在處理其中一個輸入中的每個重復項時，另一個輸入必須重繞到重復項的開始位置。

如果存在駐留謂詞，則所有滿足合并謂詞的行都將對該駐留謂詞取值，而只返回那些滿足該駐留謂詞的行。

合并聯接本身的速度很快，但如果需要排序操作，選擇合并聯接就會非常費時。然而，如果數據量很大且能夠從現有

B

樹索引中獲得預排序的所需數據，則合并聯接通常是最快的可用聯接算法。

?

3．????

哈希聯接

哈希聯接有兩種輸入：生成輸入和探測輸入。查詢優化器指派這些角色，使兩個輸入中較小的那個作為生成輸入。

哈希聯接用于多種設置匹配操作：內部聯接；左外部聯接、右外部聯接和完全外部聯接；左半聯接和右半聯接；交集；聯合和差異。此外，哈希聯接的某種變形可以進行重復刪除和分組，例如

SUM(salary) GROUP BY department

。這些修改對生成和探測角色只使用一個輸入。

以下幾節介紹了不同類型的哈希聯接：內存中的哈希聯接、

Grace

哈希聯接和遞歸哈希聯接。

內存中的哈希聯接

哈

希聯接先掃描或計算整個生成輸入，然后在內存中生成哈希表。根據計算得出的哈希鍵的哈希值，將每行插入哈希存儲桶。如果整個生成輸入小于可用內存，則可以

將所有行都插入哈希表中。生成階段之后是探測階段。一次一行地對整個探測輸入進行掃描或計算，并為每個探測行計算哈希鍵的值，掃描相應的哈希存儲桶并生成

匹配項。

Grace

哈希聯接

如果生成輸入大于內存，哈希聯接將分為幾步進行。這稱為

“Grace

哈希聯接

”

。

每一步都分為生成階段和探測階段。首先，消耗整個生成和探測輸入并將其分區(使用哈希鍵上的哈希函數)為多個文件。對哈希鍵使用哈希函數可以保證任意兩個

聯接記錄一定位于相同的文件對中。因此，聯接兩個大輸入的任務簡化為相同任務的多個較小的實例。然后將哈希聯接應用于每對分區文件。

遞歸哈希聯接

如果生成輸入非常大，以至于標準外部合并的輸入需要多個合并級別，則需要多個分區步驟和多個分區級別。如果只有某些分區較大，則只需對那些分區使用附加的分區步驟。為了使所有分區步驟盡可能快，將使用大的異步

I/O

操作以便單個線程就能使多個磁盤驅動器繁忙工作。

border: 1pt solid #dedfef; padding: 0cm

本文原創發布php中文網，轉載請注明出處，感謝您的尊重！