C#でNpgsqlを使ってPostgreSQLへアクセス【Prepare】

NpgsqlのPrepareメソッドに関して検索しても...出てこないw

と言う事で色々調べならが実際効果があるのか考察してみたいと思います。

• もくじ
  • はじめに
  • ベンチマークテスト
  • 普通にSELECT文をループする
  • WHEREの条件をパラメータとして与えてSELECT
  • NpgsqlCommandのPrepareメソッドを実行(プリペアドステートメント)
  • 接続文字列に「Max Auto Prepare」を設定

はしめに

クエリ文に存在しているPREPAREとは目的が同じようですが手法や実際にやっている事は違います。

クエリ文にあるPREPAREはSELECTなどのクエリ文を名前を付けて登録し、WHEREの条件などの変化する部分をパラメータ化します。

そして登録したクエリ文の名称とパラメータを投げて登録されているクエリ文をPostgreSQLが実行するので毎回クエリ文を与える必要がなく、PostgreSQL側も毎回クエリ文構文解析を行わずに済み、通常より高速に動作可能になります。

とは言っても、単発クエリだと逆に登録処理などが増えるので同じクエリ文でWHEREの条件だけが変化するなどの繰り返し動作で威力を発揮します。

NpgsqlのPrepareメソッドも同じような振る舞いとなりますが、登録したクエリを呼び出して実行とは少し勝手が違います。

また、接続文字列に「Max Auto Prepare」を加える事で同様な機能も使えますので併せて解説します。

Npgsqlの本家情報は

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

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

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

ベンチマークテスト

上記データテーブルのカラム名「numeric」が0～999の全部で1000個のデータを用意しておきます。

SELECT文はWHEREに「numeric」の数値が一致条件として問い合わせします。

SELECT name FROM data WHERE numeric = xxx;

つまり上記クエリ文の連続となります。

実験はSELECTを1000回実行したのにかかる時間を計測します。

それを1000回実施した平均時間。

さらにばらつきを考慮してプログラムを5回実行してみます。

これだけやればある程度正確な時間になると思います。

尚、実験は自分のPCにインストールしたPostgreSQLで行っていますのでネットワークのトラフィックに左右されてはいません。

実験内容は

• 実験その1：普通にSELECT文をループする。
• 実験その2：WHEREの条件をパラメータとして与えてSELECT
• 実験その3：NpgsqlCommandのPrepareメソッドを実行
• 実験その4：接続文字列に「Max Auto Prepare」を設定

そして結果は以下となります。

回数	実験その1	実験その2	実験その3	実験その4
1回目	0.155秒	0.161秒	0.129秒	0.138秒
2回目	0.160秒	0.166秒	0.135秒	0.129秒
3回目	0.160秒	0.166秒	0.126秒	0.138秒
4回目	0.165秒	0.166秒	0.137秒	0.128秒
5回目	0.177秒	0.178秒	0.127秒	0.120秒
平均	0.163秒	0.17秒	0.131秒	0.131秒

おおよそ30～40ミリ秒ほど早くなっています。

これだけの回数でこの速度差は微妙かもしれませんが、外部のサーバに対してだと毎回クエリ文の送信とパラメータだけの送信では通信量に差がでてもっと効果があるかもしれません。

普通にSELECT文をループする

ごく普通に単発なSELECTならこれが一般的だと思います。

for (int i = 0; i < 1000; i++)
{
    for (int j = 0; j < 1000; j++)
    {
        cmd.CommandText = $"SELECT name FROM data WHERE numeric = {j};";
        using NpgsqlDataReader rd = cmd.ExecuteReader();
        rd.Read();
        string result = rd.GetString("name");
    }
}

時間計測のプログラムは可読性重視で省略しています。

ループ変数をWHEREの条件としてSELECTを行います。

WHEREの条件をパラメータとして与えてSELECT

SELECTの解説で紹介した手法です。

WHEREの条件をパラメータとして、パラメータにループ変数を入れて条件とします。

cmd.CommandText = "SELECT name FROM data WHERE numeric = @n;";
_ = cmd.Parameters.Add(new NpgsqlParameter("n", NpgsqlDbType.Integer));
for (int i = 0; i < 1000; i++)
{
    for (int j = 0; j < 1000; j++)
    {
        cmd.Parameters["n"].Value = j;
        using NpgsqlDataReader rd = cmd.ExecuteReader();
        rd.Read();
        string result = rd.GetString("name");
    }
}

この程度のクエリ文では可読性が上がる訳でもないですが1つの実験としてやってみました。

結果は普通にクエリ文をループさせるのと同じでした。

NpgsqlCommandのPrepareメソッドを実行(プリペアドステートメント)

この手法は事前にプリペアドステートメントを実行するクエリ文を明確にする事になります。

cmd.CommandText = "SELECT name FROM data WHERE numeric = @n;";
_ = cmd.Parameters.Add(new NpgsqlParameter("n", NpgsqlDbType.Integer));
cmd.Prepare();
for (int i = 0; i < 1000; i++)
{
    for (int j = 0; j < 1000; j++)
    {
        cmd.Parameters["n"].Value = j;
        using NpgsqlDataReader rd = cmd.ExecuteReader();
        rd.Read();
        string result = rd.GetString("name");
    }
}

3行目の「cmd.Prepare();」以外は、その前の例と全く同じです。

1行目のCommandTextにセットした文字列がプリペアドステートメントの対象となるクエリ文となります。

またcmd.Prepare();実行前にパラメータの追加も忘れずに行いましょう。

事前にクエリ文だけは送信しておき、後でパラメータだけを送信する文構文解析済みな状態なので処理が早くなります。

毎回クエリ文を送信しなきゃ通信量も少なくなるよね？と素朴な疑問がありましたので計測してみました。

但しネットワークに不要な負荷をかけるのはよろしくないので、現在運用中のアプリケーションを改良して比較しました。

送信するクエリ文や同じクエリ文を何回送信するかによって結果は全く変わりますがSELECT COUNT(*)を72回実行するプログラムでは改修前の約18kbyteから改修後には約8kbyteと通信量は約44%になりました。

何度も試してみましたが結果はほとんど同じ値なので間違いないと思います。

このようにPrepareメソッドはネットワークトラフィックの改善にも効果があるのが分かります。

接続文字列に「Max Auto Prepare」を設定

接続文字列に「Max Auto Prepare=1」と記述する事でどのクエリの構文解析を行うかをPostgreSQL側に任せる手法です。

デフォルトは0（使用しない）になっているので明確化する必要があります。

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");
// 省略
cmd.CommandText = "SELECT name FROM data WHERE numeric = @n;";
_ = cmd.Parameters.Add(new NpgsqlParameter("n", NpgsqlDbType.Integer));
for (int i = 0; i < 1000; i++)
{
    for (int j = 0; j < 1000; j++)
    {
        cmd.Parameters["n"].Value = j;
        using NpgsqlDataReader rd = cmd.ExecuteReader();
        rd.Read();
        string result = rd.GetString("name");
    }
}

またAuto Prepare Min Usagesも影響します。

Auto Prepare Min Usagesで指定された回数のクエリ文実行を超えた時にPostgreSQL側が初めて文構文解析対象のクエリ文として準備します。

デフォルトでは５となっており６回目からPostgreSQL側が自動構文解析対象のクエリ文となります。

データをグラフ化したい時などは細かく条件を変えてデータを取得したいケースは多いので活用できそうです。