NHibernate queries & sql server execution plans

10 Mar

In these days I’m working with Andrea on a web application using ASP.NET & SQL Server . You should kwon that Andrea, is a SQL Server MVP, but fortunately is not a T-SQL taliban so he agree without hesitation when I proposed to use NHibernate to build our data access layer. When a tool can help us while building an application why not use it ?

As you can image a skilled DBA like Andrea can rapidly find a bottleneck in any query fired to a database. While writing a simple query using NHibernate API we noticed a strange behavior of NHibernate 2.0 with parameters. Here is the code we wrote:

using (ISession session = factory.OpenSession())
{
	//nvarchar(4)
	session.CreateQuery("from Region r where r.Description like :desc")
		.SetString("desc", "zon%")
		.List();

	//nvarchar(5)
	session.CreateQuery("from Region r where r.Description like :desc")
		.SetString("desc", "zone%")
		.List();
}

This code generates these two sql queries:

exec sp_executesql N'select region0_.RegionId as RegionId0_,
region0_.RegionDescription as RegionDe2_0_
from Region region0_ where
(region0_.RegionDescription like @p0 )
',N'@p0 nvarchar(4)',@p0=N'zon%' exec sp_executesql N'select region0_.RegionId as RegionId0_,
region0_.RegionDescription as RegionDe2_0_
from Region region0_ where
(region0_.RegionDescription like @p0 )
',N'@p0 nvarchar(5)',@p0=N'zone%'

As you can see the parameter type is different in the two statements. This unfortunately cause SqlServer to generate two different exection plans. If you need the best performace you should avoid this behaviour. If you send to the database queries using the same parameter (type & size) SqlServer use his internal cache. You have already pay for it so why not to use it ?

I had the pleasure to discuss with Fabio Maulo about this topic (you can read here. The conversation is in italian). At the end I found this solution:

I edited my hibernate.cfg.xml file adding this node to the xml

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

and then I wrote this code:

using (ISession session = factory.OpenSession())
{

	//nvarchar(10)
	session.CreateQuery("from Region r where r.Description like :desc")
		.SetParameter("desc", "zoneh%", TypeFactory.GetStringType(10))
		.List();

	//nvarchar(10)
	session.CreateQuery("from Region r where r.Description like :desc")
		.SetParameter("desc", "neh%", TypeFactory.GetStringType(10))
		.List();
}

Now with this code NHibernate generates these two sql queries:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@p0 nvarchar(10)',N'select region0_.RegionId as RegionId0_, 
region0_.RegionDescription as RegionDe2_0_ from Region region0_ where
(region0_.RegionDescription like @p0 )
',@p0=N'zoneh%' select @p1 declare @p1 int set @p1=2 exec sp_prepexec @p1 output,N'@p0 nvarchar(10)',N'select region0_.RegionId as RegionId0_,
region0_.RegionDescription as RegionDe2_0_ from Region region0_ where
(region0_.RegionDescription like @p0 )
',@p0=N'neh%' select @p1

This is better cause the parameters length is always the same. Now your database will thank you for this! But wait you can still do something better.

If your database column type is a varchar and not nvarchar you can improve your code using a different SqlDbType. How ? Whit his code:

using (ISession session = factory.OpenSession())
{
	session.CreateQuery("from Region r where r.Description like :desc")
		.SetParameter("desc", "z%", TypeFactory.GetAnsiStringType(15))
		.List();

	session.CreateQuery("from Region r where r.Description like :desc")
		.SetParameter("desc", "za%", TypeFactory.GetAnsiStringType(15))
		.List();
}

Now the parameter type is a varchar and the size is always 15. Whit this latest improvement you remove a CONVER_IMPLICIT operation from your query execution plan (futher details here but in italian ). This cast operation cost something so if you don’t need it why to remove it ?

I hope this will help someone (dev & dba) in the future!

