Go to Qiita Advent Calendar 2024 Top
LoginSignup
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@h_tacayama

【C#】型安全性とパフォーマンスで選ぶSQLビルダ

Last updated at Posted at 2024-12-15

はじめに

C#でデータベースにアクセスするアプリケーションを開発する際、SQLの発行方法として次のようなアプローチが考えられます。

  • ① 文字列で生のSQLを書き、ADO.NETなどを用いてSQLの発行を行う。SQLの記述に制約を受けることがなく、特定のライブラリに依存することも避ける。
  • ② SqlKataなどのSQLビルダとDapperなどの軽量ORMを組み合わせる。効率良くSQLを記述しつつ、ORMのメリットも享受する。
  • ③ Entity Framework Coreを使用する。SQL文を記述することなく、データモデルを中心に開発を進める。

現代の開発では、①の選択肢を取ることは少ないと思います。SQLに詳しくない場合や、将来的に複雑なSQLを必要としないことが明白な場合には、③の選択肢は効果的です。しかし、いざ複雑なSQLが必要だとなった場合には、課題に直面するという事例1も耳にします。そこで、SQLはある程度理解している、パフォーマンスを考慮したSQLを自分で書きたい、といった場合には②が有力な選択肢になります。

C#のSQLビルダとしては、Dapper.SqlBuilderやSqlKataが有名どころではないでしょうか。これらはコードベースでSQLを構築できますが、テーブル名、カラム名、一部のシンタックスなどは文字列で指定します。なるべく文字列で指定する部分が少なく、SQL構文に従い型安全で、SQLキーワードにIntelliSenseが効くライブラリがないか調査を行いました。本記事では、5つのライブラリを比較・検証した結果をまとめます。なお、本記事の情報は2024年12月10日時点のものとなります。

目次

比較の観点

本記事では、下記の観点でライブラリの比較を行いました。

対応を謳うDBMS

対応するDBMSには何があるか。対応すると謳っていても、そのDBMSで使えるSQL構文をすべて記述できる訳ではない可能性があるため、「相性のよさ」として評価する。

型安全度合い

SQLキーワード、テーブル名、カラム名などが型として表現されているか。できるだけIntelliSenseが効いて、入力補完されるとよい。また、SQLの構文として正しくない記述がコンパイルエラーとなるとよりよい。

SQL構文の豊富さ

SQL構文をどれくらいカバーするか。記述することができないSQL構文はないか。

条件に指定した値の取り扱い

例えば、WHERE Column = 123という記述をしたとき、123はリテラルとして扱われるか、バインド変数として扱われるか。データベースのキャッシュを利用できるようにバインド変数として扱われることが望ましい。明示すればリテラルも記述できるとよい。

速度とメモリ使用量

BenchmarkDotNetを使って比較を行う。内部処理として文字列整形や連結が頻繁に行われていることが予想されるため、Allocatedが増大しないかは検証しておきたいポイント。SQLビルダとしての比較のため、SQL文を構築するまでを対象として、データベースにSQLを発行したり、その応答を待ったりというところは対象としない。

ライブラリごとの実装例と評価

以降、選出したライブラリのサンプルコード、それにより出力されるSQL、私の個人的な評価を記していきます。

まず、基準としてダウンロード数が多いDapper.SqlBuilderとSqlKataを挙げています。その後、NuGet GalleryやGitHubのReadmeを見て目視でよさそうだと判断した3つのライブラリについて検証しています。

Dapper.SqlBuilder

  • Version：2.0.78
  • Last updated：2020/11/18
  • NuGet Downloads Total：14.1M

実装例

var template = new StringBuilder();
template.AppendLine("SELECT a.Id, a.Name, COUNT(*) AS Count");
template.Append("FROM Authors a");
template.Append("/**innerjoin**/");
template.Append("/**where**/");
template.Append("/**groupby**/");
template.Append("/**having**/");
template.Append("/**orderby**/");

var builder = new SqlBuilder()
	.InnerJoin("Books b ON a.Id = b.AuthorId")
	.Where("b.Rating > @p1", new { p1 = 2.5 })
	.GroupBy("a.Id, a.Name")
	.Having("COUNT(*) > @p2", new { p2 = 3 })
	.OrderBy("a.Id DESC");

var query = builder.AddTemplate(template.ToString());
var sql = query.RawSql;
var parameters = query.Parameters;

出力されるSQL

SELECT a.Id, a.Name, COUNT(*) AS Count
FROM Authors a
INNER JOIN Books b ON a.Id = b.AuthorId
WHERE b.Rating > @p1

GROUP BY a.Id, a.Name
HAVING COUNT(*) > @p2
ORDER BY a.Id DESC

