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!
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
About the Author
Paul M. Jones is an internationally recognized PHP expert who has worked as everything from junior developer to VP of Engineering in all kinds of organizations (corporate, military, non-profit, educational, medical, and others). Paul's latest open-source project is the Atlas Persistence Framework for PHP. Among his other accomplishments, Paul is the lead developer on Aura for PHP and Solar Framework, and the creator of the Savant template system. He has authored a series of authoritative benchmarks on dynamic framework performance, and was a founding contributor to the Zend Framework (the DB, DB_Table, and View components). Paul was a founding member of the PHP Framework Interoperability Group, where he shepherded the PSR-1 and PSR-2 recommendations, and was the primary author on the PSR-4 autoloader recommendation. He was also a member of the Zend PHP 5.3 Certification education advisory board. He blogs at paul-m-jones.com. In a previous career, Paul was an operations intelligence specialist for the US Air Force, and enjoys putting .308 holes in targets at 400 yards.
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...
