Yesterday's news

This post is a follow up to my previous post, digesting the facts and opinions on the infamous NHibernate parameter sizes controversy. I have also updated the ending of the original post, for those archeologists of the future who will research this topic.

Only two days after my original post, and four days after Daniel Auger's new thread on NH user group, on 10/30, Carsten Hess came back with a statement that turned the story on its head. According to his findings, execution plans generated by sp_prepexec ARE actually global and not per connection (unlike he said before), so having prepare_sql=true does not cause query cache pollution, at least not in NH 2.1 (apparently NH 2.0 may still have some issues since some parametrization defects were not fixed until 2.1). Among other things this means we no longer have to override GenerateCommand in SqlClientDriver to always call SetParameterSizes, as that's what NH 2.1 does by default, with prepare_sql set to true. This also means Fabio and Ayende were right in their downplaying of the issue, even though I still think they did a bad job communicating their position to the public (something I can't help but attribute to cultural differences).

That said, if your DBA requires your app to be SQL Profiler-compliant (and he has every right to demand that) you may have no other choice but set prepare_sql=false. In that case the following hack will make NH use fixed parameter sizes while keeping NH queries visible in SQL Profiler:

1. set prepare_sql=false in NH settings
2. Create you own class inheriting from SqlClientDriver and override GenerateCommand to always call SetParameterSizes:

   using System.Data;
   using NHibernate.Driver;
   using NHibernate.SqlCommand;
   using NHibernate.SqlTypes;
   
   namespace XXX.YYY.ZZZ
   {
       public class CustomSqlClientDriver : SqlClientDriver
       {
           public override IDbCommand GenerateCommand(CommandType type, SqlString sqlString, SqlType[] parameterTypes)
           {
               var command = base.GenerateCommand(type, sqlString, parameterTypes);
               SetParameterSizes(command.Parameters, parameterTypes);
               return command;
           }
       }
   }

3. set connection.driver_class to assembly-qualified name of your custom driver implementation
4. (optional) add type="AnsiString(LENGTH)" to mappings of string properties to make NH use varchar(LENGTH) instead of nvarchar(4000) for most basic queries
5. Keep an eye on more complex queries and be ready to use the overload of SetParameter that takes TypeFactory.GetAnsiStringType(LENGTH)

To conclude the post, here are the lessons I learned from this story:

• If you think you've found NH 2.1 does something stupid that will kill your production server you're most likely wrong. NHibernate 2.1 is a mature enterprise-ready ORM with most major wrinkles already ironed out.
• If your customer community thinks your product has a bug and you know it does not, they will do their best to fix it anyway, unless you take time to explain there is no bug.
• In serious organizations the DBAs are a big power, better have them as your friends.
• Blogging about programming controversies is not the best way to drive traffic to your website. Breaking news work better and are much easier to make.

Tags: