How does one make NHibernate stop using nvarchar(4000) for insert parameter strings?

Question

I need to optimize a query that is being produced by a save (insert query) on a domain entity. I've configured NHibernate using Fluent NHibernate.

Here's the query generated by NHibernate during the insertion of a user's response to a poll:

exec sp_executesql N'INSERT INTO dbo.Response (ModifiedDate, IpAddress, CountryCode, 
IsRemoteAddr, PollId) VALUES (@p0, @p1, @p2, @p3, @p4); select SCOPE_IDENTITY()',N'@p0
datetime,@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 bit,@p4 int',
@p0='2001-07-08 03:59:05',@p1=N'127.0.0.1',@p2=N'US',@p3=1,@p4=2

If one looks at the input parameters for IpAddress and CountryCode, one will notice that NHibernate is using nvarchar(4000). The problem is that nvarchar(4000) is far larger than I need for either IpAddress or CountryCode and due to high traffic and hosting requirements I need to optimize the database for memory usage.

Here's the Fluent NHibernate auto-mapping overrides for those columns:

    mapping.Map(x => x.IpAddress).CustomSqlType("varchar(15)");
    mapping.Map(x => x.CountryCode).CustomSqlType("varchar(6)");

This isn't the only place that I see unnecessary nvarchar(4000)'s popping up.

How do I control NHibernate's usage of nvarchar(4000) for string representation?

How do I change this insert statement to use the proper sized input parameters?

Devy

Answer 1

Specify the Type as NHibernateUtil.AnsiString with a Length instead of using a CustomSqlType.

Delectate

Thanks, it works! make sure you are on a newer version of NHibernate and you are using latest Dialect. – Wellington

Answer 2

This issue can cause a huge performance problem in queries if it forces SQL Server to perform a table scan instead of using an index. We use varchar throughout our database so I created a convention to set the type globally:

/// <summary>
/// Convert all string properties to AnsiString (varchar). This does not work with SQL CE.
/// </summary>
public class AnsiStringConvention : IPropertyConventionAcceptance, IPropertyConvention
{
    public void Accept(IAcceptanceCriteria<IPropertyInspector> criteria)
    {
        criteria.Expect(x => x.Property.PropertyType.Equals(typeof(string)));
    }

    public void Apply(IPropertyInstance instance)
    {
        instance.CustomType("AnsiString");
    }

}

Reactive

Answer 3

Okay this is what we have to do, the SQLClientDriver ignores the length property of the SqlType. So we created a our own driverclass inheriting from SQLClientDriver and override the method GenerateCommand...Something like this:

public override IDbCommand GenerateCommand(CommandType type, NHibernate.SqlCommand.SqlString sqlString, SqlType[] parameterTypes)
{
    var dbCommand = base.GenerateCommand(type, sqlString, parameterTypes);
    SetParameterSizes(dbCommand.Parameters, parameterTypes);
    return dbCommand;
}

private static void SetParameterSizes(IDataParameterCollection parameters, SqlType[] parameterTypes)
{
    for (int index = 0; index < parameters.Count; ++index)
        SetVariableLengthParameterSize((IDbDataParameter)parameters[index], parameterTypes[index]);
}

private static void SetVariableLengthParameterSize(IDbDataParameter dbParam, SqlType sqlType)
{
    SetDefaultParameterSize(dbParam, sqlType);
    if (sqlType.LengthDefined && !IsText(dbParam, sqlType) && !IsBlob(dbParam, sqlType))
        dbParam.Size = sqlType.Length;
    if (!sqlType.PrecisionDefined)
        return;
    dbParam.Precision = sqlType.Precision;
    dbParam.Scale = sqlType.Scale;
}

Octaviooctavius

Answer 4

Here is a work around, if you want to replace all nvarchar with varchar

public class Sql2008NoNVarCharDriver : Sql2008ClientDriver
{
    public override void AdjustCommand(IDbCommand command)
    {
        foreach (System.Data.SqlClient.SqlParameter x in command.Parameters)
        {
            if (x.SqlDbType == SqlDbType.NVarChar)
            {
                x.SqlDbType = SqlDbType.VarChar;
            }
        }
        base.AdjustCommand(command);
    }
}

Then plug it into your config

var cfg = Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2008.ConnectionString(connectionString)
            .Driver<Sql2008NoNVarCharDriver>())
            ...

Waterbuck