(長文御免)
前回は、トランザクションの単位と処理手順(一回のSQLに含まれる、実質行数)がざっくりだったので、
自動トランザクションと明示トランザクションに分け、１行挿入ストアードも作成して、比較してみました。
縦持ち
create table 縦持ち(
社員CODE char(10) ,
月 integer ,
予実 char , -- 'Y'予定 'J':実績
額 integer ,
)
横持ち
create table 横持ち (
社員CODE char(10) ,
予定額1月 integer,
実績額1月 integer,
予定額2月 integer,
実績額2月 integer,
予定額3月 integer,
実績額3月 integer,
予定額4月 integer,
実績額4月 integer,
予定額5月 integer,
実績額5月 integer,
予定額6月 integer,
実績額6月 integer,
予定額7月 integer,
実績額7月 integer,
予定額8月 integer,
実績額8月 integer,
予定額9月 integer,
実績額9月 integer,
予定額10月 integer,
実績額10月 integer,
予定額11月 integer,
実績額11月 integer,
予定額12月 integer,
実績額12月 integer
)
この項目で社員コードは 001～100の100人分を作成しました。
同じ業務内容を保持するためには、横持ちは 100行、縦持ちは2400行、必要になります。
作成方法は幾つが方法があります。
言語でのロジックは
自動トラントランザクションでは
Con.Open()
cmd.Connection = conn;
cmd.CommandText = sql;
DateTime dt1 = DateTime.Now;
cmd.ExecuteNonQuery(SQL);-------言語でLoopするときはこの部分がLooop
DateTime dt2 = DateTime.Now;
表示.Text += dt2.Subtract(dt1).ToString() + Environment.NewLine;
Con.Close()
トラントランザクションでは
Con.Open()
cmd.Connection = conn;
cmd.CommandText = sql;
DateTime dt1 = DateTime.Now;
tran=cmd.BeginTransaction
cmd.ExecuteNonQuery(SQL);-------言語でLoopするときはこの部分がLooop
tran.Commit
DateTime dt2 = DateTime.Now;
表示.Text += dt2.Subtract(dt1).ToString() + Environment.NewLine;
Con.Close()
で計測しました。
数字の単位は Secです。
■一括ストアード■
縦持ちは2400行設定: 横持ち100行設定 (実用性は欠ける)
Create PROCEDURE [dbo].[縦持挿入]
AS
BEGIN
insert into 縦持ち(社員CODE,月,予実,額) values('CODE001', 1,'Y',950496);
insert into 縦持ち(社員CODE,月,予実,額) values('CODE001', 1,'J',751511);
…(全2400行)……………………………………………………………………………………………
insert into 縦持ち(社員CODE,月,予実,額) values('CODE100', 12,'J',276403);
END
Create PROCEDURE [dbo].[横持挿入]
AS
BEGIN
insert into 横持ち(社員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月) values(
'CODE001',950496,751511,757902,692590,720890,297862,386594,459021,214716,961215,659211,274459,507169,672064,981869,275575,65960,195340,947885,186640,145613,301418,681952,526083);
………(全100行)………………………………………………………………………………………
END
■一括ストアード■結果
自動トランザクション トランザクション
横持ち 100行 0.023 0.005
縦持ち2400行 0.501 0.041
■一括SQL■
一回のcmd.ExecuteNonQuery(SQL)文で処理をするように、SQL文を組み立てる。
横持ちSQL =@"
insert into 横持ち(社員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月) values(
'CODE001',950496,751511,757902,692590,720890,297862,386594,459021,214716,961215,659211,274459,507169,672064,981869,275575,65960,195340,947885,186640,145613,301418,681952,526083
);
……(全100行)…………………………………………………………………………………………
insert into 横持ち(社員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月) values(
'CODE100',798940,485760,785407,20368,266,204167,246727,814783,602020,752102,354481,172038,884516,6462,778047,953103,495542,887090,377636,795883,28489,533900,398383,276403
);
";
横持ちSQL =@"
insert into 縦持ち(社員CODE,月,予実,額) values('CODE001', 1,'Y',950496);
insert into 縦持ち(社員CODE,月,予実,額) values('CODE001', 1,'J',751511);
……(全2400行)…………………………………………………………………………………………
";
■一括SQL■結果
自動トランザクション トランザクション
横持ち 100行 0.048 0.036
横持ち 100行*24 1.245 0.683
縦持ち2400行 0.643 0.188
■平文SQL投入■
パラメータを用いず、平文のSQLを生成し、1行毎に投入。プログラムで 100回. 1200回*2のLoop処理
横持ち
for(code =0; code<100;code++)
{
string teml = @"insert into 横持ち(社員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月) values(
'{0}',{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24});";
string code = string.Format("CODE{0:000}", code);
string sql = string.Format(teml, code, rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000),
rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000), rnd.Next(1000000)
);
ur.ExecuteNonQuery(sql);
}
縦持ち
for(code =0; code<100;code++)
{
for(m=1;m<=12;m++)
{
string 元Y = string.Format("insert into 縦持ち(社員CODE,月,予実,額) values('{0}', {1},'Y',{2});", code, m, rnd.Next(1000000));
string 元J = string.Format("insert into 縦持ち(社員CODE,月,予実,額) values('{0}', {1},'J',{2});", code, m, rnd.Next(1000000));
ur.Execute(元Y);
ur.Execute(元J);
}
}
■平文SQL投入■
自動トランザクション トランザクション
横持ち 100行 0.399 0.357
縦持ち2400行 9.294 8.105
■パラメータSQL■
平文SQLをパラメータ式で実行
横持ち
string teml = @"insert into 横持ち(社員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月) values(
@社員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月
);
縦持ち
string 元Y = string.Format("insert into 縦持ち(社員CODE,月,予実,額) values(@社員CODE,@月,@予実,@額);");
string 元J = string.Format("insert into 縦持ち(社員CODE,月,予実,額) values(@社員CODE,@月,@予実,@額);");
■パラメータSQL■
自動トランザクション トランザクション
横持ち 100行 0.077 0.039
縦持ち2400行 1.044 0.402
■一行ストアード■
横型
ALTER PROCEDURE [dbo].[横一行挿入]
@社員CODE char(10) ,
@予定額1月 int, @実績額1月 int,…@予定額12月 int, @実績額12月 int
AS
BEGIN
insert into 横持ち(社員CODE ,予定額1月 ,実績額1月… ,予定額12月,実績額12月
) values(@社員CODE,@予定額1月,@実績額1月…,@予定額12月,@実績額12月);
END
縦型
ALTER PROCEDURE [dbo].[縦一行挿入]
@社員CODE char(10) , @月 int, @予実 char(1), @額 int
AS
BEGIN
insert into 縦持ち(社員CODE,月,予実,額) values(@社員CODE,@月,@予実,@額)
END
のストアードを言語で1行毎に投入。プログラムで 100回. 1200回*2のLoop処理
■一行ストアード■結果
自動トランザクション トランザクション
横持ち 100行 0.057 0.033
縦持ち2400行 0.987 0.356
■■■■■■■■■■上記の結果を処理別に纏めてみました。
■一括ストアード■結果
自動トランザクション トランザクション
横持ち 100行 0.023 0.005 (改善比高い)
縦持ち2400行 0.501 0.041 (改善比高い)
■一括SQL■結果
自動トランザクション トランザクション
横持ち 100行 0.048 0.036
縦持ち2400行 0.643 0.188
(参考:100行を24回loopさせて2400行と比較してみました。これは意味が薄いので)
横持ち 100行*24 1.245 0.683
■平文SQL投入■
自動トランザクション トランザクション
横持ち 100行 0.399 0.357
縦持ち2400行 9.294 8.105 (改善比低い)
■パラメータSQL■
自動トランザクション トランザクション
横持ち 100行 0.077 0.039
縦持ち2400行 1.044 0.402
■一行ストアード■結果
自動トランザクション トランザクション
横持ち 100行 0.057 0.033
縦持ち2400行 0.987 0.356
●考察
明示的にトランザクションを張る方が早い。しかし改善比一律ではない､低いケースや高いケースもある。
行数による処理速度の差は、20倍提示のものから6倍程度のものがあり、単純な行数比にはならない。(1列の項目数の差もあるが、他の要因がありそうです。)
平文よりバラメータ式の方が早い。横持ちで5倍,縦持ちで10倍の差が開くのは、都度Compileするか否かの差なのか。件数が多いほど差は開く。
平文を投げるのは、コストが高いし、インジェクションの危険性が高いし、良くないと言えますね。
パラメータSQLを動的に生成するより、ストアードにする方が早いが、差は小さい。0.9程度。
100行対2400行でも、かなり違いが明確になりました。件数が多いと、更に特徴が出そうです。
単純挿入や更新ストアードに対する期待が薄らいでしまいました。動的にパラメータSQL文生成して投げても差が少ないので、ストアードの事前作成は、手間の割に効果薄そう。
挿入、更新もストアード化する標準化は、一考の余地あるかも。
自動トランザクションはトランザクション適用より早いと、思い込んでいたので、大いに勉強になりました。
明示トランザクション+ 縦持ちが私の結論かな。
平文2400行と パラメタSQL+明示トラン2400行の結果比は 9.294 : 0.402 => 23.11 : 1
すこし、手を加えるだけで、20倍もの改善があるのは、驚いた。SQL文は侮れないです。
(*)単純なモデルケースなので、参考にするときは、他の外部要件を加味して、判断して下さい。