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

あんまり実用性はないかもしれませんが、あくまで実験としてSELECTの検証も行ってみます。

今回も「Max Auto Prepare」の設定やPrepareメソッドを使用する事でどれだけ高速になるかを検証してみます。

• もくじ
  • WHERE条件ありのSELECT
  • パラメータを使う
  • パラメータを使い接続文字列に「Max Auto Prepare」を設定
  • パラメータを使いPrepareメソッドを使用
  • 条件のないSELECT
  • NpgsqlDataAdapter
  • 計測データのまとめ

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

Npgsqlの本家情報は

NpgsqlのPrepareメソッドについて

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

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

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

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

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

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

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

WHERE条件ありのSELECT

WHEREでidの値を条件として取得してみます。

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();
cmd.Connection = con;
for (int i = 1; i < 100001; i++)
{
    cmd.CommandText = $"SELECT * FROM data WHERE id = {i};";
    using NpgsqlDataReader rd = cmd.ExecuteReader();
    rd.Read();
    selectData.Add(new(rd.GetInt32("id"), rd.GetDateTime("time"), rd.GetString("name"), rd.GetInt32("numeric")));
}

全く実用性のない手法ですが、これが基準となります。

WHERE条件ありのSELECT
8.598秒

これだけでは何の感想もありませんので次の検証を行ってみます。

パラメータを使う

WHEREで条件指定しているidの値をパラメータとしてみます。

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 WHERE id = @id;", con);
_ = cmd.Parameters.Add(new NpgsqlParameter("id", DbType.Int32));
for (int i = 1; i < 100001; i++)
{
    cmd.Parameters["id"].Value = i;
    using NpgsqlDataReader rd = cmd.ExecuteReader();
    rd.Read();
    selectData.Add(new(rd.GetInt32("id"), rd.GetDateTime("time"), rd.GetString("name"), rd.GetInt32("numeric")));
}

手法はパラメータを使った基礎的な手段です。

パラメータを使う
8.453秒

パラメータを取得してSELECT文を生成するので理論的にはこちらの方が遅くなるのではと思ってましたが平均はごくわずかですが早かったです。

しかし最速データを見比べるとWHERE条件ありのSELECTの方が今回の実験で最も早い時間より早いスコアが2回ありました。

数値を見ると「大差なし」と見るのが妥当かと思います。

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

ここからはINSERTでも効果を発揮した手法です。

接続文字列に「Max Auto Prepare」を加える事で、連続して同じクエリ文の時はPostgreSQL側で既に構文解析した情報を用いて処理を行い無駄な処理をスキップします。

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 WHERE id = @id;", con);
_ = cmd.Parameters.Add(new NpgsqlParameter("id", DbType.Int32));
for (int i = 1; i < 100001; i++)
{
    cmd.Parameters["id"].Value = i;
    using NpgsqlDataReader rd = cmd.ExecuteReader();
    rd.Read();
    selectData.Add(new(rd.GetInt32("id"), rd.GetDateTime("time"), rd.GetString("name"), rd.GetInt32("numeric")));
}

接続文字列に「Max Auto Prepare=1」が加わりました。

Max Auto Prepareを設定
3.963秒

時間は半分以下となり、ここでも「Max Auto Prepare」の効果がある事が証明できました。

パラメータを使い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 WHERE id = @id;", con);
_ = cmd.Parameters.Add(new NpgsqlParameter("id", DbType.Int32));
cmd.Prepare();
for (int i = 1; i < 100001; i++)
{
    cmd.Parameters["id"].Value = i;
    using NpgsqlDataReader rd = cmd.ExecuteReader();
    rd.Read();
    selectData.Add(new(rd.GetInt32("id"), rd.GetDateTime("time"), rd.GetString("name"), rd.GetInt32("numeric")));
}

こちらも例にもれず効果を発揮しています。

Prepareメソッドを使用
4.052秒

「Max Auto Prepare」の実験より微妙にスコアは負けてますが、これは誤差範囲とって良いレベルかと思います。

「Max Auto Prepare」が設定された回数を超えた時に初めて構文解析を行うのに比べメソッドを実行したタイミングで明確に構文解析を行うので全体の通信量も減ると思います。

また実行するクエリ文を変えるような状況でも都度メソッドを実行する事で効率は良くなると思います。

条件のないSELECT

今までの実験とはSELECTする内容（条件）が全く違いますので比較データとはなりませんが参考までに。

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")));
}

当たり前ですが今までの効率の良い手法と比べても約100倍早いです。

条件のないSELECT
0.043秒

この手法でデータを取得してLINQで処理するのが効率いいかもしれませんね。

NpgsqlDataAdapter

条件のないSELECTと比較するなら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");
con.Open();
using NpgsqlDataAdapter nda = new("SELECT * FROM data;", con);
_ = nda.Fill(dt);

記述も短いので可読性は一番良いですね。

NpgsqlDataAdapter
0.046秒

条件のないSELECTでListにAddするのとほとんど変わらないスコアとなりました。

但し、以降の処理で差が出る可能性がありますし、使い勝手も全然違うので得意としている手法を使うのがいいかもしれません。

計測データのまとめ

全ての計測データをまとめました。

WHERE条件ありのSELECT	8.598秒
パラメータを使う	8.453秒
Max Auto Prepare	3.963秒
Prepareメソッド	4.052秒
条件のないSELECT	0.043秒
NpgsqlDataAdapter	0.043秒

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

今回の最速スコアより0.004秒とわずかですが高速化できます。