C#でNpgsqlを使ってPostgreSQLへ大量のUPDATEで速度比較-なべひろBlog

INSERT、SELECTと来たら次はUPDATEです。

今回も色々なパターンで処理時間を計測してみます。

尚、計測時間はUPDATEにかかった時間のみで事前にSELECTしたりSELECTした内容の編集は含まれていません。

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

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

UPDATEに関してはNpgsqlの技法と言うよりはクエリ文の技法紹介なので紹介していませんがNpgsqlDataAdapterを使う場合の基本は下記記事を参考にしてください。

Npgsqlの本家情報は

NpgsqlのPrepareメソッドについて

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

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

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

実験は既に10万件のデータがINSERTされた状態のテーブルのデータ取得です。

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

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

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

通常のUPDATE

まずは全データを取得しListに入れた後に編集し、そのデータを反映させます。

List<(int id, DateTime time, string name, int numeric)> selectData = new();
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 NpgsqlCommand cmd = new("SELECT * FROM data;", con);
using (NpgsqlDataReader rd = cmd.ExecuteReader())
{
    while (rd.Read())
    {
        selectData.Add(new(rd.GetInt32("id"), rd.GetDateTime("time"), rd.GetString("name"), rd.GetInt32("numeric")));
    }
}
// SELECTしたデータを更新
for (int i = 0; i < 100000; i++)
{
    selectData[i] = new(selectData[i].id, selectData[i].time, selectData[i].name.Replace("name", "1name"), selectData[i].numeric);
}
//using NpgsqlTransaction tran = con.BeginTransaction();
for (int i = 0; i < 100000; i++)
{
    cmd.CommandText = $"UPDATE data SET name = '{selectData[i].name}' WHERE id = {i + 1};";
    _ = cmd.ExecuteNonQuery();
}
//tran.Commit();

string..Replaceメソッドでカラム名「name」の情報を編集した後にidが一致している条件でUPDATEしています。

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

普通のUPDATE	トランザクション有
19.605秒	10.189秒

同一PCを内の動作とは言え10万件の処理はやっぱり時間がかかりますね。

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

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

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

パラメータを使う

UPDATEする値とWHERE条件の値をパラメータとして実験してみます。

List<(int id, DateTime time, string name, int numeric)> selectData = new();
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 NpgsqlCommand cmd = new("SELECT * FROM data;", con);
using (NpgsqlDataReader rd = cmd.ExecuteReader())
{
	while (rd.Read())
	{
		selectData.Add(new(rd.GetInt32("id"), rd.GetDateTime("time"), rd.GetString("name"), rd.GetInt32("numeric")));
	}
}
// SELECTしたデータを更新
for (int i = 0; i < 100000; i++)
{
	selectData[i] = new(selectData[i].id, selectData[i].time, selectData[i].name.Replace("name", "1name"), selectData[i].numeric);
}
//using NpgsqlTransaction tran = con.BeginTransaction();
cmd.CommandText = "UPDATE data SET name = @name WHERE id = @id;";
_ = cmd.Parameters.Add(new NpgsqlParameter("id", DbType.Int32));
_ = cmd.Parameters.Add(new NpgsqlParameter("name", DbType.String));
for (int i = 0; i < 100000; i++)
{
	cmd.Parameters["id"].Value = i + 1;
	cmd.Parameters["name"].Value = selectData[i].name;
	_ = cmd.ExecuteNonQuery();
}
//tran.Commit();

データの取得と変更内容は前回と同じです。

パラメータ使用UPDATE	トランザクション有
22.874秒	11.827秒

ほぼ予想していた結果となりました。

ベタにクエリ文を書くよりパラメータを当てはめクエリ文を生成する方が処理が多くなるかと思われます。

何度か試してみましたが、通常のUPDATEより良いスコアになる事はありませんでした。

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

効率アップの最初の手段はMax Auto Prepareの設定です。

List<(int id, DateTime time, string name, int numeric)> selectData = 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 NpgsqlCommand cmd = new("SELECT * FROM data;", con);
using (NpgsqlDataReader rd = cmd.ExecuteReader())
{
    while (rd.Read())
    {
    selectData.Add(new(rd.GetInt32("id"), rd.GetDateTime("time"), rd.GetString("name"), rd.GetInt32("numeric")));
    }
}
// SELECTしたデータを更新
for (int i = 0; i < 100000; i++)
{
    selectData[i] = new(selectData[i].id, selectData[i].time, selectData[i].name.Replace("name", "1name"), selectData[i].numeric);
}
//using NpgsqlTransaction tran = con.BeginTransaction();
cmd.CommandText = "UPDATE data SET name = @name WHERE id = @id;";
_ = cmd.Parameters.Add(new NpgsqlParameter("id", DbType.Int32));
_ = cmd.Parameters.Add(new NpgsqlParameter("name", DbType.String));
for (int i = 0; i < 100000; i++)
{
    cmd.Parameters["id"].Value = i + 1;
    cmd.Parameters["name"].Value = selectData[i].name;
    _ = cmd.ExecuteNonQuery();
}
//tran.Commit();

１つ前の実験と違うのは接続文字列に「Max Auto Prepare」があるだけです。

Max Auto Prepareのみ	トランザクション有
13.709秒	4.905秒

「Max Auto Prepare」を追加する事で約60%の処理時間となり改善されました。

またトランザクションを加える事で最初の何も効率アップの処理を含んでない状態の約21%の処理時間となり大幅に改善されました。

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

パラメータを設定した後にPrepareメソッドを実行します。

