前回、実RDB上は縦持ちで、アプリケーション上は横持ちでアプリケーションを構築した場合を考察しました。
次のストアードを使い、言語からは横持ちとして呼び、実RDBには縦持ちで書き出すものでした。
ALTER PROCEDURE [dbo].[縦24行挿入]
@社員CODE char(10) ,
@予定額1月 int, @実績額1月 int,…@予定額12月 int, @実績額12月 int
AS
BEGIN
insert into 縦持ち(社員CODE,月,予実,額) values(@社員CODE,1,'Y',@予定額1月)
insert into 縦持ち(社員CODE,月,予実,額) values(@社員CODE,1,'J',@実績額1月)
…
insert into 縦持ち(社員CODE,月,予実,額) values(@社員CODE,1,'Y',@予定額12月)
insert into 縦持ち(社員CODE,月,予実,額) values(@社員CODE,1,'J',@実績額12月)
END
この処理を行わない、縦持ちと横持ちの書き込み速度比は 10倍(明示トランザクション時) 位の差がでました。
それを、上記のように入力を横持ちにして、書き出し時に縦に分解して書き出すと、その比は2～3倍まで縮まりました。かなり改善されます。
読み出し時のケースも試行してみました。100000人分の2400000行のデータを作って計測しました。
横持ちは社員CODE, 縦持ちは 社員CODE,月,予実 で索引を張りました。
sql文は次のようにしました。
string 横 = @"
select top n
社員CODE,予定額1月,実績額1月,予定額2月,実績額2月,予定額3月,実績額3月,
予定額4月,実績額4月, 予定額5月,実績額5月,予定額6月,実績額6月,予定額7月,実績額7月,予定額8月,実績額8月,予定額9月,実績額9月,
予定額10月,実績額10月, 予定額11月,実績額11月,予定額12月,実績額12月
from 横持ち order by 社員CODE";
string sqlz = @"select top n 社員CODE
,SUM(CASE WHEN 月 = 01 and 予実='Y' THEN 額 else null END) 予定額1月
,SUM(CASE WHEN 月 = 01 and 予実='J' THEN 額 else null END) 実績額1月
,SUM(CASE WHEN 月 = 02 and 予実='Y' THEN 額 else null END) 予定額2月
,SUM(CASE WHEN 月 = 02 and 予実='J' THEN 額 else null END) 実績額2月
,SUM(CASE WHEN 月 = 03 and 予実='Y' THEN 額 else null END) 予定額3月
,SUM(CASE WHEN 月 = 03 and 予実='J' THEN 額 else null END) 実績額3月
,SUM(CASE WHEN 月 = 04 and 予実='Y' THEN 額 else null END) 予定額4月
,SUM(CASE WHEN 月 = 04 and 予実='J' THEN 額 else null END) 実績額4月
,SUM(CASE WHEN 月 = 05 and 予実='Y' THEN 額 else null END) 予定額5月
,SUM(CASE WHEN 月 = 05 and 予実='J' THEN 額 else null END) 実績額5月
,SUM(CASE WHEN 月 = 06 and 予実='Y' THEN 額 else null END) 予定額6月
,SUM(CASE WHEN 月 = 06 and 予実='J' THEN 額 else null END) 実績額6月
,SUM(CASE WHEN 月 = 07 and 予実='Y' THEN 額 else null END) 予定額7月
,SUM(CASE WHEN 月 = 07 and 予実='J' THEN 額 else null END) 実績額7月
,SUM(CASE WHEN 月 = 08 and 予実='Y' THEN 額 else null END) 予定額8月
,SUM(CASE WHEN 月 = 08 and 予実='J' THEN 額 else null END) 実績額8月
,SUM(CASE WHEN 月 = 09 and 予実='Y' THEN 額 else null END) 予定額9月
,SUM(CASE WHEN 月 = 09 and 予実='J' THEN 額 else null END) 実績額9月
,SUM(CASE WHEN 月 = 10 and 予実='Y' THEN 額 else null END) 予定額10月
,SUM(CASE WHEN 月 = 10 and 予実='J' THEN 額 else null END) 実績額10月
,SUM(CASE WHEN 月 = 11 and 予実='Y' THEN 額 else null END) 予定額11月
,SUM(CASE WHEN 月 = 11 and 予実='J' THEN 額 else null END) 実績額11月
,SUM(CASE WHEN 月 = 12 and 予実='Y' THEN 額 else null END) 予定額12月
,SUM(CASE WHEN 月 = 12 and 予実='J' THEN 額 else null END) 実績額12月
from 縦持ち group by 社員CODE order by 社員CODE;
データ取得は DataTable dt = ur.Execute(sql);
の形で、アプリにDataTableで取得する形式をとりました。
(*) Top nの nは 1～10000000 まで、10^nの形で指定しましたが、top 100,000以上は幾ら指定しても100,000件しか、DataTableに戻らないのですね。
もっとも、そんなに取得しても閲覧できないので、取得できても困りますが。
純粋な横持を取得すると次のようになりました。(単位:秒)
1件取得 00.0140000■取得件数:1
10件取得 00.0150000■取得件数:10
100件取得 00.0170000■取得件数:100
1000件取得 00.0480000■取得件数:1000
10000件取得 01.9130000■取得件数:10000
100000件取得 03.3340000■取得件数:100000
1000000件取得 02.9640000■取得件数:100000
10000000件取得 03.1790000■取得件数:100000
取得件数と処理時間の関連性は見られませんね。ネットワーク転送時間が大きな要因かもしれません。
1000件目と10,000件目の増加が大きいのは、処理のメモリー状態のReAllocなのか、なんなのか掴めてません。
縦持ちを横持ちに変換すると次のようになりました。(単位:秒)
1件取得 00.0040000■取得件数:1
10件取得 00.0080000■取得件数:10
100件取得 00.0420000■取得件数:100
1000件取得 00.3220000■取得件数:1000
10000件取得 21.9130000■取得件数:10000
100000件取得 22.7160000■取得件数:100000
1000000件取得 24.6870000■取得件数:100000
10000000件取得 21.4410000■取得件数:100000
横持ちに比べると、相関性が見えるような見えないような。
こちらも横持ち共に 1000と10000の比が 極端に大きいのは、ADOの動作のような感じですが、判りませんでした。
横持ちと縦横変換を比較すると 次のようになります。
横持ち 縦横変換
1件 00.014 00.004
10件 00.015 00.008
100件 00.017 00.042
1000件 00.048 00.322
10000件 01.913 21.913
100000件 03.334 22.716
100件のとき、縦横変換したほうが早かっり、10000件の時は 10倍以上、要したりしてます。
相関関係が一次的ではないですね。当初予想していたより差が小さかったので、妙に安心。 <-- 何が?
この程度の差でしたら、縦持ちにしておいて、見せかけ横持ちという構築でも、問題は少ないかなぁって思いました。(追記)1000件以下の時)