準備済みのステートメント

Introduction

あなたに、このブログの記事を読むことから始めることをお勧めします。

ほとんどのアプリケーションは、異なるパラメータを渡たす、同じSQL命令を何度も繰り返します。このような場合、コマンドを準備することは非常に有益です。－これは、それらを解析して計画する、コマンドのステートメント（複数可）をPostgreSQLに送信します。準備済みのステートメントは、その次に、実行時に使用することができ、貴重な計画時間を節約できます。あなたは、より複雑なクエリのより多くのパフォーマンスの向上に気づくでしょう。；しかし、さらに、とても単純なクエリでも、準備から利益を得る傾向があります。

以下は、準備された、そして、準備されていなものが、実行される、同じクエリの実行時間を測定する、Npgsql.Benchmarks.Prepareのベンチマークです。TablesToJoinは、クエリの複雑さを増加するパラメータです。－それは、クエリが、どれだけ多くのテーブルから、結合するかについて判断します。

Simple Preparation

コマンドを準備するには、単純に、次の標準のADO.NETコードを使用します。：

var cmd = new NpgsqlCommand(...);
cmd.Parameters.Add("param", NpgsqlDbType.Integer);
cmd.Prepare();
// Set parameters
cmd.ExecuteNonQuery();
// And so on

すべてのパラメータは、Prepare()を呼び出す前に、設定されている必要があることに注意してください。－それらは、PostgreSQLに送られる情報の一部です。そして、ステートメントを効果的に計画するために使用されます。あなたは、あなたのパラメータに、データ型を明確に指定する関するDbTypeやNpgsqlDbTypeを設定する必要もあります。（値の設定は、サポートされていません）。

var cmd = new NpgsqlCommand("UPDATE foo SET bar=@bar WHERE baz=@baz; UPDATE foo SET bar=@bar WHERE baz=@baz");
// set parameters.
cmd.Prepare();

準備は、それらを一緒にバッチ処理するマルチ・ステートメントを含めることができるコマンド上ではなく、個々のステートメントで、行われることに注意してください。これは、次のような場合では、重要なことがあります。：

このコマンドには、2つのステートメントがありますが、SQLが、同一であるため、実行するために、同じ準備済みステートメントが使用されます。

Persistency

3.2以前では、それらが所有するコマンドが配置されたとき、準備済みのステートメントは、閉じられていました。特に、プールされた接続を閉じると、自動的にすべての準備済みステートメントが閉じられるため、これは、それらの有用性を著しく減少させました。ほとんどのWebアプリケーションのような、接続の寿命が短いアプリケーションでは、事実上、役に立たない準備済みのステートメントを作成しました。

3.2から始まる、すべての準備済みのステートメントは、持続的です。－コマンドや接続が、閉じられたとき、もはや、閉じられません。Npgsqlは、それぞれの物理コネクションで、準備されたステートメントを追跡し続けます。;あなたが、同じ接続に、二度目に同じSQLを準備する場合、Npgsqlは、単純に、最初の準備から準備済みのステートメントを再利用します。これは、以下のことを示しています。寿命が短いアプリケーションでは、プールされた接続、準備済みのステートメントは、アプリケーションがウォームアップし、そして、最初に、接続が使用されると、徐々に作成されます。続いて、新しいプール接続を開くと、既に、あなたのSQLのための準備済みのステートメントを持っている物理接続が返されます。極めて大幅なパフォーマンスの向上を実現します。例えば、

using (var conn = new NpgsqlConnection(...)
using (var cmd = new NpgsqlCommand("<some_sql>", conn) {
    conn.Open();
    conn.Prepare();   // First time on this physical connection, Npgsql prepares with PostgreSQL
                                        // 最初のこの物理的な接続で、Npgsqlは、PostgreSQLを準備します。
    conn.ExecuteNonQuery();
}

using (var conn = new NpgsqlConnection(...)
using (var cmd = new NpgsqlCommand("<some_sql>", conn) {
    conn.Open();      // We assume the pool returned the same physical connection used above
                                        // 私たちは、プールが、上で使用した同じ物理接続を返すと想定します。
    conn.Prepare();   // The connection already has a prepared statement for <some_sql>, this doesn't need to do anything
                                        // 接続は、既に、<some_sql>の準備済みステートメントがあるため、何もする必要はありません。
    conn.ExecuteNonQuery();
}

NpgsqlCommand.Unprepare()を呼び出すことによって、あなたは、依然として、準備済みのステートメントを閉じることを選択できます。NpgsqlConnection.UnprepareAll()を呼び出すことによって、あなたは、特定の接続上の、すべてのステートメントの準備を解除することもできます。

Automatic Preparation

上に示した準備の例は、非常に大きなパフォーマンス向上をもたらしますが、それらは、あなたが、Prepare()コマンドを呼び出すことに依存ます。残念ながら、あなたが、ADO.NETより上のいくつかのデータ・レイヤーを使用している場合、DapperやEntity Frameworkのような、可能性は、これらのレイヤーが、あなたのために準備することです。DapperとEntity Framework Coreの両方に問題がありますが、それらは、現在、準備済みのステートメントを利用していません。

Npgsql 3.2では、自動準備が導入されています。オンにすると、これにより、Npgsqlは、あなたが実行したステートメントを追跡し、あなたが、特定の閾値に達したとき、それらを自動的に準備します。あなたが、その閾値に達するとき、ステートメントは、自動的に準備されます。そして、それ以降は、準備されたとおりに実行されます。上記で説明した、すべてのパフォーマンス上の利点をもたらします。この機能をオンにするために、あなたは、単純に、どの時点で、自動的に、接続上に、どれだけのステートメントを準備できるかを決定する、Max Auto Prepare接続文字列パラメータを設定する必要があります。（このパラメータのデフォルトは0で、機能は無効になっています）。2つ目のパラメータ、Auto Prepare Min Usagesは、ステートメントが、自動準備されるまでに何回実行する必要があるかを決定します（デフォルトは5）。コードを変更する必要がないため、あなたは、単純に、Max Auto Prepareを設定し、あなたのアプリケーションを実行し、直接の速度増加を確認することができます。明示的に準備されたステートメントのような、自動的に準備済みのステートメントは、持続的で、あなたは、一時的な接続をするアプリケーションで、パフォーマンスの利点を獲得できることにも注意してください。

あなたが、NpgsqlやADO.NETに対して、直接、コーディングしている場合、Npgsqlに自動的に準備させるよりも、Prepare()を使用して、コマンドを明示的に準備することをお勧めします。内部LRUキャッシュとさまざまな管理作業データ構造のため、自動準備は、明示的な準備と比較して、わずかに実行コストがかかります。あなたは、明示的に準備することで、どのステートメントが準備するか、準備しないか、正確に制御することもできます。そして、あなたのステートメントが、常に、準備済みの状態を保つことを確実にします。そして、LRUメカニズムのため、決して、追い出されることはありません。

自動準備は、やや実験的なものと考えるべき、複雑な新しい機能であることに注意してください;慎重にテストし、あなたが、変な動作や問題を見つけたら、無効にしてみてください。