http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Ayende, 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 http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment13http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment13Mon, 04 May 2009 07:18:33 GMTJimmy, Yes http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment12http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment12Sun, 03 May 2009 06:09:29 GMTAyende, NHibernate "prepared" also can be used in SQLite backend? http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment11http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment11Sun, 03 May 2009 02:27:24 GMTThere is a better explanation of the issue here [nhforge.org/.../...-queries-with-ms-sqlserver.aspx](http://nhforge.org/wikis/howtonh/tuning-queries-with-ms-sqlserver.aspx)http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment10http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment10Sun, 03 May 2009 01:38:20 GMTconfigurator, 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; http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment9http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment9Sat, 02 May 2009 17:25:30 GMTSurely there's a way to set that as default on the session factory or something? http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment8http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment8Sat, 02 May 2009 16:39:03 GMTYou have a small typo: whether or not, not whatever or not. http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment7http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment7Sat, 02 May 2009 16:08:00 GMTCan it not default to true for the databases where we know it will perform better, a.k.a. sql server.? http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment6http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment6Sat, 02 May 2009 15:48:54 GMTCertainly - reasonable advice, start with the easiest thing and fine tune as needed - thanks, will try that route. http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment5http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment5Sat, 02 May 2009 14:18:44 GMTMatt, 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. http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment4http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment4Sat, 02 May 2009 11:10:28 GMTWith 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? http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment3http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment3Sat, 02 May 2009 11:06:37 GMTScott, 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. http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment2http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment2Sat, 02 May 2009 10:10:04 GMTWhy this is not the default behaviour? http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment1http://ayende.com/3991/nhibernate-the-database-query-cache-and-parameter-sizes#comment1Sat, 02 May 2009 08:44:55 GMT