13 Risposte to “NHibernate queries & sql server execution plans”

  1. Fabio Maulo marzo 15, 2009 a 10:22 pm #

    Are you sure about the title of this post ?

    “NHibernate’s parameter problem” ?

    I think the real problem is the way MsSQL-server are choosing plans because others RDBMS (as Firebird) don’t have the same problem.
    Identically situation is for the way MsSQL-server have solved the pagination issue.

    Perhaps somebody should write something to MsSQL team.

    • makka marzo 16, 2009 a 8:27 am #

      I don’t believe this is a real NHibernate’s parameter problem but as we know a limitation of MsSqlServer we should care about it.
      If NHibernate would use Length attribute by-default in MsSql2005Dialect this issue wouldn’t exists.
      Sorry what do you mean by “MsSQL-server have solved the pagination issue” ?

  2. Dario Quintana marzo 17, 2009 a 4:56 am #

    SqlServer to me, has a complicated way to solve some the problems, including this and pagination too. I would be nice to see these problem solved as another RDBMS do.

    • makka marzo 17, 2009 a 10:36 pm #

      These details are a deep dive inside SqlServer internals.
      An ORM should help a mort developer, like me, and hide these details.
      I’m trying to understand why the attributes length & sql-type are not used to create better SqlCommand by SqlClientDriver

  3. Dario Quintana marzo 17, 2009 a 10:57 pm #

    I know are deep dive inside SqlServer internals, doesn’t mean they could make things easier for us, remember the people are developing NHibernate need a little help from the Sql Server people too, and so far they are complicating more and more some features, and we are mort developers too, they can hide some details. Because remember, this is an specific Sql Server problem. But you know, is easier to change an OSS-ORM than this kind of RDBMS.

    BTW, I know, an ORM should help, it that is the reason that prepare_sql will be true in NH 2.1.

  4. SentientPC luglio 27, 2009 a 3:36 am #

    Thanks for the great article, I found this very problem my self. Also, in my case, my data column was defined as varchar(15) and using .SetString(“MyCol”, “MyVal”) created parameter as nvarchar(5) as you described. However, this also caused the query plan to perform an Index Scan rather than Index Seek and added 200ms, higher IO and CPU cost to my query. This was all due to the conversion of nvarchar to varchar.

Trackbacks/Pingbacks

  1. A Fix for nHibernate’s Parameter Problem « Home of the Scary DBA - marzo 11, 2009

    […] systems especially with memory and the procedure cache. However, it does appear that there is a possible solution. That doesn’t eliminate my concerns over nHibernate, but it sure does […]

  2. NHibernate, SQL Server, performance, piani di esecuzione, … - Il blog di Andrea Benedetti - marzo 11, 2009

    […] ha tirato fuori una possibile soluzione che ha prontamente postato sul suo blog inglese (qui) – attendo anche una versione in italiano su UGI […]

  3. NHibernate 2.1.0 beta 2 « TDD developer - giugno 21, 2009

    […] This version contains a fix for bug that cause some problem with MS Sql server execution plans (more details here). […]

  4. Database Myths: Stored Procedures vs. Ad Hoc Queries | Coding Efficiency - agosto 3, 2009

    […] Source: MSDN1, MSDN2, MSDN3, Blog […]

  5. Yesterday's news | What's new in NHibernate 2.1 - ottobre 26, 2009

    […] set to true by default. This affects the way parametrized queries are generated and executed. See this, this and this for the history of the […]

  6. Yesterday's news | NHibernate parameter sizes controversy - ottobre 28, 2009

    […] 03/10 Claudio publishes a post on his blog, in which he explains the issue and the solutions he found so […]

  7. NHibernate and Execution Plans - ottobre 8, 2010

    […] which was causing SQL Server to create new execution plans for each unique query. We found a good explanation and “fix” setting prepare_sql to true in the NHibernate config. This seemed to fix the parameter size issue […]

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger cliccano Mi Piace per questo: