Will nHibernate ShowSql have any effect on a live system?

Question

I've configured nHibernate to output its SQL statements to the Visual Studio output window using the following configuration code:

var configuration = Fluently.Configure(cfg)
                .Database(
                    MsSqlConfiguration.MsSql2005
                    .ConnectionString(connectionString)
                    .DefaultSchema("dbo")
                    .UseReflectionOptimizer()
                    .AdoNetBatchSize(32)
                    .ShowSql()

and in my Web.config:

<appender name="NHibernateFileLog" type="log4net.Appender.TraceAppender">
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n"  />
  </layout>
</appender>

<logger name="NHibernate.SQL" additivity="false">
  <level value="DEBUG"/>
  <appender-ref ref="NHibernateFileLog"/>
</logger>

Will this have any performance impact on the live system? The log level on the live system is ERROR so I guess that means the logger won't be switched on, but will the ShowSql on my nHibernate configuration still use up resources?

Answer 1

Logging has a significant impact on your performance, however how much you should test in your production/test environment. When using ShowSql(), it will send the SQL to your logger, which will filter it. Normally you would configure the ShowSql flag in you configuration. In that case you can set it to false in your production environment:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
        <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
        <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
        <property name="connection.connection_string">
Server=(local);Database=NHibernateFAQ;Integrated Security=SSPI;
        </property>
<property name="show_sql">false</property>
    </session-factory>
</hibernate-configuration>

configure-log4net-for-use-with-nhibernate

Answer 2

ShowSql() is only needed if you want your SQL outputted to the console without configuring log4net. You don't need (and shouldn't use) ShowSql() if you have a logger configured for "NHibernate.SQL".

PS: I'd recommend adding FormatSql() in a #if DEBUG to generate readable SQL while debugging.

PS2: Why is your TraceAppender named "NHibernate*File*Log"? ;-)