Lately we have been investigating ORM’s (Object Relational Mapping) frameworks for use on a large enterprise level website which we been given the rare opportunity to rebuild from the ground up in Microsoft MVC.

I’ve used Subsonic in the past while working with dashCommerce and more recently on my latest project based on the wonderful Subsonic 3.0. We decided we needed something more robust and proven with a large enterprise level system like ours which currently comprises with hundreds of tables and store procedures all managed by a team of professional DBA’s who do a pretty good job at optimising and tuning the Microsoft SQL 2005 database.

After much deliberation we managed to convince the DBAs and Developer Head to give NHibernate a shot. It seemed to be the most mature fully featured ORM for .NET and rated highly whitin the .NET community as is apparent by the huge number of blog posts about it, so we felt more secure choosing it over the premature Entity Framework.

NHibernate and MVC brought us to reconsider our architecture and after finally getting my head around to the S#harp Architecture I managed to convince the team it was the way to go and gave us a good foundation to build our application on.

After a few weeks we got a really good prototype working. We mapped some of our core tables using FluentNhibernate, wrote some simple LinqToNHibernate queries, religiously followed Test Driven Development and Domain Driven Design until we stumbled across our first major issue.

Queries run by NHibernate were not re-using SQL Server execution plans This was causing us some major performance issues since some queries were taking a few seconds just to generate a plan. We quickly managed to figure out the problem caused viewing the queries using SQL Profiler. NHibernate was passing parameter sizes dynamically based on the length of the value rather than from the mapping which was causing SQL Server to create new execution plans for each unique query.

We found a good explanation and “fix” setting prepare_sql to true in the NHibernate config.

This seemed to fix the parameter size issue but now the queries were being sent using exec sp_prepexec which even had the DBA’s scratching their heads over as it’s documented as a system procedure used to facilitate cursors within SQL Server. The DBAs later informed us that running queries like this causes the execution plans to only be re-used per open connection so for every connection it would still have to create new execution plans.

Carsten Hess wrote a good comment on Ayende’s Blog explaning the issue to which no follow up was made but helped us find and fix the issue. I’ve attached the file for those interested in the fix as to why this isn’t the default behaviour of NHibernate has us perplexed. I've just commenting out an if statement in \Driver\SQLClientDriver.cs found in NHibernate source code thus forcing it to call SetParameterSizes() all the time regardless of the prepare_sql setting alowing us to set it to false so queries are sent normally and excecution plans being issued and re-used correctly.

The whole experience left us feeling weary, had we not discovered the issue beforehand it almost certainly would have brought down our application if we went live with NHibernate. In the end we decided to hold off on implementing the ORM to give it more investigation.

Our journey wasn’t a waste of time as it has brought us to learn about many new skills along the way that has already had an affect on our programming. Test Driven Development, Dependency Injection, Inversion of Control, Domain Driven Design all of which has given us an understanding of some techniques we can use to better develop our system.

Attached: SqlClientDriver.cs

EDIT: Andrei Volkov has posted a good followup in which he explains the history of this issue and an alternative fix to create your own driver. NHibernate parameter sizes controversy