SELECT のパフォーマンスチューニング（メモ）

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++ で書き直すと早くなるのかな？