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:

1. Query the checklist items
2. Loop through the checklist items, and for each, query the checked items to see if it’s been checked for that meeting
3. Place each of the items, and whether it’s been checked, in an array of hashes
4. 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:

1. 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:

Or, using ActiveRecord:

This returns the checklist items and whether they’re checked, with checked items last:

• title: Arrange catering, checked: false
• title: Invite people, checked: true
• title: Book meeting location, checked: true

By understanding Postgres’s CASE function, we were able to replace many queries with one, speeding up our app.

This is just one example of how your database can take the burden and handle it better. Read your database’s documentation to discover more.

Further reading:

• For more PostgreSQL strategies, check out Supercharging ActiveRecord with PostgreSQL, a thought-provoking slide deck.
• Detect N+1 issues in your Rails app with the Bullet gem.
• Full-text search is another area where you can harness cool database features to increase speed. Check out our walkthrough for implementing full-text search in a Rails app.

---

Hired.com brings Rails job offers to you. Free for candidates. Salaries from $75,000 to $250,000. Sign up now!