評価

  • 対応を謳うDBMS：Dapper.SqlBuilderとしては対応するDBMSの記載を見つけられなかった。自由に記述できる範囲が多いため、Dapperが使える範囲では使えるものと思われる。
  • 型安全度合い：SQLの雛形を用意して、そこに各句を埋め込む形となる。テーブル名やカラム名、また、SQLキーワードの一部は文字列で記述する。自由度は高い一方、型による制約がかかる範囲は少ない。
  • 構文の豊富さ：SqlBuilderで用意されている構文は少ないが、雛形に書いてしまえばよいため、特定の構文を書けないということはなさそう。
  • 条件に指定した値の取り扱い：WHERE句やHAVING句に明示的にバインド変数として指定する。

SqlKata

  • Version：2.4.0
  • Last updated：2022/09/19
  • NuGet Downloads Total：13.3M

実装例

var query = new Query("Authors")
	.Select("Authors.Id", "Authors.Name", "COUNT(*) AS Count")
	.Join("Books", j => j.On("Authors.Id", "Books.AuthorId"))
	.Where("Books.Rating", ">", 2.5)
	.GroupBy("Authors.Id", "Authors.Name")
	.Having("COUNT(*)", ">=", 3)
	.OrderByDesc("Authors.Id");

出力されるSQL

SELECT "Authors"."Id", "Authors"."Name", "COUNT(*)" AS "Count"
FROM "Authors" 
INNER JOIN "Books" ON ("Authors"."Id" = "Books"."AuthorId")
WHERE "Books"."Rating" > @p0
GROUP BY "Authors"."Id", "Authors"."Name"
HAVING "COUNT(*)" >= @p1
ORDER BY "Authors"."Id" DESC

※掲載の都合上、改行を入れています。

評価

  • 対応を謳うDBMS：SQL Server、MySql、PostgreSql、Oracle、SQLite、Firebird。
  • 型安全度合い：テーブルやカラム、関数、比較演算子などは文字列で記述する。文字列のためタイポの可能性はある。
  • 構文の豊富さ：文字列による記述ができるため表現の幅は広い。WITH句の記述も可能。
  • 条件に指定した値の取り扱い：C#で指定したリテラルがバインド変数として扱われる。

Dapper.Qb.Net

  • Version：0.20.5
  • Last updated：2023/03/23
  • NuGet Downloads Total：23.0K

実装例

var a = Table("Authors", "a");
var b = Table("Books", "b");

var query =
	Select("a.Id", "COUNT(*) AS Count")
	.From(a)
	.JoinInner(a, b, Fields("Id", "AuthorId"))
	.Where(Greater("b.Rating", 2.5))
	.GroupBy("a.Id, a.Name")
	.Having(GreaterOrEqual("COUNT(*)", 3))
	.OrderBy("a.Id", OrderByDir.Desc);

var ora = new OracleRenderer();
var sql = ora.RenderSelect(query);

出力されるSQL

select "A.ID", "COUNT(*) AS COUNT"
from "AUTHORS" "A"
inner join "BOOKS" "B" on ("A"."ID" = "B"."AUTHORID")
where ((("B.RATING" > 2.5)))
group by "A.ID, A.NAME"
having (("COUNT(*)" >= 3))
order by "A.ID" desc

※掲載の都合上、改行を入れています。

評価

  • 対応を謳うDBMS：SQL Server、SQLite、Oracle、MySQL、PostgreSQL。
  • 型安全度合い：テーブルやカラムは文字列で指定する。比較演算子はC#関数で表現する。
  • 構文の豊富さ：文字列での記述もできるため、ある程度の構文には対応できるか。
  • 条件に指定した値の取り扱い：C#のリテラルは、SQLでもリテラルとして扱われる。

Sqlify

  • Version：0.3.14
  • Last updated：2023/08/16
  • NuGet Downloads Total：3.2K

実装例

var a = Table<Sqlifys.IAuthors>("a");
var b = Table<Sqlifys.IBooks>("b");

var selectQuery =
	Select(a.Id, a.Name, Count().As("Count"))
	.From(a)
	.Join(b, a.Id == b.AuthorId)
	.Where(b.Rating > 2.5)
	.GroupBy(a.Id)
	.Having(Count() >= 3) // GroupByの前にHavingを書けてしまう
	.GroupBy(a.Name) // GROUP BYに複数カラム指定するにはGroupByを複数回記述
	.OrderByDesc(Count());
	//.OrderByDesc(Count().As("")); ORDER BY COUNT(*) AS Count DESCと出力される

