外部APIやCSVから取得したキーの一覧を使って、データベースから該当レコードを取得したいことがある。単一列のキーならContainsで書けるが、複合キーになると少し厄介である。
たとえば、DB側のDbRecordとメモリ上のCompositeKeyをKey1、Key2で結合するものとする。
素直に書けば、次のようなJoinになる。
しかし、db.RecordsはSQLへ変換されるIQueryableであるのに対し、keysはメモリ上のオブジェクト集合である。この両者を複合キーで結合するJoinはSQLへ変換できず、実行時に例外となる。
先にAsEnumerableを呼べばLINQ to Objectsとして結合できるが、そのためには照合前のDBレコードをクライアントへ読み込まなければならない。それを避けるには、キー集合をSQLへ渡せる形式に変換し、照合をDB側で実行できるようにする必要がある。
この記事では、そのために実装した次の2方式を比較する。
- キー集合を
WHERE条件へ展開するCompositeKeyPredicateBuilder - キー集合をJSONで渡し、SQL Serverの
OPENJSONで行セットに展開するOpenJsonQueryBuilder
この記事ではコードの要点だけを抜粋している。完全な実装とベンチマーク一式はGitHubリポジトリで公開している。
式木でWHERE条件へ展開する
1つ目は、各キー内の要素をANDで、キーごとの条件をORでつないだ述語を生成する方法である。2件のキーを渡した場合、次のような条件を生成する。
この形の式なら、EF Coreは通常のWhere条件としてSQLへ変換できる。呼び出し側は、エンティティ側と入力キー側の対応するプロパティを指定し、生成された述語を渡すだけである。
ビルダーの中心部分では、キー1件ごとの比較式を作り、それらを1つのExpression<Func<TEntity, bool>>へまとめている。
キー集合が空の場合は常にfalseとなる述語を生成するため、呼び出し側に特別な分岐を設ける必要はない。また、プロパティ数の不一致、存在しないプロパティ名、型の不一致といった設定誤りは、ビルダーの構築時に例外として検出する。
この方式はSQL Server固有の構文を使わない。既存のLINQへ組み込みやすい一方、キー数に比例して式とSQLが大きくなり、式の生成、SQLへの変換、メモリ割り当てのコストも増える。
キー集合をJSONで渡し、OPENJSONで展開する
2つ目は、キー集合をJSON文字列にして1つのSQLパラメータで渡す方法である。SQL Server側ではOPENJSONによってJSONを行セットに展開し、元のテーブルとJOINする。
生成されるSQLは次のようになる。
呼び出し方は式木方式とほぼ同じである。
Buildはキー集合をシリアライズし、生成したSQLをFromSqlRawへ渡す。
OPENJSONのWITH句には、EF Coreのモデルから取得した実際のテーブル名、列名、SQL型を使う。対応するプロパティがマッピングされていない、入力キーにプロパティがない、CLR型が一致しない、といった設定誤りはSQLを実行する前にビルダーが検出する。
OPENJSONのWITH句には、EF Coreのモデルから取得した実際のテーブル名、列名、SQL型を使う。エンティティ側のプロパティが列にマッピングされていない、入力キー側にプロパティがない、CLR型が一致しない、といった設定誤りはSQLを実行する前にビルダーが検出する。
CreateSqlのSQL組み立て部分では、検証済みのプロパティからSELECT列、OPENJSONの入力列、JOIN条件をそれぞれ生成し、1つのSQL文に埋め込む。
SQLの形状はキー数にかかわらず一定であり、渡すパラメータもJSON文字列の1つだけである。ただし、SQL Server固有のOPENJSONと生SQLに依存するため、他のDBプロバイダーへそのまま移植はできない。
ベンチマーク
2方式を次の環境で測定した。
- .NET 10 / EF Core 10.0.0
- SQL Server 2022
- BenchmarkDotNet 0.15.6
- 複合キーは
intとvarchar(50) - キー数は100、1,000、10,000
各ベンチマークでは、指定件数のキーと一致するレコードをDBに用意し、クエリを実行して取得件数を返している。つまり、述語やSQLの生成だけでなく、SQL Serverでの照合と結果の読み込みまでが測定対象である。
結果は次のとおりである。
| キー数 | 式木 | OPENJSON | 式木のメモリ割当 | OPENJSONのメモリ割当 |
|---|---|---|---|---|
| 100 | 1.953 ms | 1.520 ms | 116.2 KB | 57.43 KB |
| 1,000 | 6.018 ms | 4.074 ms | 1,086.5 KB | 431.79 KB |
| 10,000 | 80.096 ms | 49.037 ms | 11,006.13 KB | 4,310.55 KB |
100件では差は0.433 msだが、10,000件では31.059 msまで広がった。OPENJSONの実行時間は式木方式の約61%、メモリ割り当て量は約39%である。キー数が増えるほど、条件式を展開しないことの効果が表れている。
もちろん、この数値は今回使用したデータ型、取得件数、SQL Serverの設定、および実行環境に基づく結果である。実際の性能はテーブルの大きさ、インデックス、ネットワーク、取得する列数や行数にも左右される。
どちらを使うか
判断基準は、キー数と、DB固有の実装を許容できるかどうかである。
| 条件 | 選びやすい方式 |
|---|---|
| SQL Server以外でも使いたい | 式木 |
| 通常のLINQとして組み立てたい | 式木 |
| キー集合が小さい | 式木 |
| SQL Serverに固定できる | OPENJSON |
| 大きなキー集合を頻繁に照合する | OPENJSON |
| SQLと割り当ての増加を抑えたい | OPENJSON |
小さなキー集合なら、式木方式は単純で扱いやすく、DB固有のコードも不要である。キー集合が大きい場合、利用するDBをSQL Serverに限定できるなら、SQLの形状を一定に保てるOPENJSON方式が有力である。
実行方法
リポジトリのDocker Compose構成を使えば、SQL Serverを起動できる。
接続文字列を設定し、Release構成でベンチマークを実行する。
まとめ
インメモリの複合キー集合をIQueryableへ直接Joinすると、EF CoreはそのクエリをSQLへ変換できない。DB側で照合するには、キー集合をSQLへ渡せる形式に変換する必要がある。
式木方式は、キーをANDとORからなるWhere条件へ変換する。LINQとして扱いやすくDB固有の機能を避けられるが、キー数とともに式とSQLが大きくなる。
OPENJSON方式は、キー集合を1つのJSONパラメータで送り、SQL Server上で行セットに展開する。SQL Serverに依存するが、今回の測定ではキー数が多いほど実行時間とメモリ割り当ての両方で有利であった。