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?

score 4 · Accepted Answer · answered Feb 21 '12 at 10:02

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

Thanks, that makes a lot of sense. Our system uses fluent configuration so I've decided to add a #if DEBUG to determine whether to add the .ShowSql. Can't think of a more elegant way of doing it. — kasey, Feb 21 '12 at 10:45
cremor answer is correct: show_sql just log to stdout, it is not the logger way. — Felice Pollano, Feb 21 '12 at 14:13
In addition, writing to stdout in multi-threaded application may create race condition issues: http://stackoverflow.com/questions/12638810/nhibernate-race-condition-when-loading-entity — Vitaliy, Dec 24 '15 at 12:16

score 3 · Answer 2 · answered Feb 21 '12 at 14:05

3

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"? ;-)

answered Feb 21 '12 at 14:05

cremor

6,669
1
29
72

Thanks! I was logging to file first and then decided to log to console, so must not have realised the original config was no longer needed. – kasey Feb 29 '12 at 16:46

score 0 · Answer 3 · answered Feb 21 '12 at 14:12

I had my application performance slowed in more than 10 times using default logging configuration with .ShowSql() turned on. So be aware of it.

Also fluent nhibernate supports configuratin from the configuration file - in my case #if DEBUG wasn't a solution, as .ShowSql() slows down working with the application while testing so I decided to keep it off unless I need it.

Will nHibernate ShowSql have any effect on a live system?

3 Answers3