Skip to main content

NHibernate performance issues #4: slow query compilation - named queries

NHibernate provides many approaches how to query the database:
First three of these querying methods define the body of query in the other than native SQL. It implies that NHibernate must transform these queries into native SQL - according to given dialect, e.g. into native MS SQL query.

If you really want to develop all-times fast application the described process can present unpleasant behavior. How to avoid query compilation?

Compiled named queries

Its ridiculous but everyone met compiled (and named) query. If you have at least once browsed the log which NHibernate produced, you have had to meet similar set of lines:

2010-12-13 21:26:42,056 DEBUG [7] NHibernate.Loader.Entity.AbstractEntityLoader .ctor:0 Static select for entity eu.podval.NHibernatePerformanceIssues.Model.Library: SELECT library0_.Id as Id1_0_, library0_.version as version1_0_, library0_.Address as Address1_0_, library0_.Director as Director1_0_, library0_.Name as Name1_0_ FROM [Library] library0_ WHERE library0_.Id=?

When NHibernate starts (SessionFactory as spring.net bean in my case) he complies certainly usable queries. In displayed case, NHibernate knows that he'll probably search library entity in database by it's id so he'll generate native SQL query into internal cache and if the developer's code call Find for Library entity, he'll use exactly this pre-compiled select query in native SQL.

What's the main benefit? If you would call thousand times library's Find NHibernate would always has to compile the query. Instead of this inefficient behavior, NHibernate does it only once and uses pre-compiled version later.

How to speed you query? Use pre-complied named queries

As I've already written, NHibernate generates pre-compiled queries, stores them in the cache and uses them if necessary. NHibernate is the great framework, so he makes available such functionality even for you :-)

Simply you can declare list of HQL (or SQL) named queries within any mapping (hbm.xml) file, NHibernate loads the file, parse queries and preserves the pre-compiled version in his internal cache so called queries in runtime not have to be intricately parsed to native SQL, but he'll use already prepared ones.

How to use named queries?
  1. Define new hbm.xml file which will include your named queries, e.g. named-queries.hbm.xml
  2. Place this file within such assembly which NHibernate searches for hbm mapping files. Maybe you should have to update you Fluent NHibernate configuration to search for these files. Following code will do it for you or see FluentNHibernateSessionFactory.
m.HbmMappings.AddFromAssembly(Assembly.Load(assemblyName));
Now, it's time to write your named-queries.hbm.xml file. It has the following structure.
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

  <query name="Book.with.any.Rental">
    <![CDATA[
    select b from Book b
      where exists elements(b.Rentals)
    ]]>
  </query>

</hibernate-mapping>
How to use it?
IList<Book> books = SessionFactory.GetCurrentSession().GetNamedQuery("Book.with.any.Rental").List<Book>();

How is the speed up of named queries?

Lets say, I'll use Book.with.any.Rental query for any measures to we'll see how omitted query compilation improves test response.

I've executed the test for both named query and plain HQL. According to debug labels, plain HQL case spent 40ms by parsing of HQL to native SQL.

Note that all written till now applies only to the first call. NHibernate is tricky framework so he caches queries for you automatically when he compiles them for first time. Lets call method to get books with any rental two times (first level cache is cleared among the calls):
  • first call took 190ms
  • second one only 26ms 
It's also necessary to admit that database has also own query cache :-) The result is clear anyway.

What are real advantages of named queries?

It doesn't seem such brilliant think to moil with writing the queries in xml file. What are real benefits?
  1. Speed (of the first call) - described example save 40ms of method call. It doesn't seem so much. Imagine that you are developing huge project having almost hundred queries. It can save a lot of time! You should also notice that chosen query was very simple. According to my experiences, the compilation of more complicated query takes at least 200ms. It's not small amount of time when you develop very quick application
  2. HQL parse for error on startup - you'll find out that your query is correct or wrong at application's startup because NHibernate do these things when he starts. You haven't wait till the call of desired query
  3. Clean code - you aren't mixing C# code together SQL (HQL) code
  4. Possibility to change query code after the application compilation - consider that you can change your HQL or SQL even if application was already compiled. You can simply expose named query hbm.xml file as ordinary xml file and you can tune your queries at runtime - means without additional compilation
You can also see Series of .NET NHibernate performance issues to read all series articles.

Comments

Popular posts from this blog

NHibernate performance issues #3: slow inserts (stateless session)

The whole series of NHibernate performance issues isn't about simple use-cases. If you develop small app, such as simple website, you don't need to care about performance. But if you design and develop huge application and once you have decided to use NHibernate you'll solve various sort of issue. For today the use-case is obvious: how to insert many entities into the database as fast as possible?

Why I'm taking about previous stuff? The are a lot of articles how the original NHibernate's purpose isn't to support batch operations, like inserts. Once you have decided to NHibernate, you have to solve this issue.

Slow insertion
The basic way how to insert mapped entity into database is:
SessionFactory.GetCurrentSession().Save(object);But what happen when I try to insert many entities? Lets say, I want to persist
1000 librarieseach library has 100 books = 100k of bookseach book has 5 rentals - there are 500k of rentals It's really slow! The insertion took exactly

Tomcat 7 remote deployment

I decided to provide automatic deployment of war packaged application using Jenkins and Deployment plugin. The target platform is Amazon with Tomcat 7, see nice set of articles to find out how to setup such environment for free.

Well, there is couple of tutorials but they missing some points so it pushed me to lost one hour of my work.

What I gotFresh installation of Tomcat 7 on remote machine with opened 8080 port on firewallPersonal war file supposed to be deployed
How to push it to tomcat?
1. First of all, there is simple configuration of tomcat users in file tomcat-users.xml - it was my pain in the ass :-) As original comprehensive documentation says, it's necessary to define user, but which one(s)?

Here is working example of tomcat-users.xml:

<tomcat-users>
<user username="manager-gui" password="changeit" roles="manager-gui"/>
<user username="manager-script" password="changeit" roles="manager-script"/>

Git on Windows: MSysGit

I have started to use Git today. I read a lot of discussions that there is no good tool for Windows platform. After forethought I have decided to used TortoiseGit. I also feared of difficult work related with Git as a lot of articles mentioned many instructions. As I already said, I have decided to use TortoiseGit, because I'm used to work with TortoiseSvn, but for start, MSysGit is enought. So this article is about MSysGit, next will be about TortoiseGit.

How to start with MSysgit on local machine?
Download and install Git for WindowsCreate source code directory for your git appRight click the directory at your favorite file browser. Menu should contain item "Git init here". It initializes chosen directory to be git-abled :-)It was your first usage of Git.

Commit data to local Git repository

Now, you can add any file, your first source code, to created directory. If you are prepared to commit any changes to your local git repository, follow next instructions.
Right-click th…