SELECT のパフォーマンスチェック（続き）

投稿日: 2011/06/01 作成者: masuda

.NET を使って SQL Server に対して SELECT するには、ってことでパフォーマンスのチェックをしました。

  1. 10 個のテーブルに 20000 件ずつデータを入れておきます。
  2. 10 個のテーブルに対して、2000 回ずつ検索します。
  3. テーブルには主キーを付けておきます。

という形でデータを作っておいて、以下のパターンで検索をします。

  1. 個別に SqlDataAdapter を使って呼び出し
  2. 10 個のテーブルの検索をひとつにまとめて、SqlDataAdapter で呼び出し
  3. 10 個のテーブルの検索をひとつにまとめて、SqlCommand で呼び出し
  4. LINQ to Entities を使って呼び出し
  5. LINQ to SQL を使って呼び出し
1.個別SELECT
56.5
59.3
57.5

2.まとめてSELECT
※ 1 より遅くなったのでパス

3.SqlCommand
7.6
7.5
7.4

4.LINQ to Entities
112.5
110.6
108.2

5.LINQ to SQL
92.4
112.3
93.9

結論から言えば、

  1. 個別SELECTよりも、LINQ を使うと 2 倍ぐらい遅くなる。
  2. LINQ to Entities と LINQ to SQL はスピードが変わらない。
  3. SqlCommand を使うと、個別SELECTよりも 5 倍以上、LINQ よりも 10 倍以上早い。

という結果になりました。

LINQ の場合、10 個のテーブルに対して、検索を実行するために 10 回呼び出してしまうので、この手の方法は不利なのですが、SqlDataAdapter よりも遅いのは意外です…スピードが要求されるところで、LINQ を使うのは禁物ですね。

パフォーマンスのチューニングとしては、SqlCommand が一番やりやすく、10 個のテーブルに対する SELECT を 1 回で済ませられるのも SQL Server で複数の検索結果（DataTable）を返せるからです。
また、prepared statements という形で、あらかじめ SQL 文を発行させて再利用できるとろも SqlCommand の有利なところですね。

実験ソースを晒しておきます。

■テーブルの作成 

/// <summary>
/// テーブル作成
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
    string sql = "";
    SqlConnection cn = new SqlConnection(CNSTR);
    cn.Open();
    SqlCommand cmd;
    for (int i = 0; i < 10; i++)
    {
        sql = string.Format("drop table table{0}", i);
        cmd = new SqlCommand(sql, cn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch { }

        sql = string.Format("create table table{0} (", i);
        for (int j = 0; j < 10; j++)
        {
			if (j == 0)
			{
				sql += string.Format("col{0} varchar(50) not null,", j);
			}
			else
			{
				sql += string.Format("col{0} varchar(50),", j);
			}
        }
		sql += string.Format(" CONSTRAINT pk_table{0} PRIMARY KEY ( col0 ) ", i );
        sql += ")";
        cmd = new SqlCommand(sql, cn);
        cmd.ExecuteNonQuery();
    }
    cn.Close();
}

■20000件のデータ insert 

/// <summary>
/// データ作成
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
    int max = int.Parse(textBox1.Text);
    SqlConnection cn = new SqlConnection(CNSTR);
    cn.Open();

    string s = toMD5(DateTime.Now.ToString());
    int count = 0;
    for (int i = 0; i < 10; i++)
    {
        string sql = string.Format("insert into table{0} values (", i);
        SqlCommand cmd = new SqlCommand("", cn);
        for (int k = 0; k < 10; k++)
        {
            cmd.Parameters.Add(new SqlParameter(
                string.Format("@col{0}", k), SqlDbType.VarChar, 50));
            sql += string.Format("@col{0},", k);
        }
        sql = sql.Substring(0, sql.Length - 1);
        sql += ")";
        cmd.CommandText = sql;

        for (int j = 0; j < max; j++)
        {
            for (int k = 0; k < 10; k++)
            {
                s = toMD5(s);
                cmd.Parameters[k].Value = s;
            }
            cmd.ExecuteNonQuery();
            count++;
            if (count % 100 == 0)
            {
                toStatus(count, max * 10);
            }
        }
    }
    cn.Close();
}

■個別SELECT

まずいやり方ですが、string.Format で値込みで文字列を作っています。
よくあるパターンですね。 

/// <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");
}

■まとめてSELECT

SqlDataAdapter に渡す SQL 文をひとまとめにしたのですが、全然駄目です。 

/// <summary>
/// まとめてSELECT
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <remarks>
/// 遅くてぜんぜんだめ
/// </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 を使う

