Hibernate version:1.2
SQL Server 2000

I have a small app that uses NHibernate to read text files and insert records into a single table. Records may be repeated between files. For this reason I use a criteria query to find pre-existing records in the table and avoid the insert. If the query returns nothing, then an object is created, populatred and saved.

This all works. However, it is slower than I expected. Using Profiler I have discovered that HNibernate is doing a login and logout for each command (select or insert) that is being executed. Is there some way to stop this? Am I worrying about nothing?

My session factory configuration is included here:



Thanks,
Jim

Hi,

1. Is your application multi-threaded? I.e. it will open multiple sessions at the same time.

2. Do you manage your own db connections? I.e. you call SessinFactory.OpenSession(connection) instead of SessionFactory.OpenSession().

3. Do you play with ISession.Connection or ISession.Transaction? Normally I don't need to.

If your answers are no to both questions, here is the third: do you mind posting codes between SessionFactory.OpenSession() and ISession.Dispose() ?

1. The code will execute on a single thread in a larger app that is multi-threaded. However, the testing environment I have bee using is single-threaded.

2. I do not manage my own connections. .OpenSession() is used.

3. No transaction is used.

Here is the NHibernate code that executes between session open and dispose. It is not practical to include all of the application code. But all of the NHibernate code is listed. This code block is part of a method that is called once for each record in a text file. The program opens the text file and the NHibernate session then executes this code block for each record in the text file.

I did try it with and without the .Flush() without any difference in login/logout behaviour.

Thank you for your assistance,
Jim

Do you mean that you are getting login/out before and after the criteria.List() and the session.Save(), or at OpenSession() and session.Dispose()? I would expect the latter, but not the former.

Perhaps this is also affected by the lack of transactions? Try wrapping your code with a simple transaction usage, like so:



Another suggestion: check out the depth of the Criteria interface. You should be able to perform your query with an Exists rather than returning all matching rows.

Yes. I do mean that I am getting a login/logout around each individual SELECT and a login/logout around each individual INSERT.

I will try the transaction. I did not use it in this case because I will essentially end up wrapping with a trasnsaction wrapped around each insert. So it did not seem to make sense for this app. I could start a transaction at the beginning of the file and keep it until the file is complete before doing a commit, but that would violate the principal of keeping transaciton scope to a single unit of work. A complete file would be ~1k to ~15k inserts. That seems a lot for a single transaction.

On the idea of using an EXISTS rather than returning a list of records. That is a good idea and is what I would have done in the past if I was just writing SQL code. But, as a newbie to the Hibernate world I am not familiar with ho to do that yet. Also, the nature of the data is that the query will always return either zero or one record. So the expense of returning the list is relatively small, though I will admint the EXISTS is still less.

Thanks for your input,
Jim

When I start a transaction at the beginning of a file and do all of my inserts (thousands) within the scope of that transaction it does all happen in a single logon/logoff and my process is much (more than 10x) faster.

When I wrap a transaction around each individual insert I get a login/logout on each statement, or in this case, transaction.

According to SQL Profiler the majority of the time is spent in the logout.

When I remove the .Flush() it gets much faster. But, it still does the login/logout for every SELECT and INSERT.
There must be some database actions that I am not capturing, because I can see no difference in my SQL Profile log when I remove the .Flush() other than the timestamps indicating less time is being spent in the logout.

Not directly related to this problem, but I did cut out half (nearly) of the database calls in the app by selecting all of the records that from the table that fall into the time scope of my file and loading them into a Dictionary collection and doing my lookup from there rather than hitting the DB on each record.

I have not yet resolved this. But, I have learned several useful things. Inside of a transaction the login/logout does not happen, makes sense a transaction cannot exist across sql login sessions. It turns out that there is not much overhead in this login and logout. It is enough to make some difference but not much. I was having problems but was able to improve performace by not doing a .Flush() on each save and mostly by turning off log4net logging that the nHibernate library was doing. You should never run NHibernate with a log4net logging level of DEBUG or ALL. LOL...

I am still confused by the login/logout behaviour. I thought the the whole point of connection pooling was to elimiate that.

Thank you to those that responded.

Jim

I believe this behaviour is related to a change that was introduced in 1.2 (vs 1.0). The default behaviour now is to obtain a new connection for every transaction, rather than one connection for the entire lifetime of the Session. If you do not explicitly declare a transaction, then an implicit transaction is created for every individual statement, hence the connection per statement behaviour. You can turn this behaviour off in the configuration, using the connection_release_mode property (I can't recall the details, but it's in the docs somewhere).

Thanks. That sounds like exactly what I am looking for.

I found a reference to hibernate.connection.release_mode in the reference documentation. It says for connections obtained through ISessionFactory.GetCurrentSession() see section 2.3 "Contextual Sessions". That does apply to my situation. So I went there. In the third paragraph for Section 2.3 Contextual Sessions it says "See the API Documentation" ??? I have been looking for anything called API documentation without luck. Does this comment mean that I should look at the source code for the documentation?

Thanks
Jim

I don't think there is any API documentation, at least I've never found it. This section explains connection release modes:

[url]http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html_single/#transactions-connection-release
[/url]