var writer = new SqlWriter();
selectQuery.Format(writer);
var sql = writer.GetCommand();

出力されるSQL

SELECT a.Id, a.Name, COUNT(*) AS Count
FROM Authors a
JOIN Books b ON a.Id = b.AuthorId
WHERE b.Rating > @p1
GROUP BY a.Id, a.Name
HAVING COUNT(*) >= @p2
ORDER BY COUNT(*) DESC

※掲載の都合上、改行を入れています。

評価

  • 対応を謳うDBMS：どのDBMSでもサポートする基本構文＋PostgreSQLの一部の構文。
  • 型安全度合い：テーブルをインタフェースで定義するため、テーブル名やカラム名にもIntelliSenseが効く。関数も対応するC#メソッドで記述する。
  • 構文の豊富さ：基本的な構文はカバーする。WITH句やCASE式には対応していないと思われる。
  • 条件に指定した値の取り扱い：C#で指定したリテラルがバインド変数として扱われる。

SqExpress

  • Version：1.1.1
  • Last updated：2024/09/16
  • NuGet Downloads Total：21.5K

実装例

var a = new Authors("a");
var b = new Books("b");

var query =
	Select(
	a.Id,
	a.Name,
	Count(a.Id).As("Count"),
	Case().When(a.Id == Literal(0)).Then("Dummy").Else(""))
	//Case().When(Count(a.Id) > 10).Then("S").Else("")) 関数の比較演算はできない
	.From(a)
	.InnerJoin(b, a.Id == b.AuthorId)
	.Where(b.Rating > 2.5)
	//.Having Having句は書けない
	.GroupBy(a.Id, a.Name)
	.OrderBy(Desc(a.Id));
	//.OrderBy(Desc(Count(a.UserId))); OrderByに関数を指定できない
	//.OrderBy(Desc(a.UserId.As("Uid"))); OrderByに別名を指定できない

var sql = query.ToSql(PgSqlExporter.Default);

出力されるSQL

SELECT "a"."Id","a"."Name",COUNT("a"."Id") "Count",
CASE WHEN "a"."Id"=0 THEN 'Dummy' ELSE '' END
FROM "dbo"."Authors" "a"
JOIN "dbo"."Books" "b" ON "a"."Id"="b"."AuthorId"
WHERE "b"."Rating">2.5
GROUP BY "a"."Id","a"."Name"
ORDER BY "a"."Id" DESC

※掲載の都合上、改行を入れています。

評価

  • 対応を謳うDBMS：MS T-SQL、PostgreSQL、MySQL。
  • 型安全度合い：テーブルをインタフェースで定義するため、テーブル名やカラム名にもIntelliSenseが効く。当該の句の後ろに記述できる句のみをメソッドチェインでつなげるため、間違ったSQL文を構築しにくい。
  • 構文の豊富さ：スキーマ名の指定が可（出力例のadoが該当）。WITH句やCASE式も表現可能。今回選出したライブラリの中で一番、型安全に幅広い構文を記述可能。ただし、集計関数に対して比較演算を記述できない、HAVING句がない、ORDER BY句に関数を指定できないなど非対応の構文もある。
  • 条件に指定した値の取り扱い：C#のリテラルは、SQLでもリテラルとして扱われる。

速度とメモリ使用量のベンチマーク

環境

  • Core i5-1135G7 2.40GHz
  • メモリ 16GB
  • Windows 11 Pro
  • Visual Studio 2022
  • .NET 8.0

ベンチマーク用ソースコード

以下に、BenchmarkDotNetで走らせたソースコードの全文を示します。ライブラリによって記述できる構文に差があるため、どのライブラリでも同じSQLが出力されるようなソースコードとしました。

ソースコード全文 
using System.Text;
using BenchmarkDotNet.Attributes;
using Dapper;
using SqlBuilderBenchmark.SqExpresses;
using SqExpress;
using SqExpress.SqlExport;
using SqlKata;
using Sqlify.Core;
using Viten.QueryBuilder;
using Viten.QueryBuilder.Renderer;
using static SqExpress.SqQueryBuilder;
using static Sqlify.Sql;

namespace SqlBuilderBenchmark;

[MemoryDiagnoser]
[ShortRunJob]
[MinColumn, MaxColumn]
public class Benchmarking
{
	[Benchmark]
	public void DoDapperSqlBuilder()
	{
		var template = new StringBuilder();
		template.AppendLine("SELECT a.Id, COUNT(*) AS Count");
		template.Append("FROM Authors a");
		template.Append("/**innerjoin**/");
		template.Append("/**where**/");
		template.Append("/**groupby**/");
		template.Append("/**orderby**/");

		var builder = new SqlBuilder()
			.InnerJoin("Books b ON b.AuthorId = a.Id")
			.Where("b.Rating > @p1", new { p1 = 2.5 })
			.GroupBy("a.Id")
			.OrderBy("a.Id DESC");

		var query = builder.AddTemplate(template.ToString());
		var sql = query.RawSql;
		var parameters = query.Parameters;
	}

