Query duration in NHibernate Profiler

Question

I have an ASP .Net MVC application which uses Fluent NHibernate to access an oracle database. I also use NHibernate Profiler for monitoring the queries generated by NHibernate. I have one query which is really simple (selecting all rows from a table with 4 string columns). It is used for creating a report in CSV format. My problem is that the query is taking very long to run, and I would like to get a bit more insight into the durations displayed by nhprof. With 65.000 rows, it is taking 10-20 seconds, even though the "Database only" duration only shows something like 20 ms. Network lag should not make out a lot of this time, because the servers are on the same gigabit LAN. I don't expect people to be able to pinpoint for me exactly where the bottleneck is, but what I would like to know is some more details about how to read the duration measurements in NHibernate profiler.

What is included in the "Database only" part, and what is included in the "Total time"? Does the total time also include the processing done after populating the C# objects, so that this time is actually for the entire http request? Knowing more about this would hopefully make me able to eliminate some factors.

This what the NHibernate mapping class looks like:

Table("V_TICKET_DETAILS");

CompositeId()
     .KeyProperty(x => x.TicketId, "TICKET_ID")
     .KeyProperty(x => x.Key, "COLUMN_NAME")
     .KeyProperty(x => x.Parent, "PARENT_NAME");

 Map(x => x.Value, "COLUMN_VALUE");

And the query generated by nh profiler is like this:

SELECT this_.TICKET_ID    as TICKET1_35_0_,
       this_.COLUMN_NAME  as COLUMN2_35_0_,
       this_.PARENT_NAME  as PARENT3_35_0_,
       this_.COLUMN_VALUE as COLUMN4_35_0_
FROM   V_TICKET_DETAILS this_

The view is really simple, only joining two tables on a 2-digit integer.

I am by no means a database expert, so I would be happy for all comments that would point me in the correct direction.

Answer 1

The total time is for the call to the nHib query only.
However, it includes, in addition to the time in the db, the time it takes nHib to populate your entities (hydration). and that's likely your culprit.
I've had a similar problem, perhaps some of the suggestions there may help you.

The bottom line is that nHib is not really intended to load large datasets.
If none of the suggestions I got helped you, I would suggest a couple of things:
1. It's unlikely that your user needs to view 65,000 rows of data at the same time. perhaps you can find a way to filter the data so that the result set is smaller (and more readable).
2. otherwise- if it's, as you say, an 'special' case that only occurs when you generate a report- you don't have to use nHib. you can just use, say, good ol' ADO.Net classes...

Answer 2

there is also IStatelessSession which is intended for such situations. It doesnt have a session cache and saves a lot of work. It should be a lot faster.

using (var session = factory.OpenStatelessSession())
{
}