NHibernate, the database query cache and parameter sizes

time to read 2 min | 246 words

A while ago I took part in a herding code episode where the question was brought up, what would NHibernate’s behavior would be in the following scenario?

s.CreateQuery("from Blog b where b.Title = :title")

s.CreateQuery("from Blog b where b.Title = :title")
	.SetString("title", "hello world")

The actual problem related more to the way NHibernate interacts with the database than to the way NHibernate itself works. The issue was whether or not NHibernate will generate a query that will reuse the same query plan for parameters with different sizes, or whatever it will generate a copy of the query plan for each parameter size.

There are arguments for both sides, but most DBAs would really like to see only a single query plan for all parameter sizes. I didn’t have the answer off the top of my head, but I was pretty sure that NHibernate did the right thing from the point of view of the database.

As it turn out, I was wrong. By default, NHibernate will generate two separate query plans for this query in the database. Sounds bad, right?

Not really, here is the fix, just put the following in your configuration:

<property name='prepare_sql'>true</property>

And you are done, nothing else needs to be done, because NHibernate will now generate consistent query plan for all queries of this type.