NHibernate query plan for Linq Dml is not cached

[gokhanabatay]

The query plan cache is never hit for linq DML queries.

ISession.Query<StmtAccountStat>().Where(x => x.AccountNo == "1311").Update(x => new StmtAccountStat() { FirstDelayDate = DateTime.Now });

NHLinqExpression.cs

p2 is Entity Class we should ignore in ExpressionParameterVisitor.cs VisitConstant
p5 is the property name that we want to update should be ignored
DmlExpressionRewriter.cs ConvertAssignmentsToDictionaryExpression method below code adds extra 2 parameter p3=1, p4=1 should be ignored

var listInit = Expression.ListInit(
				Expression.New(
					DictionaryConstructorInfo,
					Expression.Condition(
						Expression.Equal(param, Expression.Constant(null, typeof(TSource))),
						Expression.Constant(assignments.Count),
						Expression.Constant(assignments.Count))),
				inits);

IQueryExpression.Key
UPDATE .Select[**StmtAccountStat**, Issuing.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null,**System.Collections.Generic.Dictionary**`2[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Object, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e](.Where[StmtAccountStat, Issuing.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null](NHibernate.Linq.NhQueryable`1[StmtAccountStat], Quote((x, ) => (String.op_Equality(x.AccountNo, p1))), ), Quote((, ) => (new System.Collections.Generic.Dictionary`2[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Object, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e(**Equal(, NULL) ? p3 : p4, )p5**p6)), )

[gokhanabatay]

To demonstrate performance result I wrote a small test that updates same records with different values, Result Linq queries are 8-16 times slower execution, because of QueryPlanCache I think.
UpdateBuilderBatch() implementation is uses batches to send queries as batched (Adonet batch size is 500)

When I look at the log of NHibernate batcher both linq and hql commands executed same time about 15-25 ms but overall elapsed times are to much different.

I can use Hql Batch it just does the job as batch that I want but my team and I prefer Linq over Hql or QueryOver.

One by One update is more slower than batching both linq and hql as expected.

Windows 10 i7 2.88 16GB Ram Oracle 11g NH 5.2.6 Release Mode
Test was run 5 times Elapsed Ms are;
Hql Batch:146
Hql One by One:3913
Linq Batch:668
Linq One by One:4621
Hql Batch:41
Hql One by One:3849
Linq Batch:539
Linq One by One:4294
Hql Batch:60
Hql One by One:3932
Linq Batch:548
Linq One by One:5089
Hql Batch:58
Hql One by One:4124
Linq Batch:518
Linq One by One:5173
Hql Batch:34
Hql One by One:3874
Linq Batch:567
Linq One by One:4272
Linq Batch Full Null:107

var accounts = ISession.Query<StmtAccountStat>().Where(x => x.FirstDelayDate == null).Take(500).Select(x => x.AccountNo).ToList();
var progress = (0, 0);
Random random = new Random();
Stopwatch watch = Stopwatch.StartNew();
for (int i = 0; i < 5; i++)
{
    watch.Restart();
    var batch = ISession.CreateQueryBatchDml();
    foreach (var item in accounts)
    {
        DateTime date = DateTime.Now.AddDays(random.Next(30));
        var query = ISession.CreateQuery($"update {typeof(StmtAccountStat)} set FirstDelayDate = :FirstDelayDate where AccountNo = :AccountNo");
        query.SetParameter("FirstDelayDate", date);
        query.SetParameter("AccountNo", item);
        batch.Add(query);
    }

    batch.Execute();
    Console.WriteLine("Hql Batch:" + watch.ElapsedMilliseconds);
    Commit(ref progress);

    watch.Restart();
    foreach (var item in accounts)
    {
        DateTime date = DateTime.Now.AddDays(random.Next(30));
        var query = ISession.CreateQuery($"update {typeof(StmtAccountStat)} set FirstDelayDate = :FirstDelayDate where AccountNo = :AccountNo");
        query.SetParameter("FirstDelayDate", date);
        query.SetParameter("AccountNo", item);
        query.ExecuteUpdate();
    }

    Console.WriteLine("Hql One by One:" + watch.ElapsedMilliseconds);
    Commit(ref progress);

    watch.Restart();
    foreach (var item in accounts)
    {
        DateTime date = DateTime.Now.AddDays(random.Next(30));
        var query = ISession.Query<StmtAccountStat>().Where(x => x.AccountNo == item)
            .UpdateBuilderBatch()
            .Set(x => x.FirstDelayDate, date)
            .CreateQuery();

        batch.Add(query);
    }
    batch.Execute();
    Console.WriteLine("Linq Batch:" + watch.ElapsedMilliseconds);
    Commit(ref progress);

    watch.Restart();
    foreach (var item in accounts)
    {
        DateTime date = DateTime.Now.AddDays(random.Next(30));
        var query = ISession.Query<StmtAccountStat>().Where(x => x.AccountNo == item)
            .UpdateBuilder()
            .Set(x => x.FirstDelayDate, date)
            .Update();
    }
    Console.WriteLine("Linq One by One:" + watch.ElapsedMilliseconds);
    Commit(ref progress);
}

watch.Restart();
ISession.Query<StmtAccountStat>()
    .Where(x => accounts.Contains(x.AccountNo))
    .Update(x => new StmtAccountStat() { FirstDelayDate = default(DateTime?) });
Console.WriteLine("Linq Batch Full Null:" + watch.ElapsedMilliseconds);
Commit(ref progress);
watch.Stop();
Console.ReadLine();

[gokhanabatay]

@bahusoid
If we return all queries as cacheable we could see good performance improvment, but it seems other issues let Linq type queries too much slower then hql. (I tested with your branch)

private bool IsPlanNotCacheable(IQueryExpression queryExpression)
{
	// 6.0 TODO: add IsPlanCacheable to IQueryExpression interface
	return queryExpression is NhLinqExpression linqExpression && !true;
}

Cached Linq
Hql Batch:155
Linq Batch:441
Hql Batch:40
Linq Batch:457
Hql Batch:41
Linq Batch:459
Hql Batch:55
Linq Batch:431
Hql Batch:36
Linq Batch:398

NonCached Linq
Hql Batch:143
Linq Batch:657
Hql Batch:45
Linq Batch:757
Hql Batch:46
Linq Batch:663
Hql Batch:42
Linq Batch:654
Hql Batch:45
Linq Batch:644