Improving Performance of ActiveRecord and NHibernate for Large Queries

First off, let me say that in general I think that NHibernate is fast enough for most use cases. And as always, save optimizations for last and use a profiling tool to tell what actually is taking up the most time instead of guessing what you think is taking up the most time.

With that said, there are occasions when you need to put a little extra thought into improving performance. A web application I'm working executes a database query that can return thousands of rows. I really wasn't pleased with the performance of the application for this use case. It took a longer than I wanted it to take, especially given that this query is run frequently by the application's users. I ran the NHibernate-emitted SQL query inside of SQL Server Management Studio, and the query returned nearly instantly, so that got me thinking.

I busted out the profiler. I'm using JetBrain's dotTrace profiling tool for .NET, which is a great tool. I loaded up my application to the view where I kick off this large query, turned on the profiling, and then let it run. Here are the results:



NHibernate is spending a lot of time determining whether or not it needs to flush changes to the results of my query to the database. In this specific use case however, no modifications will be made to these entities, so this check is just a waste of resources. It took this much time not to actually update the database with anything, but rather just to see if it should being doing any updates.

For those that don't know, NHibernate follows the unit of work pattern. All changes made to objects are not immediately flushed to the database (via INSERTs and UPDATEs.) Instead, NHibernate patiently waits for you to tell it that your session is over (or manually tell it to flush) before it starts updating the database.

There are a couple of ways to stop NHibernate from spending this much time on the flush. You can either evict all the objects that you don't want checked for a flush from the session, or you can just tell the session not to flush when closed. The latter option will not work if you actually make modifications to some other objects while using the same session, but I'm not making any changes to these objects, so that's fine with me. If you're using Castle ActiveRecord like I am, you can add an argument when you create the SessionScope surrounding your query to turn off flushing:

            using (new SessionScope(FlushAction.Never))

            {

                // Large query inside here

            }

Normally, the SessionScope is initialized with FlushAction.Auto, which causes NHibernate to perform the flush check at the end of a session.

As I said before, another option is to evict the objects that came from the query out of the session. In NHibernate, you can use the ISession.Evict() method to perform this action. Since I'm using ActiveRecord, I shy away from dealing with NHibernate directly whenever possible, so that is yet another reason I chose not to go this route.

Now that we've told NHibernate not to flush anything, and therefore not to check for something to flush, the performance has increased. Take a look:



After I made my changes, NHibernate did not perform any flushes during this request.

NHiberate and ActiveRecord are great tools and they make tedious data-driven tasks simple and easy, but it helps to know a little bit about what's going on under the hood. The takeaway from this is that you should look for opportunies to avoid triggering NHibernate's flush mechanism if you know an operation is read-only, especially when you're dealing with lots of entities in the session.

Elegantly Retry Code If There's an Error

Sometimes you want to try to retry code if there's an error. The most applicable situation for this might be when a database is busy or when the network is slow. Here's some sample code:

        public void RetryFiveTimes()

        {

            for (int count = 0; count < 5; count++)

            {

                try

                {

                    CodeThatCouldThrowAnError();

                    break;

                }

                catch (Exception)

                {

                }

            }

        }

This seems like a decent first attempt solution, until you find yourself doing this in multiple places. Plus I just don't like the way this looks at all. Let's try a different approach:

        public void RetryFiveTimes()

        {

            Retry.Times(5).Do(delegate { CodeThatCouldThrowAnError(); });

        }

OK, now I'm happier. Let's take a look at the Retry class:

    public delegate void RetryMethod();

 

    public class Retry

    {

        private int _times;

 

        public static Retry Times(int times)

        {

            Retry retry = new Retry();

            retry._times = times;

            return retry;

        }

 

        public void Do(RetryMethod method)

        {

            for (int count = 0; count < _times; count++)

            {

                try

                {

                    method();

                    break;

                }

                catch (Exception)

                {

                }

            }

        }

    }

This Retry class could probably benefit from some improvements. The first thing that comes into my head is the ability to retry for specific exceptions. So let's improve this code a bit:

    public delegate void RetryMethod();

 

    public class Retry

    {

        private Type _type = null;

        private int _times;

 

        public static Retry Times(int times)

        {

            Retry retry = new Retry();

            retry._times = times;

            return retry;

        }

 

        public Retry When<T>() where T : Exception

        {

            _type = typeof(T);

            return this;

        }

 

        public void Do(RetryMethod method)

        {

            for (int count = 0; count < _times; count++)

            {

                try

                {

                    method();

                    break;

                }

                catch (Exception e)

                {

                    if (_type != null && !_type.IsAssignableFrom(e.GetType()))

                        throw e;

                }

            }

        }

    }

