C#でNpgsqlを使ってPostgreSQLへ大量のINSERTで速度比較

一通り解説が終わったのでNpgsqlCommandのPrepareメソッドや接続時に「Max Auto Prepare」を定義する事でどの程度の効果があるのか検証してみます。

また、検証にはトランザクション有無の検証もしてみました。

トランザクションはご存知の通り、まとめて多くの処理を実行するが途中でエラーが発生した時は処理開始前に戻すのが目的でありパフォーマンスアップが目的ではありませんが、その処理内容から速度が向上するのは容易に想像できるかと思います。

• もくじ
  • 通常のINSERT
  • パラメータを使う
  • パラメータを使い接続文字列に「Max Auto Prepare」を設定
  • パラメータを使いPrepareメソッドを使用
  • NpgsqlDataAdapterとクエリ文
  • NpgsqlDataAdapterとNpgsqlCommandBuilder
  • 計測データのまとめ

以下の解説は以前紹介した手法と同じですが、まだ理解されてない方は下記記事を参考にしてください。

Npgsqlの本家情報は

NpgsqlのPrepareメソッドについて

私の作成するサンプルソースファイルは

基本的なテーブルは下記構成となります。

テーブル名	型	概要
id	serial	自動的にセットされる通し番号
time	timestamp	トランザクション開始時刻または入力された日付
name	text	任意の文字列
numeric	integer	任意の数値

実験はカラム名「name」とカラム名「numeric」のINSERTを10万回行う実験です。

尚、純粋な処理時間が知りたかったので同一PC上で行っており、ネットワークトラフィックに左右されない時間となります。

それ以外にもメールソフトを終了させたりして不用意に負荷が上がらないようにしています。

通常のINSERT

まずはベタなクエリ文からやってみます。

計測データは12回計測し、一番良い結果と一番悪い結果を除いた10回の計測平均値です。

using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public");
con.Open();
//using NpgsqlTransaction tran = con.BeginTransaction();
using NpgsqlCommand cmd = new();
cmd.Connection = con;
for (int i = 0; i < 100000; i++)
{
	cmd.CommandText = $"INSERT INTO data(name, numeric) VALUES ('name_{i}', {i});";
	_ = cmd.ExecuteNonQuery();
}
//tran.Commit();

ごくありふれた処理でループ変数をINSERTするデータとして使用しています。

サンプルコードではトランザクションはコメントアウトされてますが、トランザクション検証時は有効にしています。

普通のINSERT	トランザクション有
15.767秒	6.726秒

さすがにクエリ文を10万回は時間がかかっていますが、これが基準となる時間になります。

また、トランザクションはNpgsqlCommandのExecuteNonQueryメソッドでデータを送るだけでNpgsqlTransactionのCommitメソッドで実際にINSERTが行われ一気に書き込みが行われる分だけ効率よく処理されトランザクションなしの約42%とかなり速度の改善はできました。

再度言いますがトランザクションの本来の目的とは違うのだけは忘れないでください。

当たり前ですがクエリ文をベタに送っているので「Max Auto Prepare」や「Prepare」の記述を行っても効果は全くありません。

パラメータを使う

次に変更するデータをパラメータ化して、パラメータの値を変化させてINSERTした場合は通常のINSERTと違うのかを検証します。

using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public");
con.Open();
//using NpgsqlTransaction tran = con.BeginTransaction();
using NpgsqlCommand cmd = new($"INSERT INTO data(name, numeric) VALUES(@insert_name, @insert_numeric);", con);
// NpgsqlCommandのParametersに新しいNpgsqlParameterを作成
_ = cmd.Parameters.Add(new NpgsqlParameter("insert_name", DbType.String));
_ = cmd.Parameters.Add(new NpgsqlParameter("insert_numeric", DbType.Int32));
for (int i = 0; i < 100000; i++)
{
    cmd.Parameters["insert_name"].Value = $"name_{i}";
    cmd.Parameters["insert_numeric"].Value = i;
    _ = cmd.ExecuteNonQuery();
}
//tran.Commit();

結果は以下となりますが、ベタなクエリ文を実行するより遅くなりました。

パラメータ使用INSERT	トランザクション有
16.226秒	7.307秒

