ある場所で「部署毎で一番小さい日付を持つレコードを取得したい」って話があって
なんとなくSQL考えていたら複数種類出てきたので
その中で気になる２パターンをSQL Server 2005にて検証してみた。
検証準備として次のテーブルを作成しました。
（インデクスは全く張っていません）
| id |
d |
nm |
g |
| int |
datetime |
varchar(50) |
varchar(50) |
ここに次のSQLを発行してデータを突っ込んでみた。
insert into test
select num, cast('2006-01-01' as datetime) + num % 100 , 'name' + cast(num as varchar) , num / 100 from generate_series(1,1000000,1) ;
ここで使用しているgenerate_seriesについてはここで書いたものを使用しています。
で以下の２つのSQLを発行してみました。
(1) 関数のROW_NUMBERを使用してg毎にd,idの昇順で番号付与。
付与した番号が1のものを取得対象とした。
select id, nm, g,d from
(
select
row_number() over (partition by g order by d,id) rn,
id, nm, g, d from test
) outertest
where
rn = 1
order by id
;
(2) EXISTSを使用してグループが一致し、dの最小番号とカレント行のdが一致したら取得対象とした。
select id, nm, g, d from test t
where exists ( select * from test where g = t.g group by g having min(d) = t.d )
order by id;
(２)のSQLが時間がかかるSQLだと思ったんですが(相関サブクエリなので)
結果としては・・・・
(１)の方が時間がかかっていました。
以下SET STATISTICS TIME ONを使用して取得した時間
(1)の場合
SQL Server の構文解析とコンパイルの時間:
CPU 時間 = 0 ミリ秒、経過時間 = 1 ミリ秒。
(10001 行処理されました)
SQL Server 実行時間:
、CPU 時間 = 5312 ミリ秒、経過時間 = 4856 ミリ秒。
(2)の場合
SQL Server の構文解析とコンパイルの時間:
CPU 時間 = 0 ミリ秒、経過時間 = 1 ミリ秒。
(10001 行処理されました)
SQL Server 実行時間:
、CPU 時間 = 2234 ミリ秒、経過時間 = 2601 ミリ秒。
どんな動きをしているか気になったのでSET SHOWPLAN_TEXT ONにて実行計画をとってみた。
(1)の場合
|--Sort(ORDER BY:([test].[dbo].[test].[id] ASC))
|--Filter(WHERE:([Expr1004]=(1)))
|--Sequence Project(DEFINE:([Expr1004]=row_number))
|--Compute Scalar(DEFINE:([Expr1006]=(1)))
|--Segment
|--Sort(ORDER BY:([test].[dbo].[test].[g] ASC, [test].[dbo].[test].[d] ASC, [test].[dbo].[test].[id] ASC))
|--Table Scan(OBJECT:([test].[dbo].[test]))
(2)の場合
|--Sort(ORDER BY:([t].[id] ASC))
|--Hash Match(Right Semi Join, HASH:([test].[dbo].[test].[g], [Expr1007])=([t].[g], [t].[d]), RESIDUAL:([test].[dbo].[test].[g]=[test].[dbo].[test].[g] as [t].[g] AND [Expr1007]=[test].[dbo].[test].[d] as [t].[d]))
|--Hash Match(Aggregate, HASH:([test].[dbo].[test].[g]), RESIDUAL:([test].[dbo].[test].[g] = [test].[dbo].[test].[g]) DEFINE:([Expr1007]=MIN([test].[dbo].[test].[d])))
| |--Table Scan(OBJECT:([test].[dbo].[test]))
|--Table Scan(OBJECT:([test].[dbo].[test] AS [t]))
なんか(1)の実行計画はネストが深いのね(--;
インデクス作成していないので、テーブルスキャンは当然として、
ソートしたり計算したりする分(１)のほうが遅くなったのかしら・・・・？
(２)は２回テーブルスキャンが出てくるけど、同じテーブルで100万行しかないから
そんなに影響でていないのかなぁ。
Hash Matchしているので、結合は早いんでしょうねぇ。
意外な結果にちょっとびっくりしました。