	[Benchmark]
	public void DoSqlKata()
	{
		var query = new Query()
			.Select("a.Id", "COUNT(*) AS Count")
			.From("Authors a")
			.Join("Books b", j => j.On("a.Id", "b.AuthorId"))
			.Where("b.Rating", ">", 2.5)
			.GroupBy("a.Id")
			.OrderByDesc("a.Id");

		var compiler = new SqlKata.Compilers.PostgresCompiler();
		var sql = compiler.Compile(query);
	}

	[Benchmark]
	public void DoDapperQbNet()
	{
		var a = From.Table("Authors", "a");
		var b = From.Table("Books", "b");

		var query = Qb.Select("a.Id, COUNT(*) AS Count")
			.From(a)
			.JoinInner(a, b, JoinCond.Fields("Id", "AuthorId"))
			.Where(Cond.Greater("b.Rating", 2.5))
			.GroupBy("a.Id")
			.OrderBy("a.Id", OrderByDir.Desc);

		var pg = new PostgreSqlRenderer();
		var sql = pg.RenderSelect(query);
	}

	[Benchmark]
	public void DoSqlify()
	{
		var a = Table<Sqlifys.IAuthors>("a");
		var b = Table<Sqlifys.IBooks>("b");

		var selectQuery = 
			Select(a.Id, Count().As("Count"))
			.From(a)
			.Join(b, a.Id == b.AuthorId)
			.Where(b.Rating > 2.5)
			.GroupBy(a.Id)
			.OrderByDesc(Count());

		var writer = new SqlWriter();
		selectQuery.Format(writer);
		var sql = writer.GetCommand();
	}

	[Benchmark]
	public void DoSqExpress()
	{
		var a = new Authors("a");
		var b = new Books("b");

		var query = Select(
			a.Id,
			Count(a.Id).As("Count"))
			.From(a)
			.InnerJoin(b, a.Id == b.AuthorId)
			.Where(b.Rating > 2.5)
			.GroupBy(a.Id)
			.OrderBy(Desc(a.Id));

		var sql = query.ToSql(PgSqlExporter.Default);
	}
}

ベンチマーク結果

以下の図が、BenchmarkDotNetによる各ライブラリのベンチマーク結果です。

  • Mean: 平均実行時間
  • Allocated: メモリ使用量（ヒープへのアロケーション）

benchmark.png

Sqlifyは、GC friendlyを謳う2だけあって、Allocatedが最小であるとともにMeanも最小でした。SqExpressは、機能が豊富なことが影響しているのか、Sqlifyに比べMeanは2倍近くになっていますが、AllocatedはSqlifyに次ぐ値となっています。SqlKataは、他のライブラリより桁が1つ大きいのが見て取れます。

おわりに

本記事では、主に型安全とパフォーマンスの観点で、5つのSQLビルダの比較・検証を行いました。その中で特に、SqlifyとSqExpressはとても魅力的なライブラリだと感じました。

Sqlifyは、基本的なSQL構文をカバーし、C#リテラルがバインド変数として扱われ、パフォーマンスに優れます。複雑なSQLが必要ないプロジェクトにおいて、有力な選択肢となりそうです。対応している構文を事前に確認しておくとよいでしょう。

SqExpressは、より広範な構文に対応します。本記事では触れませんでしたが、SQLビルダとしての機能のみでなくデータアクセスの機構が内包されています。また、テーブルを表すC#クラスからDBでの作成用スクリプトを生成する、逆にDBで作成済みのテーブルからC#クラスを作成するといった機構も用意されるなど、非常にリッチなライブラリとなっています。計画的に導入することにより開発効率の向上に寄与しそうです。ただし、一部対応していない構文があることや、C#リテラルがバインド変数化されない点には注意が必要です。

他のライブラリも含めて、優劣があるというよりは特性が異なるため、自分のプロジェクトにおいてどのようなSQLビルダを必要とするか要件を明らかにした上で、選定する必要があると思いました。本記事が選定時の参考になれば幸いです。他のライブラリの情報や記載の誤りがあれば、是非コメントをお願いします。

参考文献

  1. Entity Frameworkを使ったプログラマの末路・・・ | レンコン畑でつかまえて ↩

  2. Sqlify Features | NuGet Gallery ↩

4
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?