Thursday, February 27, 2014

NHibernate + LINQPad

While looking at ways to assess and improve performance some nHibernate queries, I was frustrated with the tools at my disposal.

I will start with the point: I don't have access to nhprof.  It seems to be the gold standard for any nHibernate shop, but them's the breaks.

What I was doing:

It was painful to execute the entire codebase to run one or two queries and view the output sql, so I started writing integration-unit tests for query optimisation.  This was slightly better, but still required a change-compile-run-review process which was annoying.

What was I thinking:

I then remembered I have a personal LINQPad license that I hadn't used in a while and wondered if I could get it working.  I saw this which helped me on my way, but we don't use nHibernate.Linq, so the steps were a bit different.

The outcome was extremely useful however, and now I am free to tinker with my queries a lot more freely.

How I did it:

To start you need to add the references to nHibernate that you require (in my case NHibernate, Iesi.Collections, and FluentNHibernate).  You then add the references to your Domain / Models and mapping assemblies.

The next step is to create a hibernate.cfg.xml file with the appropriate configuration for your database.  Make sure you set show_sql=true in the configuration so LINQPad can display the generated SQL.

Then you can call the following code

var cfg = new Configuration().Configure(@"D:\linqpad\ahs\hibernate.cfg.xml");
var factory = Fluently.Configure(cfg)
                .Mappings(m =>

using (var session = factory.OpenSession()){
    var site = session.QueryOver()
and viola, you can now tinker with queries as you will, with immediate feedback on the generated SQL and execution time.

You can then save this as a query, or save the assembly references/using statements as a snippet to get you up and running quickly for new queries. 


This method only works with pre-compiled entity mappings, so if you intend to improve performance at the entity mapping layer you still need to do this through your application and export the assemblies for LINQPad to use.


LINQPad allows you to create an 'application config' file that is used when your inner assemblies require web/app.config sections.  Run the code:
to find the location of the file, and if it does not exist create it.  Note that unlike most .NET apps, this is not the LINQPad.exe.config, but LINQPad.config.  Enter any configuration you need into this file.  This can include the nHibernate config  instead of the separate file (but limits configuration flexibility).

This allows you to configure things like nHibernate 2nd level cache instances, such as memcache.  As long as you include the necessary libraries in the query references, and the configuration in the linqpad.config file this will work and provide even greater flexibility for performance analysis and testing.


So there you go, a "poor man's" guide to nHibernate performance analysis, thanks to the ever awesome LINQPad.

No comments:

Post a Comment