2000件のデータ登録で2時間程掛かっている処理を、10分程度に短縮する方法を紹介…ができたらいいなぁ。ってのを、考察も交えて紹介します。
■擬似コード
・20回ほどの SELECT 文が呼び出されている(らしい)。
・1件のデータ登録で、20件のデータ挿入がある。
for i = 1 to 2000 何かと SELECT 処理がある。 前の SELECT の結果を利用して、次のパラメータとする...らしい。 時には if 文などがある。 SELECT1 SELECT2 ... SELECT20 帳票用のテーブルから該当レコードを DELETE する。 DELETE1 DELETE2 ... DELETE20 帳票用のテーブルに 20 件データ出力する INSERT1 INSERT2 ... INSERT20 next
この1回のループが4秒ぐらいなので、4秒×2000回 = 8000秒 ≒ 2時間超 という具合。
■考えられる対策
1.
データベースに SELECT を何度も呼び出しているが、これが毎回 SqlCommand 呼出となっている。なので、毎回データベースに接続する手間が掛かっているのでは？これを、できれば一回の SqlCommand 呼出にする。ただし、SELECT1 から SELECT20 までの間は if 文が入ったり、動的に SQL 文を作っていたりするので、必ずしもひとつにまとめられるとは限らない。
出来る限り、まとまることにする。
2.
帳票テーブルに関しては、DELETE のあとに INSERT している。INSERT を単独で呼び出すと遅くなるので（総計4万件になる）SqlBulkCopy を使って一括挿入する。
3.
その前に DELETE を何度も呼び出しているが、これは無駄なので、該当する ID をまとめるなどして1回だけの DELETE 呼出にする。
4.
実は、所々 DataTable を使っているので VB 側の処理が重たくなっている可能性もある。このあたりは、実測した後に、どちらのチューニングに重きをおくのか？あるは、アクセスチューニングによって、どのくらいの効果が見込めるのか、をあらかじめ考察しておく。
■実験用テーブル
プロジェクト案件テーブル
親子の案件があるので、親案件（parent_id）が自分自身を示す。
（これが今回の SQL を複雑にしている原因でもあるので、実験テーブルにいれておく）
CREATE TABLE [dbo].[t_anken]( [id] [int] NOT NULL, [name] [varchar](50) NOT NULL, [parent_id] [int] NULL, [userid] [varchar](10) NOT NULL, [updatedate] [datetime] NOT NULL ) ON [PRIMARY]
出力用の帳票テーブル
CREATE TABLE [dbo].[t_report]( [id] [int] NOT NULL, [name] [varchar](50) NOT NULL, [data] [varchar](500) NOT NULL, [userid] [varchar](10) NOT NULL, [updatedate] [datetime] NOT NULL ) ON [PRIMARY]
■データ挿入用コード
20万件のデータ挿入は10秒弱ぐらいで終わります。
private string toMD5(string s)
{
//文字列をbyte型配列に変換する
byte[] data = System.Text.Encoding.UTF8.GetBytes(s);
//MD5CryptoServiceProviderオブジェクトを作成
System.Security.Cryptography.MD5CryptoServiceProvider md5 =
new System.Security.Cryptography.MD5CryptoServiceProvider();
//または、次のようにもできる
//System.Security.Cryptography.MD5 md5 =
// System.Security.Cryptography.MD5.Create();
//ハッシュ値を計算する
byte[] bs = md5.ComputeHash(data);
//byte型配列を16進数の文字列に変換
System.Text.StringBuilder result = new System.Text.StringBuilder();
return BitConverter.ToString(bs).ToLower().Replace("-", "");
}
// 接続文字列
const string CNSTR =
@"Data Source=.\sqlexpress;Initial Catalog=stress;Integrated Security=True;Pooling=False";
const int ANKENMAX = 200000;
private void button1_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(CNSTR);
// テーブル内容を削除
SqlCommand cmd = new SqlCommand();
cmd.CommandText =
"DELETE FROM t_anken \n" +
"DELETE FROM t_report";
cmd.Connection = cn;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
// データ作成
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM t_anken WHERE 1 = 0", cn);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < ANKENMAX; i++)
{
DataRow row = dt.NewRow();
row["id"] = i;
row["name"] = toMD5(i.ToString() + DateTime.Now.ToString());
row["parent_id"] = DBNull.Value;
row["userid"] = "masuda";
row["updatedate"] = DateTime.Now;
dt.Rows.Add(row);
}
SqlBulkCopy bc = new SqlBulkCopy(cn);
bc.DestinationTableName = "t_anken";
cn.Open();
bc.WriteToServer(dt);
cn.Close();
bc.Close();
MessageBox.Show(string.Format("t_anken に {0} 件データ挿入しました", ANKENMAX));
}
続きは明日以降で
