Ayende @ Rahien

It's a girl

NHibernate, the database query cache and parameter sizes

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")
	.SetString("title","hello")
	.List();

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

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.

Comments

Scott
05/02/2009 08:44 AM by
Scott

Why this is not the default behaviour?

Ayende Rahien
05/02/2009 10:10 AM by
Ayende Rahien

Scott,

Because there are implications of preparing queries in certain databases.

For example, IIRC, MySQL will actually perform slower in some scenarios, because it has better cache semantics.

matt
05/02/2009 11:06 AM by
matt

With this post and the series of posts before, it seems like there is a lot of power locked away in the XML config options in NHibernate. You've mentioned Fluent NHibernate in the past - is any of this stuff possible using that framework instead of XML config? (not that it's bad, just wondering) What do you recommend someone just starting ?with NHibernate?

Ayende Rahien
05/02/2009 11:10 AM by
Ayende Rahien

Matt,

To be frank, I don't know.

I usually use FN as a drafting board, and move to the XML when I need more than that.

matt
05/02/2009 02:18 PM by
matt

Certainly - reasonable advice, start with the easiest thing and fine tune as needed - thanks, will try that route.

Josh
05/02/2009 03:48 PM by
Josh

Can it not default to true for the databases where we know it will perform better, a.k.a. sql server.?

configurator
05/02/2009 04:08 PM by
configurator

You have a small typo: whether or not, not whatever or not.

Neil Mosafi
05/02/2009 04:39 PM by
Neil Mosafi

Surely there's a way to set that as default on the session factory or something?

Ayende Rahien
05/02/2009 05:25 PM by
Ayende Rahien

configurator,

thanks fixed

josh,

because there are costs associated with it that you have to understand.

For example, consider prepared statements with dynamic-update or dynamic-insert.

The DB can sometimes generate a better query plan for different parameters as well.

Neil,

Yes,

cfg.Properties[Environment.UsePreparedStatements] = true;

Jimmy Chan
05/03/2009 02:27 AM by
Jimmy Chan

Ayende,

NHibernate "prepared" also can be used in SQLite backend?

Carsten Hess
05/04/2009 07:18 AM by
Carsten Hess

Ayende,

Unfortunately there is a "catch" to it and setting "prepare_sql" to true is not truly a good solution... I'l explain:

Just a little background on executionplans: Executionplans takes time to create and fills up the DB memory, leaving less space for cached data. Therefore - what we want is the DB to reuse executionplans GLOBALLY - across all connections - old and new, as much as possible. The way to do that is to use "parameterized statements" (not prepared statements) where all parameters to the query are defined as variables of a certain type. And its true, in this respect SQL server treats a varchar(5) and a varchar(11) as two different types and creates two different executionplans.

In this sense, setting "prepare_sql" to true, kind of helps, not because NHibernate calls Prepare() on the underlying IDBCommand, but because it sets the length on the IDBCommands to a fixed size - which is what makes the ADO.NET layer create parameterized statements automatically.

The sideeffect of calling Prepare() is that a socalled "prepared statement" is created in the DB. This is essentially a statement that is precompiled in the DB and gives back a handle so future calls on THE SAME CONNECTION can be made just referencing this handle and not sending the entire sql again. If all calls to the DB were made on the same connection its true that the executionplans would be reused, but as we know - connections are drawn from the connectionpool as needed and handed back in a random manner. The prepared statements on the connection are un-prepared everytime a new session is created and the connection is soft-initialized.

So - personally I have made some local changes (which Fabio by the way is not interested in) to NHibernate (SQLClientDriver.cs) so SetParameterSizes() is ALWAYS called, but I'm leaving "prepare_sql" set to false. This gives me exactly the behaviour I want (verified with SQLProfiler and by looking at the statements in the executionplan cache in SQL server)

The change in SQLClientDriver leads to some other small changes because the fixed sizes that are set on the parameters are not truly the column sizes defined in the mapping (known bug in NHibernate)

Hope my entry can throw some new light on the discussion.

Kind regards

Carsten

Comments have been closed on this topic.