.NET を使って SQL Server に対して SELECT するには、ってことでパフォーマンスのチェックをしました。
- 10 個のテーブルに 20000 件ずつデータを入れておきます。
- 10 個のテーブルに対して、2000 回ずつ検索します。
- テーブルには主キーを付けておきます。
という形でデータを作っておいて、以下のパターンで検索をします。
- 個別に SqlDataAdapter を使って呼び出し
- 10 個のテーブルの検索をひとつにまとめて、SqlDataAdapter で呼び出し
- 10 個のテーブルの検索をひとつにまとめて、SqlCommand で呼び出し
- LINQ to Entities を使って呼び出し
- 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
結論から言えば、
- 個別SELECTよりも、LINQ を使うと 2 倍ぐらい遅くなる。
- LINQ to Entities と LINQ to SQL はスピードが変わらない。
- 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 を使ったほうがよいでしょうってことで。
