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

Question

I need to optimize a query created using a save (insert) request for a domain object. I configured NHibernate using Fluent NHibernate.

Here is the query created by NHibernate during the insertion of the user's response to the survey:

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 you look at the input parameters for IpAddress and CountryCode , you will notice that NHibernate uses nvarchar(4000) . The problem is that nvarchar(4000) much larger than I need for IpAddress or CountryCode , and because of the high traffic requirements and For the hosting I need to optimize the database for memory usage.

Here's the automatic mapping of Fluent NHibernate overrides for these columns:

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

This is not the only place where I see the unnecessary nvarchar (4000).

How to control the use of NHibernate nvarchar(4000) to represent strings?

How do I modify this insert to use input parameters of the correct size?

Answer 1

Set Type to NHibernateUtil.AnsiString using Length instead of using CustomSqlType.

Answer 2

This issue can cause a huge query performance problem if it causes SQL Server to scan the table instead of using the index. We use varchar throughout our database, so I created an agreement 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"); } } 

Answer 3

Well, this is what we have to do, SQLClientDriver ignores the SqlType length SqlType . Thus, we created our own driver class that inherits from SQLClientDriver and overrides the GenerateCommand method ... 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; } 

Answer 4

Here is the job 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 connect it to your configuration

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