List<(int id, DateTime time, string name, int numeric)> selectData = new();
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 NpgsqlCommand cmd = new("SELECT * FROM data;", con);
using (NpgsqlDataReader rd = cmd.ExecuteReader())
{
    while (rd.Read())
    {
        selectData.Add(new(rd.GetInt32("id"), rd.GetDateTime("time"), rd.GetString("name"), rd.GetInt32("numeric")));
    }
}
// SELECTしたデータを更新
for (int i = 0; i < 100000; i++)
{
    selectData[i] = new(selectData[i].id, selectData[i].time, selectData[i].name.Replace("name", "1name"), selectData[i].numeric);
}
//using NpgsqlTransaction tran = con.BeginTransaction();
cmd.CommandText = "UPDATE data SET name = @name WHERE id = @id;";
_ = cmd.Parameters.Add(new NpgsqlParameter("id", DbType.Int32));
_ = cmd.Parameters.Add(new NpgsqlParameter("name", DbType.String));
cmd.Prepare();
for (int i = 0; i < 100000; i++)
{
    cmd.Parameters["id"].Value = i + 1;
    cmd.Parameters["name"].Value = selectData[i].name;
    _ = cmd.ExecuteNonQuery();
}
//tran.Commit();

今回の実験ではベストスコアとなりました。

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

「Max Auto Prepare」を使った手法より若干高速になりましたが、検証を行うタイミングで変わるかもしれません。

トランザクションを使った実験ではわずか0.3秒の違いなのでほぼ同等と考えるのが妥当かと思います。

NpgsqlDataAdapterとクエリ文

私の感想としては意外と健闘しているNpgsqlDataAdapterです。

トランザクションの有無、「Max Auto Prepare」の有無での比較データです。

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 * FROM data;", con);
_ = nda.Fill(dt);
for (int i = 0; i < 100000; i++)
{
    dt.Rows[i][2] = $"{i}{dt.Rows[i][2]}";
}
//using NpgsqlTransaction tran = con.BeginTransaction();
// データベースにUPDATEするNpgsqlCommandを追加
using NpgsqlCommand updateCommand = new();
updateCommand.Connection = con;
updateCommand.CommandText = "UPDATE data SET name = @name WHERE id = @id;";
// 「name」をUPDATEするNpgsqlParameterを作成して追加
NpgsqlParameter updateName = new();
updateName.ParameterName = "@Name";
updateName.SourceColumn = "name";
_ = updateCommand.Parameters.Add(updateName);
// 条件となるidのNpgsqlParameterを作成して追加
NpgsqlParameter updateId = new();
updateId.ParameterName = "@id";
updateId.SourceColumn = "id";
_ = updateCommand.Parameters.Add(updateId);
// NpgsqlDataAdapterのInsertCommandに追加
nda.UpdateCommand = updateCommand;
_ = nda.Update(dt);
//tran.Commit();

UPDATEの条件は同じくidが一致している事としています。

NpgsqlDataAdapterのみ	トランザクション有
18.691秒	9.835秒

思いの外良いスコアとなりクエリ文のみやパラメータを使った時より短時間となりました。

但し、大きな差とはなりませんでしたので互角と考えて良いと思います。

そう思うとDataTableを使うのも意外とアリなのかもしれません。

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

NpgsqlDataAdapterのみ	トランザクション有
13.248秒	5.199秒

これも相変わらず処理速度アップに貢献してくれます。

NpgsqlDataAdapterとNpgsqlCommandBuilder

UPDATEのクエリ文はNpgsqlCommandBuilderで自動生成してみます。

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 * FROM data;", con);
_ = nda.Fill(dt);
for (int i = 0; i < 100000; i++)
{
	dt.Rows[i][2] = $"{i}{dt.Rows[i][2]}";
}
//using NpgsqlTransaction tran = con.BeginTransaction();
using NpgsqlCommandBuilder cb = new(nda);
_ = nda.Update(dt);
//tran.Commit();

生成されたUPDATEのクエリ文は下記となります。

UPDATE \"db_PostgreTest\".\"public\".\"data\" SET \"time\" = @p1, \"name\" = @p2, \"numeric\" = @p3 WHERE ((\"id\" = @p4) AND ((@p5 = 1 AND \"time\" IS NULL) OR (\"time\" = @p6)) AND ((@p7 = 1 AND \"name\" IS NULL) OR (\"name\" = @p8)) AND ((@p9 = 1 AND \"numeric\" IS NULL) OR (\"numeric\" = @p10)))

生成されるクエリ文は取得したカラムの全ての一致が条件となりますのでパラメータの数が多くなります。

その分だけ処理が多くなるのが原因なのか、今回の実験では一番遅いスコアとなりました。

NpgsqlDataAdapterとNpgsqlCommandBuilder	トランザクション有
24.268秒	14.826秒

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

NpgsqlDataAdapterとNpgsqlCommandBuilder	トランザクション有
17.056秒	7.693秒

時間は遅いですが一番安全なUPDATEなので不特定多数がUPDATEするような状況で威力を発揮します。

計測データのまとめ

表示の制約があるのでトランザクション有無で分けデータを縦にまとめてみます。

トランザクションの有無→	無	有
普通のUPDATE	19.605秒	10.189秒
パラメータを使用したUPDATE	22.874秒	11.827秒
Max Auto Prepare	13.709秒	4.905秒
Prepareメソッド	11.953秒	4.605秒
NpgsqlDataAdapter	18.691秒	9.835秒
NpgsqlDataAdapterとMax Auto Prepare	13.248秒	5.199秒
NpgsqlCommandBuilder	24.268秒	14.826秒
NpgsqlCommandBuilderとMax Auto Prepare	17.056秒	7.693秒

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

通常のUPDATE

パラメータを使う

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

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

NpgsqlDataAdapterとクエリ文

NpgsqlDataAdapterとNpgsqlCommandBuilder

計測データのまとめ

0 件のコメント:

コメントを投稿

自己紹介

カテゴリ

このブログを検索