lambdicSql - Queryの自由な組み立て - ②Expression単位 -

LambdicSqlではExpression単位で分解、構築できるようにしました。
Castっていうキーワードがポイントです。Sql.Query().ExpressionはISqlExpressionという型を返します。これを文中に埋め込むとその文字列になります。でもISqlExpressionではコンパイル通らないので、それがコンパイルできる型にキャストするわけです。

public void SqlExtension()
{
    var expMoneyAdd = Db<DB>.Sql(db => db.tbl_remuneration.money + 100);
    var expWhereMin = Db<DB>.Sql(db => 3000 < db.tbl_remuneration.money);
    var expWhereMax = Db<DB>.Sql(db => db.tbl_remuneration.money < 4000);

    var query = Db<DB>.Sql(db =>
        Select(new SelectedData()
        {
            Name = db.tbl_staff.name,
            PaymentDate = db.tbl_remuneration.payment_date,
            //作っておいたExpressionを組み込み
            //最終的にはdecimalで扱いたい
            Money = expMoneyAdd.Cast<decimal>(),
        }).
        From(db.tbl_remuneration).
            Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id).
        //組み合わせることも可能です。
        Where(expWhereMin && expWhereMax).
        OrderBy(new Asc(db.tbl_staff.name)));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT
tbl_staff.name AS Name,
	tbl_remuneration.payment_date AS PaymentDate,
	(tbl_remuneration.money) + (@p_0) AS Money
FROM tbl_remuneration
	JOIN tbl_staff ON (tbl_remuneration.staff_id) = (tbl_staff.id)
WHERE ((@p_1) < (tbl_remuneration.money)) AND ((tbl_remuneration.money) < (@p_2))
ORDER BY
	tbl_staff.name ASC

条件文を構築しやすくるためのヘルパ

条件文も上の書き方で、連結していけるわけですが、ちょっと面倒です。

//こんな感じで連結可能
var expWhereMin = Db<DB>.Sql(db => 3000 < db.tbl_remuneration.money);
var expWhereMax = Db<DB>.Sql(db => db.tbl_remuneration.money < 4000);
var expWhere = Db<DB>.Sql(db => expWhereMin && expWhereMax.Cast);

これはよくやるので、簡単にかけるようにしました。
Whereの組み立てで、その条件を有効にするか否かは頻繁にあり、そこで便利に使うことができます。ちなみに全部無効でExpressionが空になった場合はWhere句自体が消えます。

public void ContinueConditions(bool minEnable, bool maxEnable)
{
    //その条件を有効にするかどうかを指定することができる
    var exp = Db<DB>.Sql(db =>
        Condition(minEnable, 3000 < db.tbl_remuneration.money) &&
        Condition(maxEnable, db.tbl_remuneration.money < 4000) &&
        db.tbl_staff.id == 1);

    var query = Db<DB>.Sql(db =>
        Select(Asterisk(db.tbl_remuneration)).
        From(db.tbl_remuneration).
            Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id).
        //特殊仕様で渡されたExpressionが空ならWhere句は消える。Havingも同様。
        Where(exp));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

履歴

2016/09/02

lambdicSql - Queryの自由な組み立て - ①句単位 -

Queryの組み立てシリーズです。まずは、クエリ単位で分割、構築する方法です。Concatでクエリを連結できるようにしました。Concatは型に関してわざと緩くしました。使っているDBの型が違っても結合できます。最終的にSQL文字列になったときに意味が通っていたらOKです。（ダメならSQL実行で例外が発生します。)

