試してみた。 〜 Can Foreign key improve performance?

Can Foreign key improve performance?: SQL Server 2000, 2005, 2008

外部キーを設定することで、SELECTパフォーマンスが上がります、とのこと。

SQL SERVER 2008で検証。

実行環境

OS	Windows XP SP3
CPU	Core 2 Duo T7250 2GHz
Memory	2.5G
HDD	5400rpm

table作成

EmployeeOrderの属性EmployeeIDはテーブルEmployeeの属性EmployeeIDへの外部キー

create table Employee(EmployeeID int primary key) 
create table EmployeeOrder(
	OrderID int primary key,
	EmployeeID int not null constraint fkOrderCust references Employee(EmployeeID)) 

data挿入

Employee：300万行
EmployeeOrder：100万行、外部キーの値はランダムに設定。

;with
Range( I, L )
AS
(
	SELECT 1, 3000000
),
EID( ID )
AS
(
	SELECT I
	FROM Range
	UNION ALL
	SELECT ID+1
	FROM EID
	WHERE ID < ( SELECT L FROM Range )
)
INSERT INTO Employee
SELECT ID
FROM EID
OPTION ( MAXRECURSION 0);

;with
Range( I, L )
AS
(
	SELECT 1, 1000000
),
OID( ID )
AS
(
	SELECT I
	FROM Range
	UNION ALL
	SELECT ID+1
	FROM OID
	WHERE ID < ( SELECT L FROM Range )
)
INSERT INTO EmployeeOrder
SELECT ID, ABS(CHECKSUM(NEWID())) % 3000000 + 1
FROM OID
OPTION ( MAXRECURSION 0);

実行するSQL

Employeeに存在するEmployeeIDを持つEmployeeOrderを取得

Select * from EmployeeOrder eo 
where exists 
( 
select * From Employee e 
where eo.EmployeeID = e.EmployeeID 
) 

外部キー有効版結果

キャッシュ無し

(1000000 行処理されました)
テーブル 'EmployeeOrder'。スキャン回数 1、論理読み取り数 2110、物理読み取り数 6、先行読み取り数 2091、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

SQL Server 実行時間: 
、CPU 時間 = 453 ミリ秒、経過時間 = 45583 ミリ秒。

キャッシュ有り

(1000000 行処理されました)
テーブル 'EmployeeOrder'。スキャン回数 1、論理読み取り数 2110、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

 SQL Server 実行時間: 
、CPU 時間 = 485 ミリ秒、経過時間 = 44385 ミリ秒。

実行プラン

外部キー無効版結果

外部キー制約を無効にして実行する。

ALTER TABLE EmployeeOrder
NOCHECK CONSTRAINT fkOrderCust

キャッシュ無し

(1000000 行処理されました)
テーブル 'EmployeeOrder'。スキャン回数 3、論理読み取り数 2136、物理読み取り数 15、先行読み取り数 2107、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
テーブル 'Employee'。スキャン回数 3、論理読み取り数 4889、物理読み取り数 43、先行読み取り数 4846、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
テーブル 'Worktable'。スキャン回数 0、論理読み取り数 0、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

 SQL Server 実行時間: 
、CPU 時間 = 3595 ミリ秒、経過時間 = 51534 ミリ秒。

キャッシュ有り

(1000000 行処理されました)
テーブル 'EmployeeOrder'。スキャン回数 3、論理読み取り数 2136、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
テーブル 'Employee'。スキャン回数 3、論理読み取り数 4893、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
テーブル 'Worktable'。スキャン回数 0、論理読み取り数 0、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

 SQL Server 実行時間: 
、CPU 時間 = 2907 ミリ秒、経過時間 = 46566 ミリ秒。

実行プラン

外部キー無効版結果（並列実行しない）

MAXDOPオプションを使用して並列実行を禁止する。

Select * from EmployeeOrder eo 
where exists 
( 
select * From Employee e 
where eo.EmployeeID = e.EmployeeID 
)
OPTION( MAXDOP 1)

キャッシュ無し

(1000000 行処理されました)
テーブル 'Worktable'。スキャン回数 0、論理読み取り数 0、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
テーブル 'Employee'。スキャン回数 1、論理読み取り数 4843、物理読み取り数 13、先行読み取り数 4830、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
テーブル 'EmployeeOrder'。スキャン回数 1、論理読み取り数 2110、物理読み取り数 6、先行読み取り数 2091、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

 SQL Server 実行時間: 
、CPU 時間 = 3234 ミリ秒、経過時間 = 49076 ミリ秒。

キャッシュ有り

(1000000 行処理されました)
テーブル 'Worktable'。スキャン回数 0、論理読み取り数 0、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
テーブル 'Employee'。スキャン回数 1、論理読み取り数 4843、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
テーブル 'EmployeeOrder'。スキャン回数 1、論理読み取り数 2110、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

 SQL Server 実行時間: 
、CPU 時間 = 3000 ミリ秒、経過時間 = 47496 ミリ秒。

実行プラン

結論

外部キー制約があることでEmployeeテーブルとの結合条件が確実にTrueとなることが分かる。
その為、Employeeテーブルとの結合をする必要がなくなる。
よって、SELECT時のパフォーマンスが上がる。
この結果ではSELECT結果返すところが時間かかってて全体ではそんなに時間は変わらないけど。SELECT COUNT(*)とかにしたほうが結果わかりやすい。