+972 52-548-6969
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
Why this is not the default behaviour?
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.
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?
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.
Certainly - reasonable advice, start with the easiest thing and fine tune as needed - thanks, will try that route.
Can it not default to true for the databases where we know it will perform better, a.k.a. sql server.?
You have a small typo: whether or not, not whatever or not.
Surely there's a way to set that as default on the session factory or something?
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;
There is a better explanation of the issue here
nhforge.org/.../...-queries-with-ms-sqlserver.aspx
Ayende,
NHibernate "prepared" also can be used in SQLite backend?
Jimmy,
Yes
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
Comment preview