ホーム / Npgsql /

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

2021/10/27

C# Npgsql

アイキャッチ

このシリーズ最後はDELETEです。

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

尚、計測時間はDELETEにかかった時間のみでNpgsqlDataAdapterの場合、事前に取得したりDataTableを編集した時間は含まれません。

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

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

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

Npgsqlの本家情報は

NpgsqlのPrepareメソッドについて

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

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

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

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

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

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

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

通常のDELETE

まずは削除する条件としてidを指定して削除を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 NpgsqlCommand cmd = new();
cmd.Connection = con;
//using NpgsqlTransaction tran = con.BeginTransaction();
// 指定したidのデータを削除
for (int i = 0; i < 100000; i++)
{
    cmd.CommandText = $"DELETE FROM data WHERE id = {i + 1};";
    _ = cmd.ExecuteNonQuery();
}
//tran.Commit();

早いのか遅いのかの判断はできませんが、これを基準として他の手法と比べてみます。

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

普通のDELETE トランザクション有
16.632秒 8.234秒

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

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

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

パラメータを使う

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

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("DELETE FROM data WHERE id = @id;", con);
_ = cmd.Parameters.Add(new NpgsqlParameter("id", DbType.Int32));
// 指定したidのデータを削除
for (int i = 0; i < 100000; i++)
{
    cmd.Parameters["id"].Value = i + 1;
    _ = cmd.ExecuteNonQuery();
}
//tran.Commit();

やっている事は通常のクエリ文実行と変わりません。

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

こちらの方がクエリ文生成処理などで遅くなると予想していましたがわずかに早かったです。

とは言えごくわずかなので実行したタイミングで逆転する可能性もあります。

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

ここからは処理速度アップの恩恵を受ける手法となります。

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("DELETE FROM data WHERE id = @id;", con);
_ = cmd.Parameters.Add(new NpgsqlParameter("id", DbType.Int32));
System.Diagnostics.Stopwatch sw = new();
sw.Start();
// 指定したidのデータを削除
for (int i = 0; i < 100000; i++)
{
    cmd.Parameters["id"].Value = i + 1;
    _ = cmd.ExecuteNonQuery();
}
//tran.Commit();

こんな手法で削除するか？と言う疑問はさておき、今まで各種処理を行ってきた時と同じような効果がありました。

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

Max Auto Prepareのみ トランザクション有
11.446秒 3.733秒

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

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

パラメータを使い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("DELETE FROM data WHERE id = @id;", con);
_ = cmd.Parameters.Add(new NpgsqlParameter("id", DbType.Int32));
cmd.Prepare();
System.Diagnostics.Stopwatch sw = new();
sw.Start();
// 指定したidのデータを削除
for (int i = 0; i < 100000; i++)
{
    cmd.Parameters["id"].Value = i + 1;
    _ = cmd.ExecuteNonQuery();
}
//tran.Commit();

「Max Auto Prepare」を設定した実験よりわずかに遅かったです。

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

平均では負けましたが最速スコア同士だと勝ってますので、互角と言っていいと思います。

NpgsqlDataAdapterとクエリ文

前回もそうですが、普通にクエリ文を実行した時と劇的な差とまでは言えない程度だと思います。

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);
// Deleteメソッドは削除対象行をマークするだけで、これをデータベースに反映させる
for (int i = 0; i < 100000; i++)
{
    dt.Rows[i].Delete();
}
//using NpgsqlTransaction tran = con.BeginTransaction();
// データベースにUPDATEするNpgsqlCommandを追加
using NpgsqlCommand deleteCommand = new();
deleteCommand.Connection = con;
deleteCommand.CommandText = "DELETE FROM data WHERE id = @id;";
// 条件となるidのNpgsqlParameterを作成して追加
NpgsqlParameter deleteId = new();
deleteId.ParameterName = "@id";
deleteId.SourceColumn = "id";
_ = deleteCommand.Parameters.Add(deleteId);
// NpgsqlDataAdapterのInsertCommandに追加
nda.DeleteCommand = deleteCommand;
_ = nda.Update(dt);
//tran.Commit();

実験ではDataTableのDeleteメソッドの時間は加えていません。

NpgsqlDataAdapterのみ トランザクション有
18.265秒 8.791秒

実は今回初めてDataTableで削除を行ってみたのですが、最初はRemoveメソッドを使ってしまいデータベースにあるデータが削除されずハマってしまいました。

そしてDeleteメソッドで削除できたのを確認しましたが、Deleteメソッドの解説を見ると普通のクエリ文よりは遅くなるかな？と予想していましたがおおよそ合ってました。

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

NpgsqlDataAdapterのみ トランザクション有
12.801秒 4.562秒

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

NpgsqlDataAdapterとNpgsqlCommandBuilder

DELETEのクエリ文は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);
// Deleteメソッドは削除対象行をマークするだけで、これをデータベースに反映させる
for (int i = 0; i < 100000; i++)
{
    dt.Rows[i].Delete();
}
System.Diagnostics.Stopwatch sw = new();
sw.Start();
//using NpgsqlTransaction tran = con.BeginTransaction();
using NpgsqlCommandBuilder cb = new(nda);
_ = nda.Update(dt);
//tran.Commit();

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

DELETE FROM \"db_PostgreTest\".\"public\".\"data\" WHERE ((\"id\" = @p1) AND ((@p2 = 1 AND \"time\" IS NULL) OR (\"time\" = @p3)) AND ((@p4 = 1 AND \"name\" IS NULL) OR (\"name\" = @p5)) AND ((@p6 = 1 AND \"numeric\" IS NULL) OR (\"numeric\" = @p7)))

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

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

NpgsqlDataAdapterとNpgsqlCommandBuilder トランザクション有
22.83秒 14.569秒

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

NpgsqlDataAdapterとNpgsqlCommandBuilder トランザクション有
14.687秒 6.228秒

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

計測データのまとめ

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

トランザクションの有無→ 無 有
普通のDELETE 16.632秒 8.234秒
パラメータを使用したDELETE 15.762秒 7.853秒
Max Auto Prepare 11.446秒 3.733秒
Prepareメソッド 11.522秒 3.744秒
NpgsqlDataAdapter 18.268秒 8.791秒
NpgsqlDataAdapterとMax Auto Prepare 12.801秒 4.562秒
NpgsqlCommandBuilder 22.83秒 14.569秒
NpgsqlCommandBuilderとMax Auto Prepare 14.687秒 6.228秒

自己紹介

自分の写真



新潟県のとある企業で働いてます。
【できる事】
電子回路設計
基板パターン設計
マイコンプログラム
C#(WinForms WPF)を使ったWindowsアプリケーション作成
PLCラダー
自動化装置アドバイザー
にほんブログ村 IT技術ブログ ソフトウェアへ

カテゴリ

このブログを検索

QooQ