Tuning queries with MS SQLServer

Groxx — Fri, 01 Apr 2011 20:43:54 GMT

Current revision posted to How to by Groxx on 01/04/2011 05:43:54 p.m.

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%Term Paper Help AND Research Paper Help'

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 ?Thesis Help AND Essay Help AND Dissertation Help

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:

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

Tuning queries with MS SQLServer

markweee — Wed, 30 Mar 2011 11:25:54 GMT

Revision 6 posted to How to by markweee on 30/03/2011 08:25:54 a.m.