Cool, now we can do this:

        public void RetryFiveTimesWhenTheresATimeoutException()

        {

            Retry.Times(5)

                .When<TimeoutException>()

                .Do(delegate { CodeThatCouldThrowAnError(); });

        }

Of course, we don't always have nice exceptions where the type indicates that it was a timeout error. What if the message of the exception contained the information we needed? We could probably add further facilities for the user to inspect the exception and provide feedback as to whether we should continue. Let's try this:

    public class Retry

    {

        private Predicate<Exception> _shouldRetry;

        private Type _type = null;

        private int _times;

 

        public Retry()

        {

            _shouldRetry = DefaultShouldRetry;

        }

 

        public static Retry Times(int times)

        {

            Retry retry = new Retry();

            retry._times = times;

            return retry;

        }

 

        public Retry When<T>() where T : Exception

        {

            _type = typeof(T);

            return this;

        }

 

        public Retry If(Predicate<Exception> predicate)

        {

            _shouldRetry = predicate;

            return this;

        }

 

        private bool DefaultShouldRetry(Exception e)

        {

            if (_type == null)

                return true;

            if (!_type.IsAssignableFrom(e.GetType()))

                return false;

            return true;

        }

 

        public void Do(RetryMethod method)

        {

            for (int count = 0; count < _times; count++)

            {

                try

                {

                    method();

                    break;

                }

                catch (Exception e)

                {

                    if (!_shouldRetry(e))

                        throw e;

                }

            }

        }

    }

Great, now we can do this type of thing:

        public void RetryFiveTimesWhenTheresAnExceptionWithTimeoutInItsMessage()

        {

            Retry.Times(5)

                .If(delegate(Exception e) { return e.Message.Contains("Timeout"); })

                .Do(delegate { CodeThatCouldThrowAnError(); });

        }

OK, that's good enough for now. We could continue with this forever, but this solution seems pretty flexible. I think this is a pretty good example of a fluent interface as well.

Have Log4Net Send an Email When an Error Occurs

Instead of building up your own error notification system and injecting an email sender, you can easily have Log4Net send you an email when you want to be notified of something.

        public void DoSomethingImportant()

        {

            try

            {

                InternalDoSomethingImportant();

            }

            catch (Exception e)

            {

                _logger.Error("A serious error occured.", e);

            }

        }

Now, instead of passing in an IEmailSender here and calling SendMessage(), try setting this up in your Log4Net configuration:

<?xml version="1.0" encoding="utf-8" ?>

<log4net>

  <appender name="SmtpAppender" type="log4net.Appender.SmtpAppender">

    <threshold value="WARN"/>

    <to value="to@emailaddress.com" />

    <from value="from@emailaddress.com" />

    <subject value="SmtpAppender" />

    <smtpHost value="SmtpHost" />

    <bufferSize value="512" />

    <lossy value="false" />

    <layout type="log4net.Layout.PatternLayout">

      <conversionPattern value="%newline%date [%thread] %-5level %logger [%property{NDC}] - %message%newline%newline%newline" />

    </layout>

  </appender>

  <root>

    <level value="ERROR"/>

  </root>

</log4net>

Of course, you probably will have more configuration than this, but this is the bare minimum if you want to be emailed of errors. Don't forget about the FATAL log level as well. You could very easily change the level of the message that you wanted to be notified of via email.

I've set the appender threshold of the SmtpAppender here to WARN. You can make sure that you don't get any emails of lower priority using this setting in more advanced Log4Net configurations.

Five Essential Development Tools

1. Resharper - Make quick work of refactoring and code generation with this addon to Visual Studio. It costs a bit of money but it's well worth the price. I won't use Visual Studio without this addon.
   
2. TortoiseSVN - You should be using source control. This subversion interface makes managing your working copy of your code easy and painless.
   
3. WinMerge - This is best free diff tool that I have found for Windows.
   
4. Notepad2 - This free text editor is much better than the Notepad application that comes with windows.
   
5. Paint.NET - Every programmer at some point has to engage in some minor graphics manipulation. I mainly use this to change image formats and crop images.