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();

[bahusoid]

@gokhanabatay Just ignore my last comment (I've already deleted it).

[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

[gokhanabatay]

Hi @bahusoid,
I think the issue with linq is reflection there is a lot of reflection in NHibernate code, it could be more faster if we use cached delegates.
Also there is a lot of MakeGenericMethod call that could be cached via method info and generic method types using ConcurrentDictionary<MethodInfo, ConcurrentDictionary<Type, Delegate>>
What do you think?

https://www.nuget.org/packages/DelegatesFactory/

Think about below code;

public virtual IQueryable CreateQuery(Expression expression)
{
	MethodInfo m = CreateQueryMethodDefinition.MakeGenericMethod(expression.Type.GetGenericArguments()[0]);

	return (IQueryable)m.Invoke(this, new object[] { expression });
}

//Without reflection
public virtual IQueryable CreateQuery(Expression expression)
{
        //ReflectionHelper.Get returns cached delegate for input method info and generic type parameters
	var query = (Func<INhQueryProvider, Expression, IQueryable>)ReflectionHelper.Get(CreateQueryMethodDefinition, expression.Type.GetGenericArguments()[0]);

	return query(this, expression);
}

https://internetexception.com/post/2016/09/02/Faster-than-Reflection-Delegates-Part-3

[bahusoid]

I think the issue with linq is reflection

Maybe but you should provide some evidence. I'm pretty sure CreateQuery isn't called from LINQ DML queries and in most cases CreateQuery<T> should be called.

[gokhanabatay]

@bahusoid
I just give an example there is a lot of reflection call all needs to be checked; for instance below code always executed if we use a linq query.
Test Code with simple entity with 2 columns and returns 2 rows of data.

for (int i = 0; i < 10; i++)
{
    Stopwatch stopwatch = Stopwatch.StartNew();
    for (int j = 0; j < 100; j++)
    {
        var query = ISession.CreateQuery($"from {typeof(BtcParam)}");
        var result = query.List<BtcParam>();
    }
    stopwatch.Stop();
    Console.WriteLine($"Hql:{stopwatch.ElapsedMilliseconds} Ms");
    stopwatch.Restart();
    for (int j = 0; j < 100; j++)
    {
        var result = ISession.Query<BtcParam>().ToList<BtcParam>();
    }
    stopwatch.Stop();
    Console.WriteLine($"Linq:{stopwatch.ElapsedMilliseconds} Ms");
}

AbstractSessionImpl.cs

--With Reflection
public virtual IList List(IQueryExpression queryExpression, QueryParameters parameters)
{
	 var results = (IList) typeof(List<>)
		.MakeGenericType(queryExpression.Type)
		.GetConstructor(System.Type.EmptyTypes)
		.Invoke(null);
	List(queryExpression, parameters, results);
	return results;
}

--Without Reflection 
ConcurrentDictionary<System.Type, Func<object>> genericTypes = new ConcurrentDictionary<System.Type, Func<object>>();

public virtual IList List(IQueryExpression queryExpression, QueryParameters parameters)
{
	var newFunc = 
		genericTypes.GetOrAdd(queryExpression.Type, x => { return Expression.Lambda<Func<object>>(
		Expression.New(typeof(List<>).MakeGenericType(queryExpression.Type))).Compile(); });

	var results = (IList) newFunc();

	List(queryExpression, parameters, results);
	return results;
}

Test Results;
With Reflection:
Hql:1350 Ms
Linq:1266 Ms
Hql:1528 Ms
Linq:1842 Ms
Hql:1859 Ms
Linq:1191 Ms
Hql:1321 Ms
Linq:1582 Ms
Hql:1642 Ms
Linq:1466 Ms
Hql:1371 Ms
Linq:1448 Ms
Hql:1616 Ms
Linq:1826 Ms
Hql:1514 Ms
Linq:1269 Ms
Hql:1247 Ms
Linq:1549 Ms
Hql:1462 Ms
Linq:1276 Ms

Without Reflection:
Hql:1200 Ms
Linq:1233 Ms
Hql:1178 Ms
Linq:1186 Ms
Hql:1117 Ms
Linq:1101 Ms
Hql:1161 Ms
Linq:1169 Ms
Hql:1146 Ms
Linq:1136 Ms
Hql:1127 Ms
Linq:1102 Ms
Hql:1094 Ms
Linq:1178 Ms
Hql:1123 Ms
Linq:1119 Ms
Hql:1126 Ms
Linq:1104 Ms
Hql:1159 Ms
Linq:1107 Ms