今回は久々にSQLServer2005で遊んでみました。
ターゲットにしたのは
付加列インデックスです。
まずは対象となるテーブルの作成をします。
CREATE TABLE [dbo].[TestTable1](
[id] [int] NOT NULL,
[dt] [datetime] NULL,
[value] [varchar](max) COLLATE Japanese_CI_AS NULL
)
次にデータを1,000,00件追加します。
ここで使用しているgenerate_seriesについては
この記事で書いたものを使用しています。
またNEWID()については
この記事を参照してください。
insert into TestTable1
select num, cast('2007/01/01' as datetime) + num*10, '01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
+ cast((select num from ( select top 1 NEWID() as nid , num from generate_series(1,1000,1) order by nid) t) as varchar) from
generate_series(1,100000,1)
この状態で２つのSQLを発行します。
まずはインデクスの作成をしない状態でSQLを発行し、テーブルスキャンの状態でどのぐらいの負荷がかかるかを見てみます。
1つ目のSQLはid列、dt列をテーブルから絞り込み条件なしで取得するSQLで、
2つ目のSQLはid列、dt列をテーブルから絞り込み条件ありで取得するSQLです。
SQLの実行と同時にIOの情報を取得するため、あらかじめ
のSQLを発行しています。
- 1つ目のSQL
select id, dt from TestTable1 ;
テーブル 'TestTable1'。スキャン回数 1、論理読み取り数 2858、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
- 2つ目のSQL
select id, dt from TestTable1 where dt between cast('2007/01/01' as datetime) and cast('2010/01/01' as datetime);
テーブル 'TestTable1'。スキャン回数 1、論理読み取り数 2858、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
ここで、付加列を持つインデクスを作成します。
id列がキーでdt列が付加列になるように指定します。
CREATE INDEX IX_Test
ON TestTable1 (id)
INCLUDE (dt)
インデクス作成後、先ほどと同じSQLを再実行して実行プランを見てみます。
- 1つ目のSQL
select id, dt from TestTable1 ;
テーブル 'TestTable1'。スキャン回数 1、論理読み取り数 324、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
- 2つ目のSQL
select id, dt from TestTable1 where dt between cast('2007/01/01' as datetime) and cast('2010/01/01' as datetime);
テーブル 'TestTable1'。スキャン回数 1、論理読み取り数 324、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
論理読み取り数やI/Oのコストが減少したのを確認してもらえると思います。
インデクスにキー列(id列)以外に付加列(dt列)を格納するようにしたため、
テストしたSQLはインデクスだけをサーチして結果を返せるようになったため、IOコストが下がったといえます。
こんな感じで便利な付加列ですが、付加列をつけたからと言って早くなるとは限らないです。
例えば、付加列の指定をdt列ではなくvalue列でインデクス作成をしたとします。
その後、2つの付加列に指定したvalue列に対して検索条件をつけたSQLを実行してもテーブルスキャンと大差ない結果が取得されます。
まずは、インデクスのない状態でSQLを発行した結果を取得します。
- 1つ目のSQL
select id, [value] from TestTable1
テーブル 'TestTable1'。スキャン回数 1、論理読み取り数 2858、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
- 2つ目のSQL
select id, r from
(select id, cast(replace([value],'01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789','') as int) as r from TestTable1 ) t
where r between 1 and 500
テーブル 'TestTable1'。スキャン回数 1、論理読み取り数 2858、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
今度はvalue列を付加列に指定して、インデクスの作成を行います。
CREATE INDEX IX_Test
ON TestTable1 (id)
INCLUDE (value)
その後、再び同じSQLを実行してみます。
- 1つ目のSQL
select id, [value] from TestTable1
テーブル 'TestTable1'。スキャン回数 1、論理読み取り数 2825、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
- 2つ目のSQL
select id, r from
(select id, cast(replace([value],'01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789','') as int) as r from TestTable1 ) t
where r between 1 and 500
テーブル 'TestTable1'。スキャン回数 1、論理読み取り数 2825、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。
この様に、付加列の値が大きい場合、インデクスのデータが大きくなってしまうため
論理読み取り数やI/Oのコストが大きく改善されることはなくなってしまいます。
便利な付加列をうまく活用していただけたら幸いです。