How to Deal with the N + 1 Query Issue

One common type of performance issue that we have run into with our client partners at Devetry is called the “N + 1 query issue”. It’s easiest to understand with an example.

Let’s say you’re building the next big blog platform. You have posts and comments on those posts. The page you’re trying to speed up right now displays all posts with at least 15 comments. One way to figure out which ones have at least 15 comments would be:

1. Get a list of all the posts

2. For every post:

A. Check how many comments it has

B. Throw it out if there are fewer than 15

This triggers a query to get all posts (the 1 in N + 1), and then follows up by sending a ‘how many comments’ query for each post (the N in N + 1). The N + 1 pattern is common because many popular frameworks encourage it, or at least make it easy to do accidentally. It crops up anytime you get a list of Posts (or Pets, Users, Orders — whatever the record we’re retrieving is) that doesn’t have all the information you will need. In this case, you needed to know the number of comments on each post, but you can imagine it might have needed to show a list of all categories on a post.

This case is particularly egregious because you don’t even need the comments—you just need to know how many there are for each post. You didn’t even need all of the posts! You threw away the ones that didn’t have enough comments to make the cut. It’s easy to understand why this could be a performance problem—you’re doing more work than you need to.

However, N + 1 is a problem even if you are using all of the data you’re fetching. Every query has a bit of overhead cost. It is more efficient to make a single large query with all the information you need than to make 100 small ones.

It’s the law that any mention of code performance must include Knuth’s quote: “Premature optimization is the root of all evil”. That is, the fact that you’re running into N + 1 queries doesn’t necessarily mean your app was written wrong. It’s difficult to know in advance where performance bottlenecks will appear. Trying to guess and fix them before they appear might not be the best use of your time and money.

If you find yourself in the happy position of having an app that’s beginning to take off—and performance issues are starting to show themselves—give us a call.

Author: Brian, Senior Software Developer

—

Devetry partners with clients to identify critical needs and build custom software. Understanding client needs comes first. Choosing the right technology comes second. Learn more.