There will be those who disagree with me (hi DBAs!) but ORMs totally rock. Object Relational Mappers have been around for a while now and you may know them by names such as LINQ to SQL, NHibernate and Entity Framework (among others). The idea of ORMs is that all the plumbing between entities in the app and entities in the database can be abstracted away into a managed framework so that data access can become a no-mess, no-fuss affair.
As with many automated ways to build apps, ORMs have their pitfalls and one of the worst – and most common – is the dreaded “n+1” brought about by lazy loading. Here’s how an n+1 condition manifests itself:
- You query the database and get back a bunch of records in a table (this is one query)
- In your app code, you read through each record and refer to one or more attributes which need to be pulled from other tables
- Each record then causes the app to go off and make a heap of other queries in order to retrieve the attributes in the previous point (the n bit)
The lazy loading bit comes about as a result of that first query only returning the entity and not all the other attributes so it has to go back for in the third point (slacker). Think of it like this; let’s take this query:
SELECT * FROM dbo.Products
Now let’s image that every product has a category which is normalised out to another table and you want to display this to users which means that for every record you end up doing this:
SELECT * FROM dbo.Categories WHERE CategoryID = 1
SELECT * FROM dbo.Categories WHERE CategoryID = 2
SELECT * FROM dbo.Categories WHERE CategoryID = 3
And so on and so forth. This happens easily because ORMs are so simple to implement and query without actually seeing what’s going on underneath on the SQL Server. I’ve seen cases where a single page with 20 records on it was making 2,000 – yes, two thousand – queries to the DB. The developer didn’t realise it because it still performed well against a small set of data on a local DB with a single user but change any of those conditions and things are going to get very nasty very quickly.
The problem is identifying an n+1 condition in the first place and there a number of approaches to this. The other day I sent some performance tips over to Red Gate which where then included in their free (yes – free!) eBook titled 50 Ways to Avoid, Find and Fix ASP.NET Performance Issues. The tip I’m talking about here goes like this:
Always profile your ORM database hits with SQL Profiler during development. ORMs get away from you very quickly. Before you know it, you’ve run a query 2000 times in a loop, when you could have retrieved all your data with a single database hit.
SQL Profiler is one way of doing this but another tool that also does a great job of pointing out your database hits is ANTS Performance Profiler. In fact ANTS does a heap of other very useful stuff that gets right under the covers of your .NET app and profiles performance down to a very fine-grained level and it makes it dead simple (a classic Red Gate Software trait). So simple in fact that I thought it was worth adding to my 5 minute wonders series because it’s worth a video (a very quick video), to properly demonstrate how it works: