SELECT のパフォーマンスチューニング（メモ）
ちょっと、メモ的に書き下しておきます。
1. 10 個のテーブルに 20000 件ずつデータを入れておきます。
2. 10 個のテーブルに対して、2000 回ずつ検索します。
というパターンがあったとき、どうやると早いでしょうか？という問題。
■1回ずつ SqlDataAdapter を呼び出す
だいたい 200 秒ぐらいかかります。
/// <summary>
/// 個別SELECT
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
// 律儀にSELECT文を10回発行します。
DateTime start = DateTime.Now;
string s = toMD5(start.ToString());
int count = 0;
SqlConnection cn = new SqlConnection(CNSTR);
int max = 2000;
for (int i = 0; i < max; i++)
{
for ( int j=0; j<10; j++ ) {
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(
string.Format("SELECT * FROM table{0} WHERE col0 = '{1}'", j, s), cn);
s = toMD5(s);
da.Fill( dt );
}
if (++count % 10 == 0)
toStatus(count, max);
}
DateTime end = DateTime.Now;
textBox2.Text = ((TimeSpan)(end - start)).TotalSeconds.ToString("#.0");
}
■10回の呼び出しをひとつにまとめる
SQL Server の場合、複数の検索結果を取れるので DataSet を使って 1 回にまとめます。
SQL Server 2008 だと 10 倍ぐらい早くなって 15 秒程度なんですが、
SQL Server 2000 だと、最初のパターンよりも遅くなってしまうんですよね…何故だろう？
/// <summary>
/// まとめてSELECT
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <remarks>
/// 遅くてぜんぜんだめ
/// SQL Server 2008 だと ok なのだが、SQL Server 2000 だと駄目らしい。
/// </remarks>
private void button4_Click(object sender, EventArgs e)
{
// 10回のSELECTをひとまとめにして DataSet に保存します。
DateTime start = DateTime.Now;
string s = toMD5(start.ToString());
int count = 0;
SqlConnection cn = new SqlConnection(CNSTR);
string sql = "";
int max = 2000;
for (int i = 0; i < max; i++)
{
for (int j = 0; j < 10; j++)
{
sql += string.Format("SELECT * FROM table{0} WHERE col0 = '{1}' ", j, s);
s = toMD5(s);
}
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, cn);
da.Fill(ds);
if (++count % 10 == 0)
toStatus(count, max );
}
DateTime end = DateTime.Now;
textBox2.Text = ((TimeSpan)(end - start)).TotalSeconds.ToString("#.0");
}
■あらかじめ SqlCommand を作成する
prepared sql statement ということで、あらかじめ SqlCommand で作成しておきます。
これをやると、12,3 秒になります。
ループの中で SqlCommand を作ると早くならないので注意が必要ですね。
/// <summary>
/// SqlCommand の利用
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button5_Click(object sender, EventArgs e)
{
// 10回のSELECTをひとまとめにして DataSet に保存します。
DateTime start = DateTime.Now;
string s = toMD5(start.ToString());
int count = 0;
SqlConnection cn = new SqlConnection(CNSTR);
string sql = "";
SqlCommand cmd = new SqlCommand("",cn);
for (int j = 0; j < 10; j++)
{
sql += string.Format("SELECT * FROM table{0} WHERE col0 = @param{1} ", j, j);
s = toMD5(s);
cmd.Parameters.Add(new SqlParameter(
string.Format("@param{0}", j), SqlDbType.VarChar, 50));
}
cmd.CommandText = sql;
int max = 2000;
for (int i = 0; i < max; i++)
{
for (int j = 0; j < 10; j++)
{
cmd.Parameters[j].Value = s;
s = toMD5(s);
}
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
if (++count % 10 == 0)
toStatus(count, max);
}
DateTime end = DateTime.Now;
textBox2.Text = ((TimeSpan)(end - start)).TotalSeconds.ToString("#.0");
}
更に高速化する場合はどうするんでしょうね？
.NET の文だけおそくなるから、C++ で書き直すと早くなるのかな？