public void QueryConcat()
{
    //句単位で別々に記述
    var select = Db<DB>.Sql(db =>
        Select( new SelectedData()
        {
            Name = db.tbl_staff.name,
            PaymentDate = db.tbl_remuneration.payment_date,
            Money = db.tbl_remuneration.money,
        }));

    var from = Db<DB>.Sql(db =>
            From( db.tbl_remuneration).
        Join( db.tbl_staff,  db.tbl_remuneration.staff_id == db.tbl_staff.id));

    var where = Db<DB>.Sql(db =>
        Where( 3000 < db.tbl_remuneration.money && db.tbl_remuneration.money < 4000));

    var orderby = Db<DB>.Sql(db =>
        OrderBy(new Asc(db.tbl_staff.name)));

    //クエリ構築
    var query = select.Concat(from).Concat(where).Concat(orderby);

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT
tbl_staff.name AS Name,
	tbl_remuneration.payment_date AS PaymentDate,
	tbl_remuneration.money AS Money
FROM tbl_remuneration
	JOIN tbl_staff ON (tbl_remuneration.staff_id) = (tbl_staff.id)
WHERE ((@p_0) < (tbl_remuneration.money)) AND ((tbl_remuneration.money) < (@p_1))
ORDER BY
	tbl_staff.name ASC

例えば、Where句以外は使いまわしとかの場合は、外の句はstatic readonlyにしておくと便利ですね。

static readonly SqlQuery<SelectedData> select = Db<DB>.Sql(db =>
    Select(new SelectedData()
    {
        Name = db.tbl_staff.name,
        PaymentDate = db.tbl_remuneration.payment_date,
        Money = db.tbl_remuneration.money,
    }));

static readonly ISqlQuery from = Db<DB>.Sql(db =>
        From(db.tbl_remuneration).
    Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id));

static readonly ISqlQuery orderby = Db<DB>.Sql(db =>
    OrderBy(new Asc(db.tbl_staff.name)));
        
