Ayende @ Rahien

Hi!
My name is Ayende Rahien
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

@

Posts: 5,947 | Comments: 44,540

filter by tags archive

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

Why this is not the default behaviour?

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

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

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

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

Josh

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

configurator

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

Neil Mosafi

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

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

Ayende,

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

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

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

No future posts left, oh my!

RECENT SERIES

  1. RavenDB Sharding (2):
    21 May 2015 - Adding a new shard to an existing cluster, the easy way
  2. The RavenDB Comic Strip (2):
    20 May 2015 - Part II – a team in trouble!
  3. Challenge (45):
    28 Apr 2015 - What is the meaning of this change?
  4. Interview question (2):
    30 Mar 2015 - fix the index
  5. Excerpts from the RavenDB Performance team report (20):
    20 Feb 2015 - Optimizing Compare – The circle of life (a post-mortem)
View all series

RECENT COMMENTS

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats