Optimize database traffic with future results in NHibernate

One nice feature that NHibernate has is future results. It is technically a capability to put queries on hold until data is actually asked. When data is asked from one delayed query then all queries are sent to database server as one batch and results are read also as one batch of multiple result sets. This blog post explains how to use future results with NHibernate.

Why future results?

Future results decrease traffic between application and database server. Most of database servers support processing of multiple queries sent as one batch resulting in returning multiple result sets with one batch.

Suppose we have a dashboard showing some charts, tables and gauges. We can send queries to database one by one. After sending query to server we have to wait when results are sent back and then we can execute next query. But our dashboard turns out to be an excellent use case for future results because all queries we need to execute are not dependent on other queries results.

As future results are used to query data from database you should consider using stateless session of NHibernate. Stateless session performs better as it doesn’t have change tracking enabled.

How to use future results?

Using future results is actually easy. We build queries like usually but the last call in row is to ToFuture() or ToFutureValue() method. ToFuture() and ToFutureValue() methods put queries on hold until data is asked.

Let’s start with simple ASP.NET Core model for dashboard.

public class DashboardModel
{
    public IList<Task> MyPendingTasks { get; set; }
    public IList<Task> MyOverDueTasks { get; set; }
    public IList<WorkLog> MyWorkToday { get; set; }
}

We need three database queries to get values to all properties of this model. Here are sample queries that are all delayed until data is actually asked in code.

var myPendingTasks = _nhSession.Query<ProjectTask>
                               .Fetch(t => t.Project)
                               .Where(t => t.AssignedTo.InternalName == userName &&
                                           t.Deadline > DateTime.Now &&
                                           t.Status != ProjectTaskStatusEnum.Closed &&
                                           t.Status != ProjectTaskStatusEnum.WaitingForApproval)
                               .Take(10)
                               .ToFuture();
                                            
var myOverDueTasks = _nhSession.Query<ProjectTask>
                               .Fetch(t => t.Project)
                               .Where(t => t.AssignedTo.InternalName == userName &&
                                           t.Deadline < DateTime.Now &&
                                           t.Status != ProjectTaskStatusEnum.Closed &&
                                           t.Status != ProjectTaskStatusEnum.WaitingForApproval)
                               .Take(10)
                               .ToFuture();
 
var myWork = _nhSession.Query<WorkLog>
                       .Fetch(w => w.Task)
                       .ThenFetch(w => w.Project)
                       .Where(w => w.User.InternalName == userName &&
                                   w.Date == DateTime.Now.Date)
                       .ToFuture();
 
var model = new DashboardModel();
model.MyPendingTasks = (await myPendingTasks.GetEnumerableAsync()).ToList();
model.MyOverDueTasks = (await myOverDueTasks.GetEnumerableAsync()).ToList();
model.MyWorkToday = (await myWork.GetEnumerableAsync()).ToList();

Asking of data happens when model.MyPendingTasks is assigned. Here’s the query that was sent to database server when data was asked first time from some of those delayed queries. NB! I left out fields list from FROM clauses to keep queries more readable.

select 
    ... 
from 
    ProjectTasks projecttas0_ 
    inner join Users user1_ on 
        projecttas0_.AssignedToId=user1_.Id 
    left outer join Projects project2_ on 
        projecttas0_.ProjectId=project2_.Id 
where 
    user1_.InternalName=@p0 and 
    projecttas0_.Deadline>@p1 and 
    (projecttas0_.Status<>@p2 or projecttas0_.Status is null) and 
    (projecttas0_.Status<>@p3 or projecttas0_.Status is null) 
ORDER BY 
    CURRENT_TIMESTAMP 
OFFSET 0 ROWS FETCH FIRST @p4 ROWS ONLY;
 
select
    ...
from 
    ProjectTasks projecttas0_ 
    inner join Users user1_ on 
        projecttas0_.AssignedToId=user1_.Id 
    left outer join Projects project2_ on 
        projecttas0_.ProjectId=project2_.Id 
where 
    user1_.InternalName=@p5 and 
    projecttas0_.Deadline<@p6 and 
    (projecttas0_.Status<>@p7 or projecttas0_.Status is null) and 
    (projecttas0_.Status<>@p8 or projecttas0_.Status is null) 
ORDER BY 
    CURRENT_TIMESTAMP 
OFFSET 0 ROWS FETCH FIRST @p9 ROWS ONLY;
 
select
    ...
from 
    WorkLogs worklog0_ 
    inner join Users user1_ on 
        worklog0_.UserId=user1_.Id 
    left outer join ProjectTasks projecttas2_ on 
        worklog0_.TaskId=projecttas2_.Id 
    left outer join Projects project3_ on 
        projecttas2_.ProjectId=project3_.Id 
where 
    user1_.InternalName=@p10 and 
    worklog0_.[Date]=@p11;

We can clearly see that it was batch of multiple queries that was sent to database server with one shot.

NB! If you want to mimic DbContext from Entity Framework Core then please take a look at my blog post NHibernate on ASP.NET Core.

Using Count() with future results

Using Count() function is not very straightforward but it’s there and it’s easy to use. I mentioned previously extension method called ToFutureValue(). This method helps us turn queries to count queries.

var futureCount = _nhSession.Query<WorkLog>
                            .Fetch(w => w.Task)
                            .ThenFetch(w => w.Project)
                            .Where(w => w.User.InternalName == userName &&
                                        w.Date == DateTime.Now.Date)
                            .ToFutureValue(v => v.Count());
 
var count = await futureCount.GetValueAsync();

Here is the query sent to database.

select 
    cast(count(*) as INT) as col_0_0_ 
from 
    WorkLogs worklog0_ 
    inner join Users user1_ on
        worklog0_.UserId=user1_.Id 
where 
    user1_.InternalName=@p0 and 
    worklog0_.[Date]=@p1

Same way we can use also other aggregate functions but also LINQ methods like Any(), FirstOrDefault(), etc.

NB! If you prefer to write queries using criteria API or if you don’t have any other option than criteria API then future results are also supported there. With criteria API you can also use query batches. Query batches let you define sets of queries that are sent to database together. Future results are gathered by NHibernate and they are all executed as soon as data is actually asked from some future query.

Wrapping up

Future results in NHibernate lead to decreased database traffic as all delayed queries are sent to database with one shot and results are also read back with one shot. It may put some additional load to client application but in these cases I suggest to use stateless sessions as they come with less overhead and no change tracking. Future results are not silver bullets and your mileage may vary. Like always – if you make any performance improvements to your application, make sure you measure the effect.