public IEnumerable<SelectedData> Execute(decimal min, decimal max)
{
    //Where句だけここで作成
    var where = Db<DB>.Sql(db =>
        Where( 3000 < db.tbl_remuneration.money && db.tbl_remuneration.money < 4000));

    //クエリ構築
    var query = select.Concat(from).Concat(where).Concat(orderby);

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

履歴

2016/09/02

lambdicSql - Queryの自由な組み立て -

LambdicSqlα_0.0.40 をリリースしました。
Queryの組み立てに関して仕様追加と、既存の仕様を変更しました。α版なんで、破壊的な変更もバンバンいれます。
www.nuget.org

Queryは組み合わせることが重要

以前、おださんからこのような資料を紹介してもらいました。

SQL上級者こそ知って欲しい、なぜO/Rマッパーが重要か？ from kwatch

www.slideshare.net
この中では、クエリを分解、構築、抽象化できると便利ですよ。って書かれてました。ということで、これをLambdicSqlに取り入れてみました。

LambdicSqlで可能な分解、構築

1. 句単位で分解、構築
2. Expression単位で分解、構築
3. サブクエリ

サブクエリは前からありました。
今回は1,2を追加しました。サンプルコードが長くなるので、3回に分けて書きます。

lambdicSql - CASE式に対応しました -

LabdicSql_α0.0.39をリリースしました。CASEに対応しています。
www.nuget.org
迷いましたがWHENとTHENはC#上ではWhenThenで書くようにしました。それからENDは書かなくても良いようにしました。
SELECT句への合成はサブクエリと同じ書き方で書くようにしました。

検索ケース式

[TestMethod]
public void Case1()
{
    var query = Db<DB>.Sql(db =>
        Select(new SelectedData()
        {
            Type = Case().
                        When(db.tbl_staff.id == 3).Then("x").
                        When(db.tbl_staff.id == 4).Then("y").
                        Else("z").
                    End()
        }).
        From(db.tbl_staff));
            
    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SQLです。ちょっと頑張って綺麗にタブが入るようにしました。この改善でサブクエリにも同様に改善されています。

SELECT
	CASE
		WHEN (tbl_staff.id) = (@p_0) THEN @p_1
		WHEN (tbl_staff.id) = (@p_2) THEN @p_3
		ELSE @p_4
	END AS Type
FROM tbl_staff

単純ケース式

public void Case2()
{
    var query = Db<DB>.Sql(db =>
        Select(new SelectedData()
        {
            Type = Case(db.tbl_staff.id).
                        When(3).Then("x").
                        When(4).Then("y").
                        Else("z").
                    End()
        }).
        From(db.tbl_staff));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT
	CASE tbl_staff.id
		WHEN @p_0 THEN @p_1
		WHEN @p_2 THEN @p_3
		ELSE @p_4
	END AS Type
FROM tbl_staff

履歴

2016/09/02 β版対応

LambdicSql -DistinctとAll

α0.0.36をリリースしました。
www.nuget.org

DistinctとAll

enumで指定できるように変更しました。

Select文

[TestMethod]
public void Distinct()
{
    var query = Db<DB>.Sql(db =>
        Select(AggregatePredicate.Distinct, new
        {
            id = db.tbl_remuneration.staff_id
        }).
        From(db.tbl_remuneration));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT DISTINCT
tbl_remuneration.staff_id AS id
FROM tbl_remuneration

集計関数

[TestMethod]
public void GroupByPredicateDistinct()
{
    var query = Db<DB>.Sql(db =>
        Select(new SelectedData()
        {
            Name = db.tbl_staff.name,
            Count = (int)Count(AggregatePredicate.Distinct, db.tbl_remuneration.money),
            Total = Sum(AggregatePredicate.Distinct, db.tbl_remuneration.money)
        }).
        From(db.tbl_remuneration).
            Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id).
        GroupBy(db.tbl_staff.id, db.tbl_staff.name));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);
            
    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT
tbl_staff.name AS Name,
	COUNT(DISTINCT tbl_remuneration.money) AS Count,
	SUM(DISTINCT tbl_remuneration.money) AS Total
FROM tbl_remuneration
	JOIN tbl_staff ON (tbl_remuneration.staff_id) = (tbl_staff.id)
GROUP BY tbl_staff.id, tbl_staff.name

lambdicSql - パフォーマンス改善せねば④ SQLite -

プロファイラで見てわかりやすかったところを対応しました。
www.nuget.org

こっから先は難しいですね。
で、もう少しノイズを減らすためにDBをSQLサーバーからSQLiteに変えました。ファイルアクセスなんで余計なブレはすくないでしょう。ちなみに計測はSurfacePro4のCorei5でやってます。
SQLiteはDBアプリとか必要なくて、Nugetから落としてくるだけで使えます。

あれ？EF入るの？
EFないバージョンと分けてくれればいいのに。

SQLiteは方言対応とかあるのですが、まあ、今回の計測程度は使えます。で、計測。SQLは一件だけ取得するものです。

SELECT
        IntVal,
        FloatVal,
        DoubleVal,
        DecimalVal,
        StringVal
FROM
        TableValues
WHERE
        ((TableValues.IntVal) = (@p_0));

(msec)	Lambdic	Dapper
1	0.5243	0.2986
2	0.1682	0.0964
3	0.137	0.0824
4	0.1407	0.0779
5	0.1341	0.0734
6	0.1226	0.0746
7	0.1234	0.0717
..	..	..
..	..	..
500	0.1001	0.0648
平均	0.13300521	0.075227455	←差 0.057777756

速えー！
500回の平均で57µまで詰めました！
（でも、Dapperの1.76倍かかっているのか・・・）
このケースでは実使用上は問題ないレベルなんじゃないかなー。
まあ、ボトルネックはクエリ作成だから、もう少し色んなパターンで計測しないとダメでしょうけど。

ていうか、SQLite対応先にやろう。

lambdicSql - パフォーマンス改善せねば ③計測 -

※この書き方は最新のLambdicSqlとは異なります。速度計測の記録なので以前のままにしております。

もう少し詳しく計測してみることにします。
VisualStudioのパフォーマンスプロファイラが便利ですね。
VSTestではイマイチやりにくいのでコンソールアプリでやります。

using System;

namespace Performance
{
    class Program
    {
        static void Main(string[] args)
        {
            //ユーザー入力を待つ
            Console.ReadLine();
            
            //テスト実行
            SelectTime.CheckLambdicSqlCondition();
            Console.WriteLine("Finish");
            Console.ReadLine();
        }
    }
}

起動時間とかでノイズを拾うと結果が見づらくなるので、一時停止して開始を選びます。

で、起動が終わると、再開させます。

そして何かキー入力をして、本当に計測したい処理を実行。
CheckLambdicSqlConditionの中では1000回クエリを発行しています。

internal static void CheckLambdicSqlCondition()
{
    CheckTimeCore(connection =>
    {
        int x = 1;
        var datas = Sql.Query<DB>().SelectFrom(db => db.TableValues).
               Where(db => db.TableValues.IntVal == x).ToExecutor(connection).Read().ToList();
    });
}
static void CheckTimeCore(Action<SqlConnection> action)
{
    using (var connection = new SqlConnection(TestEnvironment.ConnectionString))
    {
        connection.Open();
        for (int i = 0; i < 1000; i++)
        {
            action(connection);
        }
    }
}

終わったら停止を押します。

こんな感じでログが出ます。

ファイアスポットが分かりますね。
で、クリックするとさらに詳細な情報が分かります。

Expressionの解析も重いですが、しょうもないところでも時間を無駄にしていることが分かりますね。
まだまだ改善できそうです！