Jonathan George's Blog

Optimising an ASP.NET MVC web site, part 2: Database and NHibernate

Posted in Technical Stuff by Jonathan on October 3, 2009

Note: This was originally posted on my old blog at the EMC Consulting Blogs site.

This is part 2 in a series of posts on optimisation work we carried out on my current project – an ASP.NET MVC web site built using S#arp Architecture, NHibernate, the Spark view engine and Solr. Please see “Optimising an ASP.NET MVC web site part 1 – Introduction” for the background.

Whilst there are many different things you can do to a web application to kill performance, having a badly implemented database – or a well implemented database that you access in a bad way – has got to be right up there with the best. We therefore made the database our first port of call, and started off with a pretty simple approach – fire up  SQL Server Profiler, start a new trace and see what the pain points are in each database.

Using SQL Server Profiler to find data access mistakes

As I mentioned in the introduction, the goal of our optimisation wasn’t to do everything, just to get the main things we thought were causing problems before we began our load testing. If you use the TSQL_Duration template in SQL Profiler for your trace, you can hit your site and quickly see what the most expensive queries are.

Sample TSQL_Duration Trace

The screenshot above shows the results of repeatedly executing three different stored procedures in a random order. As you can see, the results are organised in ascending order of cost, with the interesting column being “Duration” – which shows the execution time in milliseonds.

When Howard first ran this against some of our landing and search result pages he quickly noticed a large nugget of smelliness at the bottom of the list, in the form of repeated calls to a stored procedure that was taking a whopping 400ms to execute. Whilst 400ms is not a long time – I’m sure it’s taken longer than that for you to read this sentence so far – when you call it 20 times you’ve suddenly spent 8 seconds just on database calls before any other page activity.

Digging into this identified three separate, but related issues.

1. One of our NHibernate mappings included a view based on a recursive query

Of course, this was something we’d told NHibernate to do. Our entities are organised hierarchically, and an entity can appear in multiple positions in the hierarchy. When we pass entity data to Solr for indexing, we work out the full list of paths for an entity and pass that through (see item 3 for more details.) This was done by creating a CTE to generate the list of hierarchy paths for each entity.

For the uninitiated, a Common Table Expression (or CTE) is a T-SQL construct that (amongst other things) enables you to execute recursive queries. They are very handy when dealing with hierarchical datasets, but aren’t the most efficient queries you can execute. Some time after the initial creation of the view, we’d realised that it would be useful to have the data it contained as part of our entity so we added a mapping into our NHibernate configuration. This meant that accessing that data would cause NHibernate to go off and execute a SELECT statement which included a join from the main entity table to the view. This query took in the region of 400ms.

We have two main types of page on the site landing pages and search/browse pages. The landing pages were causing this query to be executed 13 times and the browse pages were causing it to be executed 20 times, so it’s no wonder that performance had dropped. Whilst the view was never intended for use in this way, the requirement to have the data as part of our entity was still valid.

The simple solution to the problem was essentially to materialize the view. SQL Server can do this by turning the view into an indexed view – adding a unique clustered index to it does this. However, this approach isn’t applicable when the SELECT statement for the view uses a CTE, so we went with a more basic approach – since our product catalogue is actually managed in the back office and populated via ETL, we replaced the view with a table (complete with all the appropriate indexes) and tweaked the ETL to populate this table at the same time as all the rest.

For the pages in question, load time dropped from around 8 seconds to under 2, at which point we all got quite excited. However, this wasn’t solely to do with the materialisation of the view, as the investigation also showed up that…

2. Everything was being lazy loaded

By default, NHibernate uses lazy loading across the board. Depending on your object model and how it is used, this can lead to massive inefficiences. The classic example is the "SELECT N+1” anti-pattern, in which you retrieve an entity from a repository then iterate over a collection on that entity. If you’ve left NHibernate lazy loading the values, then this results in a SELECT statement being issued for every iteration of the loop. Have a look at this page on the NHibernate Profiler site for more information.

Howard spent some time using the NHibernate profiler to better understand what our app was doing, and made a number of tweaks to our mapping configuration to apply eager loading where it made sense. This provided another significant improvement, dramatically reducing the number of database calls made by the application.

3. An architectural rule imposed for performance reasons had been broken

We’d made an architectural decision to drive the search and browse functions on our website exclusively from Solr. We did this because it gives us more options for scalability. Since all the data in question comes from a single database, pulling the data directly out of that database would mean that as website load increases then so does the load on the database. The problem with that is that it’s difficult to scale a single database out – you can throw CPUs and RAM at your database server, but you’re always going to hit a limit, at which point you face some architectural challenges. This kind of basic architecture is shown in the following diagram:

Simple architecture

Even though the main entity database is static, meaning that it would be possible to have a number of replicas of this database (potentially even one per webhead), this would require architectural change and would bring with it a new set of issues around data consistency. By pushing that load onto Solr, which has a far more straightforward scale-out story, we can grow far further without requiring a redesign. Solr basically stores a flat set of data in a form optimised for searching, and provides access via a web service. This means it is straightforward to have multiple instances of Solr running behind a load balancer. Solr makes this kind of setup even easier, as it supports a master-slave configuration as shown in the following diagram (I apologise now for the proliferation of connecting lines – I’m more of a reluctant architect than a Powerpoint architect):

With solr tier

In this example, the web tier will still talk direct to the data tier for some tasks – it’s unavoidable. However, we introduce the search tier which consists of a set of load balanced search servers, each of which contains an identical copy of the search index. In order to build that index, we push data from the database into the Solr master server, and the Solr master server indexes it and pushes the result out to each slave. If you can see past the nasty mess of lines, it should be obvious that as load grows, adding more webheads and/or Solr slaves is a relatively trivial operation.

