Avoid the N+1 problem in Rails by harnessing your database

The #1 cause of slow apps may be the N+1 problem, where the app queries a database over and over again to get information about a list of items.

To avoid this, learn the powers of your database so you can query it more efficiently.

Say that you have an app to manage meetings. When someone creates a meeting, there is a standard checklist that they must complete.

The standard checklist items, which are the same for every meeting, are stored in the Checklist table. When someone checks off an item for a particular meeting, that action is stored in Checklist Checked Items table.

Here is the database structure and the sample records we’ll use in this example. For the only meeting in the database, the user has completed two of the checklist items.

meetings

id: 1, title: A Meeting

checklist_items

id: 1, title: Book meeting location

id: 2, title: Arrange catering

id: 3, title: Invite people

checklist_checked_items

id: 1, checklist_item_id: 1, meeting_id: 1

id: 2, checklist_item_id: 2, meeting_id: 1

Say you want to retrieve, for a given meeting, the checklist items and whether each has been checked. You want any checked items to appear at the end of the list.

Here is the app-centric way to do that:

Query the checklist items

Loop through the checklist items, and for each, query the checked items to see if it’s been checked for that meeting

Place each of the items, and whether it’s been checked, in an array of hashes

Sort the array of hashes to place checked items last

This is inefficient and presents the N+1 problem, with step 2 producing multiple queries that will only increase in number as your app grows. Furthermore, there is an unnecessary array middleman and a sorting operation on that array.

Here is the database-centric way:

In one query, get the checklist items, whether each is checked, sorted by whether each is checked.

That’s right, one query has replaced many, and the databsae is doing all the work.

The secret lies in Postgres’s CASE function, which lets you insert conditional logic into queries.

Here, we join checklist_items and checklist_checked_items a checked field that shows whether the checklist item is checked for that particular meeting: