fluent nhibernate query taking a long time to sort a mapped formula with paging

Question

We have a data grid to display member records for our clients. We recently were tasked to add paging to our application so that our large clients (500,000+ members).

Our table that we grab the data from has 10 columns. When pull our data into our application we added 3 additional columns that are all calculated fields

i.e.

SELECT SUM(tableX.Cost) FROM tableX WHERE tableX.ID = this_.ID

where "this_" is the alias for our members table and tableX represents the other table that we need to grab data from.

In our query we must provide the ability to order the columns, but i've noticed that when I try to order the calculated fields that it takes a significant amount of time compared to when I ORDER BY an column that exists in the table. This makes sense that it takes longer, but when it takes a couple minutes to load only 1000 records and there will be 500+ pages to go through. Then I feel this makes paging pointless.

I've looked a bit into maybe doing a 2nd level cache with nhibernate, but from what i've read so far i'm not sure if will solve this problem.

By the way one of the maps is:

Map(x => x.Cost)
    .Formula(@"(SELECT SUM(_Claims.Cost) FROM _Claims WHERE _Claims.ID = this_.ID")
    .Nullable();

and for the query

    var list = CurrentSession()
               .SetFirstRsult(startPos)
               .SetMaxResults(1000)
               .SetTimeout(100)
               .Add(Restrictions.Eq("ClientID", _clientID));

    if(isASC)
    {
        list.AddOrder(Order.Asc(Projetions.Property(_propertyString)));
    }
    else
    {
        list.AddOrder(Order.Desc(Projetions.Property(_propertyString)));
    }

    return list.List<Member>();

I'm running out of ideas for this. So if anyone can point us in the right direction I'd appreciate it a lot.

Thanks

Answer 1

This is actually not so much a NHibernate problem, but a general SQL problem.

Of course it is sorted on the server, but the sum will be calculated for all the records before they can be sorted. You should be able to verify that by using an SQL profiler or by creating an SQL function / procedure that calculates the sum and insert that into your query and then check how often it is called. If you have a table with 500,000+ members it will be called 500,000+ times; there is just no way around it.

What you could try is this:

1. Make sure you use an index on Claims.ID (a foreign key does the trick).
2. Copy&Paste the generated SQL and run it directly on the database and see whether that takes a long time.
3. If you don't mind having some form of redundancy you can add new columns for the calculated fields in your Members table and use triggers (insert/delete) on the Claims table to update those columns accordingly.
4. If you don't want to use triggers you could also create a scheduled database event to do the calculation. (Of course, that will only be an option if you don't need real-time data).