However, you can have the best intentions in the world when you design your solution, but if you then go on to break the rules then it’s not going to happen. In our case, the code had ended up in a state where for each result retrieved from a Solr search, a database query would be made. Not only that, but the query in question was the horribly expensive one I mentioned in point 1.

This will no doubt cause some brow-wrinkling activity if you’re paying attention, as I mentioned that the original intended purpose of the view being used for the expensive query was to push data into Solr – so why, if the data was already in there, would we be accessing it again from the database once we’d pulled it out of Solr?

I can think of a number of good explanations, the best of which might be “My name’s Jon, and I’m a terrible programmer”. The day I get it right 100% of the time is quite probably the day I get to decide which of my Ferrari’s I drive to work, as well as the day that my projects no longer require testers, and I doubt that will ever happen. Maybe I just missed the Ballmer Peak on that day, but whatever happened, I’m just happy when the mistakes I make are as easy to identify and fix as this one was.

Using the SQL Server Database Engine Tuning Advisor

In tandem with this, we also made use of the SQL Server Database Engine Tuning Advisor. This is the next stage of evolution for the Index Tuning Wizard that (I believe) first appeared in SQL Server 2000. The MSDN page for it is here; the short (aka .NET developer) version is that you put a trace file into one end, and out of the other comes a variety of changes you can make to your database to make it go faster.

In order to generate the input, you use SQL Server Profiler with the “Tuning” template. Once this is running, and saving the trace to a file, you need to generate a “typical” load against your application. There are various ways to do this, ranging from fully manual to fully automated. We were fortunate on our project that we had a complete set of Selenium tests for the site and a way of running them as part of the build. I’m hoping that Tom, the consultant responsible for this, will start blogging about it at some point as it’s really interesting stuff. It meant that to generate our trace, all we had to do was start the profiler, kick of the test suite and go for a beer. The next morning, we pushed the resultant file through the tuning advisor and received our recommendations out of the other end. There was little we disagreed with, and most of the recommendations were subsequently applied to the database.

The rest?

You may have noticed that the main focus in this post has been around the way the application accessed the data, rather than the way the database was built. Over the duration of the project, and in addition to the fantastic data architect we had in the team, we’ve had guest appearances by JRJ, Jamie and Simon, so it shouldn’t come as a massive surprise that the database works well. EMC Consulting is rightly proud of the database guys we have on the team and whilst I’m sure that there are many further tweaks that could be made to our databases to add further go-faster stripes, they aren’t needed at the moment.  Optimisation is one of many things that doesn’t benefit by having the word “premature” put in front of it – it’s basically another form of  YAGNI. So, until testing proved otherwise, we were happy to stop at this point and move on to something else.

In the next exciting epsiode of “Optimising an ASP.NET MVC web site”, we look at a pattern for applying application layer caching. Don’t touch that dial!

Please let me know what you think of my blog posts, either by commenting, dropping me an email, or via Twitter.


Optimising an ASP.NET MVC web site part 1 – Introduction

Posted in Technical Stuff by Jonathan on October 3, 2009

Note: This was originally posted on my old blog at the EMC Consulting Blogs site.

One of the things I’ve been involved in over the past couple of months is performance tuning work for my current project (now live at One of my EMC Consulting colleagues, Marcin Kaluza, has recently started posting on this subject and I’ve been encouraged by Howard to post some “war stories” of the kind of things I’ve encountered whilst doing this on projects, starting with the most recent.

So first, some background. It’s an public facing website project, and is based on Billy McCafferty’s excellent S#arp Architecture – which means it’s ASP.NET MVC with NHibernate talking to SQL Server 2008 databases. We’re using the Spark View Engine instead of the out of the box one, and the site uses N2 CMS to provide content management capabilities (James posted a while back on the reasons for choosing N2.) Finally, we use Solr to provide our search functionality, integrated using Solrnet. I joined the team a few months into the project, by which point they had laid a firm foundation and were about to be abandoned for 6 weeks by their technical lead who had inconsiderately booked his wedding and an extended honeymoon right in the middle of the project.

When the project was set up it was done so in strictly in accordance with agile principles. A small team was given a fixed date for go-live and the directive to spend the client’s money as if it were their own. One of the first things that happened was the adoption of a number of principles from the excellent 37signals e-book “Getting  Real”. A product backlog was assembled, and then – in accordance with the “build less” maxim – divided into “core” and “non-core” user stories. The core stories were what was essential for go live – things the client couldn’t live without, such as basic search and content management. The non-core stories are things that might enhance the site but aren’t essential – for example, advanced search features such as faceted navigation.

The absolute focus the team maintained on the core functionality and target delivery date has made this one of the best and most successful agile projects I’ve worked on – we reached our go live date on budget and were able to substantially over deliver on functionality. Whilst the site is relatively basic compared to some I’ve worked on, it stands out amongst its peers and provides a great platform for new functionality to be built on.

However, now I’ve extolled the virtues of the approach that was taken, I should talk about the performance optimisation and testing work we did. Since I have some experience from previous projects, I took on the task of testing the site to make sure it could handle an acceptable level of load without falling over in an embarrassing heap. However, before I started on that, we did some optimisation work on the site.

The aim was to hit the major pain points, since we knew performance had degraded over the previous few sprints. Once this was done, we could run some load testing and perform further tuning and optimisation work as required. I was originally intending to write a single post covering the optimisation process, then follow that up with one about the load testing process. However, that resulted in a rather lengthy post, so I’m splitting it up into several parts that I will post over the next week or two:

In addition, I’ve already covered the work we did to correctly configure IIS in my post How to improve your YSlow score under IIS7.

I hope you find these posts interesting – please let me know what you think by leaving a comment.