あらかじめコンパイルされるように SqlCommand を使います。
ループの中で 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 = "";
</p>
<p>
    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;
</p>
<p>
    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");
}

■LINQ to Entities

比較のために LINQ to Entities でも作ってみます。
LINQ の場合は、複数の SELECT 文をまとめることができないので、ループの中で 10 回呼び出します。この方法は、SqlDataAdapter と同じですよね。
スピードを比較したものがないので、初めて試してみたのですが、個別SELECTよりも2倍ほどおそくなります。 

/// <summary>
/// LINQ to Entities の利用
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button6_Click(object sender, EventArgs e)
{
	// 律儀にSELECT文を10回発行します。
	DateTime start = DateTime.Now;
	string s = toMD5(start.ToString());
	int count = 0;

	stressEntities ent = new stressEntities();
	int max = 2000;
	for (int i = 0; i < max; i++)
	{
		var items0 = from t in ent.table0
					where t.col0 == s
					select t;
		s = toMD5(s);
		var item1 = from t in ent.table1
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items2 = from t in ent.table2
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items3 = from t in ent.table3
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items4 = from t in ent.table4
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items5 = from t in ent.table5
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items6 = from t in ent.table6
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items7 = from t in ent.table7
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items8 = from t in ent.table8
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items9 = from t in ent.table9
						where t.col0 == s
						select t;
		s = toMD5(s);

		var it0 = items0.SingleOrDefault();
		var it1 = items0.SingleOrDefault();
		var it2 = items0.SingleOrDefault();
		var it3 = items0.SingleOrDefault();
		var it4 = items0.SingleOrDefault();
		var it5 = items0.SingleOrDefault();
		var it6 = items0.SingleOrDefault();
		var it7 = items0.SingleOrDefault();
		var it8 = items0.SingleOrDefault();
		var it9 = items0.SingleOrDefault();

		if (++count % 10 == 0)
			toStatus(count, max);
	}
	DateTime end = DateTime.Now;
	textBox2.Text = ((TimeSpan)(end - start)).TotalSeconds.ToString("#.0");
}

■LINQ to SQL を利用

比較のために LINQ to SQL でも動かしてみます。
不利なのは、LINQ to Entities と同じで、10 回呼び出さないといけないところですね。
スピードに関しては、LINQ to Entities と変わりません。 

/// <summary>
/// LINQ to SQL の利用
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button7_Click(object sender, EventArgs e)
{
	// 律儀にSELECT文を10回発行します。
	DateTime start = DateTime.Now;
	string s = toMD5(start.ToString());
	int count = 0;

    SqlConnection cn = new SqlConnection(CNSTR);
	DataClasses1DataContext cnt = new DataClasses1DataContext(cn);

	int max = 2000;
	for (int i = 0; i < max; i++)
	{
		var items0 = from t in cnt.Dtable0
						where t.col0 == s
						select t;
		s = toMD5(s);
		var item1 = from t in cnt.Dtable1
					where t.col0 == s
					select t;
		s = toMD5(s);
		var items2 = from t in cnt.Dtable2
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items3 = from t in cnt.Dtable3
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items4 = from t in cnt.Dtable4
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items5 = from t in cnt.Dtable5
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items6 = from t in cnt.Dtable6
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items7 = from t in cnt.Dtable7
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items8 = from t in cnt.Dtable8
						where t.col0 == s
						select t;
		s = toMD5(s);
		var items9 = from t in cnt.Dtable9
						where t.col0 == s
						select t;
		s = toMD5(s);

		var it0 = items0.SingleOrDefault();
		var it1 = items0.SingleOrDefault();
		var it2 = items0.SingleOrDefault();
		var it3 = items0.SingleOrDefault();
		var it4 = items0.SingleOrDefault();
		var it5 = items0.SingleOrDefault();
		var it6 = items0.SingleOrDefault();
		var it7 = items0.SingleOrDefault();
		var it8 = items0.SingleOrDefault();
		var it9 = items0.SingleOrDefault();

		if (++count % 10 == 0)
			toStatus(count, max);
	}
	DateTime end = DateTime.Now;
	textBox2.Text = ((TimeSpan)(end - start)).TotalSeconds.ToString("#.0");
}

そんな訳で、実行時のパフォーマンスが気にかかる場合は、SqlCommand にすると 10 倍ほど早くなるよ、という話です。逆に言えば、たいしてパフォーマンスが気にならないときは、書きやすい LINQ を使ったほうがよいでしょうってことで。

カテゴリー: 開発, C# パーマリンク