If I had the time and the expertise, I’d love to make a 60s style health film about the Select N+1 problem.

This problem arises usually when you need to get related data from more than one database table.

Let’s say I have a Customers table with CustomerId as a primary key.  There is an Orders table that uses CustomerId as a foreign key, and there is an OrderLineItems table that uses OrderId as a foreign key.

On my website, let’s say I have a page where I want to show a customer a list of their orders and the details of those orders.  From a pure SQL standpoint, what I want to do (probably) is do one SQL SELECT statement where I join those three tables together using those IDs to do it.  Makes sense, right?

But imagine this.  Imagine I took my CustomerId, did a SELECT in the Orders table to get all the OrderIds tied to that Customer (that’s one SQL statement), then proceeded to execute a SELECT statement against the OrderLineItems table for each individual OrderId that I’d found.  In other words, instead of something like this:

SELECT * FROM Customers C, Orders O, OrderLineItems OL WHERE C.CustomerId = O.CustomerId AND O.OrderId = OL.OrderId

I did something like this:

SELECT OrderId FROM Customers C, Orders O WHERE C.CustomerId = O.CustomerId

SELECT * FROM OrderLineItems WHERE OrderId = 3

SELECT * FROM OrderLineItems WHERE OrderId = 7

SELECT * FROM OrderLineItems WHERE OrderId = 13

And so on.

That second solution is what we call a Select N+1: instead of doing one join, you’re doing a SELECT over and over to get your data out of each “join id” value individually.

Sometimes, people will do this in raw code or SQL operations, but the problem is far more common when you use ORMs that lazy load.  If my ORM lazy loads by default, then in code, when I do something like this:

foreach(var lineItem in myCustomer.Orders[0].LineItems)

My ORM is going to think, “Ok, I need this customer’s orders.  Ok, I’ll hit the database and load that.  Oh, wait, I also need all the line items.  Better go back and get those.”

For the example above, that’s not that bad because we’re just doing one order, but if we wanted to iterate through all the line items in every order (maybe we want to display if a customer has already ordered an item if they’re viewing it on our website), the odds are very good that your ORM will go back and get the line items by way of using Select N+1.

So, how do you make sure this doesn’t happen?

All the solutions to this problem revolve around eagerly fetching the data that you’re going to use rather than your ORM having to “go back and get it.”  Unfortunately, there’s not a one size fits all solution for every situation.  Even in the exact same application, it might be better to solve the Select N+1 one way in one spot, and take care of it a different way in a different spot.

One solution is to turn off lazy loading.  Most of the time, this is not ideal.  Not only does it typically mean worse performance across the board, it also means your ORM can’t do a lot of its automated magic, and you end up having to do a lot of synchronization in your context, etc.  Sometimes, though, it might be useful to disable this at a property level if you find you are always using the data from a property along with its aggregate root.

Another solution is to specify the things you need to eagerly fetch in your query.  This is usually how I deal with this problem.  In my query, I just tell it, “When you get a customer this time, go ahead and load their orders and line items, too.”  I mean, I don’t say that out loud, but I code that into the query.

Another solution may involve how your object model is laid out.  Maybe you have two entities joining together when, in reality, one is actually a component of the other.  Maybe you’re using a lot of joined entities to get something you could get from a simpler object that just represents the data that you need for that operation (typically, these are DTOs).  Maybe you’re querying an aggregate root when you don’t need anything from it and can query the “child” objects directly.

In any case, using an ORM does not free an application team from having to worry about the SQL.  Even if your mappings are great and your object model is awesome, you can still run into Select N+1 without even knowing it.  Check the SQL your ORM is generating and decide if it needs tweaking.