I've recently come across an interesting performance problem using SQL server and want to see whether anyone out there can help.

We have a large table (> 1,000,000 rows) which is mapped to an NHibernate entity and we need to locate the entity by a property which maps to a varchar database column.

We have an index on the column, so we expected that this index would be used by the execution planner...and it is, however rather than doing an index seek it's doing an index scan. This had me stumped for some time until I came across the following post:

http://sqljunkies.com/WebLog/sqlbi/arch ... 24249.aspx

This points out that if a column is of type varchar and an index is created (also varchar, obviously) then if a query is executed against this column but the value passed is an *nvarchar* then an index *scan* will be performed (rather than a seek) so that the values in the index can be converted to nvarchar before the comparison is performed, causing a pretty serious performance degradation.

The real issue here is that NHibernate executes it's queries using sp_executesql and all string parameters are passed as nvarchar, which triggers the above performance problem.

Obviously we *could* change the column data to nvarchar, but this really isn't what we want in our DB schema.

What I really want to know is whether there's some way that the parameter type passed to sp_executesql can be explicitly set to varchar.

Hope that's clear and (more importantly) that someone can help!

Cheers,

Symon.

You could try using type="AnsiString" for properties mapped to varchar columns.

Hey Sergey,

Nope - that didn't seem to work. The SQL statement is still using nvarchar:

eg:


Whatever creates the query seems to automatically assume that the parameter type to sp_executesql is nvarchar.

Any other suggestions?

Cheers,

Symon.

I've wondered about this too. This can have a significant impact on performance.

After a bit of additional research it appears that the sp_executesql stored proc is being called automatically by the SqlCommand's class's implementation of IDbCommand.ExecuteReader(), and it's getting the parameter datatypes from the parameters in the Parameters collection.

I'm not yet clear on whether this is a problem where NHibernate is failing to set the IDbParameter.DBType to DBType.AnsiString or whether it's a problem with the SqlCommand not interpreting this type correctly.

If anyone else has any input it'd be appreciated.

Cheers,

Symon.

Ok, this is essentially my own fault, so I'll document my findings so others don't get caught:

I had assumed that parameter type used in the SQL would be assigned by IQuery.SetParameter based on the entity property that was the target of the HQL clause in which the parameter was used.

The type seems to be guessed directly according to the type provided to IQuery.SetParameter, so if a System.String value is passed to IQuery.SetParameter will automatically be identified as DbType.String.

If, however, you use IQuery.SetAnsiString to explicitly specify the parameter as a varchar things work as expected, so my problem is solved.

I would be nice if NH could try to set the parameter type according to the target entity property...is this within the realms of possiblity? If so I'll create a JIRA issue.

Cheers,

Symon.

It's not really possible right now because it would require us to have a good HQL parser in NH which we currently don't. H3.2 does such a thing however (with their ANTLR-based parser), so it is possible in theory.

Thank Sergey,

Until we've caught up I'll use IQuery.SetAnsiString. :)

Cheers,

Symon.

Hi,

I run in the same performance problem, what I dit, and it worked; was to change the type from String to AnsiString in the XML of the involved tables. Remember that the mappings are embeded in the VS .NET project, so it is necessary to rebuild the project/solution, to make the changes active.

No other changes to code were necessary.

My current development environment is:

V Studio 2003
C#
NHibernate 1.0.2
SQL Server 2000

I hope this helps

DiegoL