Tuning queries with MS SQLServer

While writing this simple query using NHibernate API

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

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

you may notice a strange behavior of NHibernate 2.0. The code shown above generates this two SQL statements:

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 Size is different in the two statements and this cause SqlServer to generate two different execution plans. If you need the best performace you should avoid this behaviour. If you send to the database some queries using the same parameter (type & size) SqlServer use his internal cache. You have already pay for it so why not to use it ?

If you add this line in your NHibernate .config file

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

and write this code the database engine will use his internal cache:

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

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

This code is better cause the parameters Size 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 ? With 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. With this latest improvement you remove a CONVER_IMPLICIT operation from your query execution plan. This cast operation cost something so if you don’t need it why to remove it ?

The things you should care about are:

1. the length of the parameter must always be the correct one in order to reuse the cached execution plans
2. the type ot the parameter must be the same of the table's column