NHibernate, unicodes and index
My boss is optimizing one of our previous projects. He was specifically looking at the queries used by NHibernate and he noticed that the generated sp_executesql queries are using nvarchar parameters. The query was just a filter for just one column (Position) and he noticed that the index for the said column was not being used.
Here is the generate query:
exec sp_executesql N'SELECT this.Id as Id0_, this.MiddleName as MiddleName0_, this.Code as Code0_, this.Position as Position0_, this.LastName as LastName0_, this.FirstName as FirstName0_ FROM Employee this WHERE this.Position = @p0', N'@p0 nvarchar(4000)', @p0 = N'LSDE'
The Position field in the database is a varchar(4) and when he changed the datatype of the parameter of the query to a varchar, the desirable index seek will be performed. Another guy found out about this issue here.
We all know that in .NET, the string type supports unicode so it correctly maps to an nvarchar. In order to tell NHibernate to use varchar for certain fields, you have to use the AnsiString type in your mapping file.
1 <?xml version="1.0" encoding="utf-8" ?>
2 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" namespace="ProjectTracker.Core" assembly="Core">
3 <class name="Employee" table="Employee">
4 <id name="Id">
5 <generator class="native"/>
6 </id>
7 <property name="Code"/>
8 <property name="LastName"/>
9 <property name="FirstName"/>
10 <property name="MiddleName"/>
11 <property name="Position" type="AnsiString"/>
12 </class>
13 </hibernate-mapping>
Another obvious workaround is to have string fields in the database use nvarchar types. I'm not an sql guru but i do know that using unicode entails more space (doubled) for the database but do you all use unicode fields these days?