パラメータとベースになるクエリ文の解析など、最初のベタなクエリ文より手間が増える分だけ時間がかかっていると思われます。

とは言えSQLインジェクションを考えると避けては通れない手法でもあります。

パラメータを使い接続文字列に「Max Auto Prepare」を設定

パフォーマンスアップの本命となる手段の1つです。

using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public; Max Auto Prepare=1");
con.Open();
//using NpgsqlTransaction tran = con.BeginTransaction();
using NpgsqlCommand cmd = new($"INSERT INTO data(name, numeric) VALUES(@insert_name, @insert_numeric);", con);
// NpgsqlCommandのParametersに新しいNpgsqlParameterを作成
_ = cmd.Parameters.Add(new NpgsqlParameter("insert_name", DbType.String));
_ = cmd.Parameters.Add(new NpgsqlParameter("insert_numeric", DbType.Int32));
System.Diagnostics.Stopwatch sw = new();
sw.Start();
for (int i = 0; i < 100000; i++)
{
	cmd.Parameters["insert_name"].Value = $"name_{i}";
	cmd.Parameters["insert_numeric"].Value = i;
	_ = cmd.ExecuteNonQuery();
}
//tran.Commit();

パラメータを使う手法の接続文字列の最後に「Max Auto Prepare=1」を加えただけでそれ以外の変更はありません。

「Max Auto Prepare」って何？と言う方は下記リンクの記事を参考にしてください。

結果は速度向上しました。

Max Auto Prepareのみ	トランザクション有
12.351秒	3.992秒

通常のクエリ文を使ったINSERTと比較すると処理時間は約78%となりトランザクションあり比較では60%、トランザクションなしのINSERTと今回のトランザクションありでは処理時間が約1/4になりました。

ここまで効果が現れるとやり甲斐があります。

パラメータを使いPrepareメソッドを使用

「Max Auto Prepare」がお任せの効率アップならPrepareメソッドは自発的な効率アップになります。

しかも「Max Auto Prepare」は設定された以上の回数から初めて効果を発揮しますが、Prepareメソッドは最初から効果を発揮します。

using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public");
con.Open();
//using NpgsqlTransaction tran = con.BeginTransaction();
using NpgsqlCommand cmd = new($"INSERT INTO data(name, numeric) VALUES(@insert_name, @insert_numeric);", con);
// NpgsqlCommandのParametersに新しいNpgsqlParameterを作成
_ = cmd.Parameters.Add(new NpgsqlParameter("insert_name", DbType.String));
_ = cmd.Parameters.Add(new NpgsqlParameter("insert_numeric", DbType.Int32));
cmd.Prepare();
for (int i = 0; i < 100000; i++)
{
	cmd.Parameters["insert_name"].Value = $"name_{i}";
	cmd.Parameters["insert_numeric"].Value = i;
	_ = cmd.ExecuteNonQuery();
}
//tran.Commit();

パラメータを加えたらPrepareメソッドを実行するだけです。

結果はMax Auto Prepareと同等の時間となりました。

Prepareメソッドのみ	トランザクション有
12.415秒	3.911秒

理論上一番早いと思ってましたが「Max Auto Prepare」に微妙に負けてますｗ

Prepareメソッドの名誉のためにフォローすると一番早いスコア比較はMax Auto Prepareが11.2秒に対してPrepareメソッドが10.7秒なので、もしかしたら何か別の負荷があったのかもしれません。

いずれにしてもどちらかを使えば速度向上が期待できるのが分かりました。

NpgsqlDataAdapterとクエリ文

次は遅いと言われがちなDataTableを使ってみます。

