nhibernate query performance

Question

I decided to test nhibernate on an existing application (replacing linq to sql) and I'm seeing abysmal results so far. Hopefully someone here can point me in the right direction.

My config:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
        <property name="dialect">NHibernate.Dialect.MsSql2000Dialect</property>
        <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
        <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
        <property name="connection.connection_string">{my connection string here}</property>
        <property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
        <property name="adonet.batch_size">10</property>
        <property name="show_sql">false</property>           
        <property name="use_outer_join">true</property>
        <property name="command_timeout">60</property>
        <property name="query.substitutions">false</property>            
    </session-factory>
</hibernate-configuration>

The domain class:

public class Code
{
    public virtual Guid CodeId { get; set; }
    public virtual string CodeValue { get; set; }
    public virtual Guid EntryId { get; set; }
}

The mapping xml:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="SweepstakesWeb" namespace="SweepstakesWeb.Domain">
    <class name="Code" table="Codes">
        <id name="CodeId">
            <generator class="guid.comb"/>
        </id>
        <property name="CodeValue" length="20" />
        <property name="EntryId" />
    </class>
</hibernate-mapping>

When I run the test, I'm seeing this:

For the following query:

SELECT this_.CodeId as CodeId0_0_, this_.CodeValue as CodeValue0_0_, this_.EntryId as EntryId0_0_ FROM Codes this_ WHERE this_.CodeValue = N'3734872FVD'

I get:

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Nested Loops(Inner Join, OUTER REFERENCES:([this_].[CodeID]) OPTIMIZED)
       |--Index Scan(OBJECT:([Codes].[ix_codes_code] AS [this_]),  WHERE:(CONVERT_IMPLICIT(nvarchar(20),[Codes].[CodeValue] as [this_].[CodeValue],0)=N'3734872FVD'))
       |--Clustered Index Seek(OBJECT:([Codes].[PK__Codes__3493CFA7] AS [this_]), SEEK:([this_].[CodeID]=[Codes].[CodeID] as [this_].[CodeID]) LOOKUP ORDERED FORWARD)

If I manually change the parameter to varchar (removing the N prefix) like so:

SELECT this_.CodeId as CodeId0_0_, this_.CodeValue as CodeValue0_0_, this_.EntryId as EntryId0_0_ FROM Codes this_ WHERE this_.CodeValue = '3734872FVD'

I get:

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Nested Loops(Inner Join, OUTER REFERENCES:([this_].[CodeID]) OPTIMIZED)
       |--Index Seek(OBJECT:([Codes].[ix_codes_code] AS [this_]), SEEK:([this_].[CodeValue]='3734872FVD') ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([Codes].[PK__Codes__3493CFA7] AS [this_]), SEEK:([this_].[CodeID]=[Codes].[CodeID] as [this_].[CodeID]) LOOKUP ORDERED FORWARD)

Note that with varchar parameter values instead of nvarchar, I get an index seek. With nvarchar, I get an index scan. The column type is varchar(20). Apparently, there's an implicit conversion going on that is causing a full scan instead of a seek.

1. Why does the implicit conversion require a scan instead of a seek?
2. Why does nhibernate use nvarchar by default, and how can I tell it to use varchar?

Answer 1

Darn. A few minutes after I posted, I found the answer:

http://community.devpinoy.org/blogs/joeycalisay/archive/2007/05/11/nhibernate-unicodes-and-index.aspx