Solving The N+1 Problem In PHP
Last updated on 2015-03-19
About the Book
The web page was taking three hours to load.
Let that sink in for a moment: three hours.
The code backing the page was a reporting script written in PHP, attempting to build 40,000 entity objects from 2 million database rows. Indeed, sometimes the page never loaded at all. At least the client could tell when it failed, because a big ugly error popped up in the browser window. Unfortunately that was also around the three hour point. The client was understandably frustrated.
It turned out the cause was not that the database was unoptimized. It was not the number of rows being returned. It was not the number of objects being built.No, the cause was the high volume of queries being issued from the reporting script.
When I say "high volume", I mean two hundred thousand and one queries. These 200,000+ queries were used to build each root entity along with each of its five associated collections and entities, all as a result of how the script was assembling the entity objects. No wonder the script was was taking three hours to finish!
After discovering this, I was able to apply the techniques presented in this book to refactor how the entity objects were assembled. Over the course of a few days of refactoring I was able to reduce the load time from 3+ hours to just under 6 minutes. That's a 30x improvement, and while still slow by typical standards, it was much more bearable for the client. (Later refactorings improved the load time even further.)
The causes leading to the terrible performance of that reporting script have a name: The N+1 Problem. The problem goes by various aliases, such as the "1+N problem", "N+1 queries", "SELECT N+1", and so on, but they all refer to the same thing.
While I mention the N+1 problem more than once in Modernizing Legacy Applications in PHP, I did not address a solution directly. This book remedies that. Herein, we will discuss the specifics of the N+1 problem:
- what the N+1 problem is,
- how to diagnose and discover the problem in your application,
- why the problem occurs, and
- how to solve the problem in your codebase using plain old PHP (no ORMs needed!).
If your application code has database performance issues, or if you want to learn how to prevent creating an N+1 problem in your codebase, buy this book today!
About the Author
Table of Contents
-
1. Introduction
- The Slow Reporting Script
- Example Application
-
2. Defining The N+1 Problem
- The Problem By Example
- The Problem Defined
- Performance Implications
-
3. Diagnosing N+1 Problems
- Codebase Examination
- Log/Profile Examination
- Easier Query Examination
-
4. Why Does The N+1 Problem Occur?
- CRUD Is The Problem
- BREAD Is The Answer
-
5. Solving N+1 With A Single Query
- Handling “Many-To-One” Relationships
- Handling “One-To-One” Relationships
- Combining “Many-To-One” and “One-To-One”
- Handling “To-Many” Relationships
- Single-Query Fails With “To-Many” Relationships
-
6. Solving N+1 With Query-And-Stitch
- Tradeoffs In Query-And-Stitch
- Handling “One-To-Many” Relationships
- Handling “Many-To-Many” Relationships
- Handling “Many-To-One” Relationships
- Handling “One-To-One” Relationships
- Performance Implications
-
7. Refactoring The Codebase To Remove N+1 Problems
- Prerequisites
- The Process
- N+1 In A Single Location
- N+1 Across Separated Locations
- Test, Commit, Push, Notify QA
- Do … While
-
8. Common Questions
- Can I Combine Single-Query With Query-And-Stitch?
-
Can I Use Sub-Selects Instead Of The
IN()Operator? - Should I Use A Transaction With Query-And-Stitch?
- Can I Just Use An ORM?
-
Appendix
- SQL Schema
- PHP Classes
- Colophon
- About the Author
The Leanpub 45-day 100% Happiness Guarantee
Within 45 days of purchase you can get a 100% refund on any Leanpub purchase, in two clicks.
See full terms...