using DataTable dt = new();
using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public; Max Auto Prepare=1");
con.Open();
using NpgsqlDataAdapter nda = new("SELECT name, numeric FROM data;", con);
var result = nda.Fill(dt);
//using NpgsqlTransaction tran = con.BeginTransaction();
for (int i = 0; i < 100000; i++)
{
	DataRow addRow = dt.NewRow();
	addRow[dt.Columns[0].ColumnName] = $"name_{i}";
	addRow[dt.Columns[1].ColumnName] = i;
	dt.Rows.Add(addRow);
}
// データベースにINSERTするNpgsqlCommandを追加
using NpgsqlCommand insertCommand = new();
insertCommand.Connection = con;
insertCommand.CommandText = "INSERT INTO data(name, numeric) VALUES(@Name, @Numeric);";
// 「name」をINSERTするNpgsqlParameterを作成して追加
NpgsqlParameter insertName = new();
insertName.ParameterName = "@Name";
insertName.SourceColumn = "name";
_ = insertCommand.Parameters.Add(insertName);
// 「numeric」をINSERTするNpgsqlParameterを作成して追加
NpgsqlParameter insertNumeric = new();
insertNumeric.ParameterName = "@Numeric";
insertNumeric.SourceColumn = "numeric";
_ = insertCommand.Parameters.Add(insertNumeric);
// NpgsqlDataAdapterのInsertCommandに追加
nda.InsertCommand = insertCommand;
result = nda.Update(dt);
//tran.Commit();

データのINSERTが目的なのでNpgsqlDataAdapterのUpdateメソッド実行の時間だけを計測してみました。

10万個のデータをDataTableに追加するのも気になりましたが、計測してみたら0.15秒と、Updateメソッド実行時間と比べたら1/100程度で10回のデータ取得のバラつき以下なので、所要時間にはあまり影響ないです。

NpgsqlDataAdapterのみ	トランザクション有
16.987秒	7.733秒

今回の計測では一番遅い時間となりました。

とは言え、通常のINSERTと比較して約7%、パラメータを使用したINSERTと比較しても約5%程度の遅さなので致命的とまではいかないと思います。

また、接続文字列に「Max Auto Prepare」を加えると

NpgsqlDataAdapterのみ	トランザクション有
13.034秒	4.297秒

ここでも「Max Auto Prepare」の効果ははっきり出てきました。

NpgsqlDataAdapterとNpgsqlCommandBuilder

最後はNpgsqlCommandBuilderでINSERTのクエリ文を自動生成した時の実験です。

using DataTable dt = new();
using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public; Max Auto Prepare=1");
con.Open();
using NpgsqlDataAdapter nda = new("SELECT name, numeric FROM data;", con);
_ = nda.Fill(dt);
//using NpgsqlTransaction tran = con.BeginTransaction();
for (int i = 0; i < 100000; i++)
{
	DataRow addRow = dt.NewRow();
	addRow[dt.Columns[0].ColumnName] = $"name_{i}";
	addRow[dt.Columns[1].ColumnName] = i;
	dt.Rows.Add(addRow);
}
using NpgsqlCommandBuilder cb = new(nda);
// これで実行されているクエリ文が分かる「INSERT INTO \"db_PostgreTest\".\"public\".\"data\" (\"name\", \"numeric\") VALUES (@p1, @p2)」
//string str = cb.GetInsertCommand().CommandText;
_ = nda.Update(dt);
//tran.Commit();

自動生成されたクエリ文を見ると普通に自分で各クエリ文と同じですね。

NpgsqlDataAdapterとNpgsqlCommandBuilder	トランザクション有
16.338秒	7.912秒

自分でクエリ文を書いた処理より少し早いですが、ほぼ誤差範囲と言ってもいいレベルです。

また、接続文字列に「Max Auto Prepare」を加えると

NpgsqlDataAdapterとNpgsqlCommandBuilder	トランザクション有
12.504秒	4.935秒

今回も効果がある事が確認できました。

結論としては、どの手法も大きく変わる事はなく、「Max Auto Prepare」や「Prepareメソッド」を使用する事で速度アップを望める事が分かりました。

計測データのまとめ

表示の制約があるのでデータを縦にまとめてみます。

トランザクションの有無→	無	有
普通のINSER	15.767秒	6.726秒
パラメータを使用したINSERT	16.226秒	7.307秒
Max Auto Prepare	12.351秒	3.992秒
Prepareメソッド	12.415秒	3.911秒
NpgsqlDataAdapter	16.987秒	7.733秒
NpgsqlDataAdapterとMax Auto Prepare	13.034秒	4.297秒
NpgsqlCommandBuilder	16.338秒	7.912秒
NpgsqlCommandBuilderとMax Auto Prepare	12.504秒	4.935秒

もっと早くINSERTしたい！と言う方は少し前の記事ですが、下記リンクの記事を参考にしてください。

今回の最速スコアの約1/10でINSERTができます。