What’s a good simple way to combat the n+1 problem?

I’m trying to better understand performance in PHP. One issue I’m thinking about is the n+1 problem. By n+1 I mean something like this:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>$posts = Posts::getPosts();
foreach($posts as $post) {
$comments = Comments::getComments(array('post_id' => $post->id));
// do something with comments..
}
</code>
<code>$posts = Posts::getPosts(); foreach($posts as $post) { $comments = Comments::getComments(array('post_id' => $post->id)); // do something with comments.. } </code>
$posts = Posts::getPosts();

foreach($posts as $post) {
  $comments = Comments::getComments(array('post_id' => $post->id));
  // do something with comments..
}

It’s quite inefficient as we have to do many queries for every post to get the comments.

Is something like this better? It’s more PHP code but only two queries will be executed:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>$posts = Posts::getPosts();
// get the ids into an array
$post_ids = array();
foreach($posts as $post) {
array_push($post_ids, $post->id);
}
// get comments for ALL posts in one query by passing array of post ids (post_id IN (...))
$comments = Comments::getComments(array('post_id' => $post_ids));
// map comments to posts
foreach($posts as $key => $post) {
foreach($comments as $comment) {
if($post->id == $comment->post_id) {
$post->pushComment($comment);
}
}
}
foreach($posts as $post) {
$comment = $post->comments;
// do something with comments..
}
</code>
<code>$posts = Posts::getPosts(); // get the ids into an array $post_ids = array(); foreach($posts as $post) { array_push($post_ids, $post->id); } // get comments for ALL posts in one query by passing array of post ids (post_id IN (...)) $comments = Comments::getComments(array('post_id' => $post_ids)); // map comments to posts foreach($posts as $key => $post) { foreach($comments as $comment) { if($post->id == $comment->post_id) { $post->pushComment($comment); } } } foreach($posts as $post) { $comment = $post->comments; // do something with comments.. } </code>
$posts = Posts::getPosts();

// get the ids into an array
$post_ids = array();
foreach($posts as $post) {
  array_push($post_ids, $post->id);
}

// get comments for ALL posts in one query by passing array of post ids (post_id IN (...))
$comments = Comments::getComments(array('post_id' => $post_ids));

// map comments to posts
foreach($posts as $key => $post) {
  foreach($comments as $comment) {
    if($post->id == $comment->post_id) {
      $post->pushComment($comment);
    }
  }
}

foreach($posts as $post) {
  $comment = $post->comments;
  // do something with comments..
}

This is much more PHP, and kinda messy too, but this time I’m only using two queries (one for posts, the other for fetching ALL comments of those posts in one query). Is this a good proposal to tackle the n+1 problem in PHP?

Also, how do frameworks generally deal with this under the hood?

2

Your original approach does lazy loading while your modified code does eager loading.

You are absolutely right that eager loading is more efficient in your situation. In most cases, minimising the number of queries is the best thing you can do to speed up your app. If you were only going to look at one of the posts then lazy loading would be faster.

Most ORMs (at least, all the good ones!) support lazy and eager loading. For example, SQLAlchemy has extensive support. You were asking about PHP; I expect the main PHP ORMs have similar features. Usually the ORM defaults to lazy loading, but you can tell it to eager load a particular table when executing a query.

In fact, it is possible load all your data in one query. SQLAlchemy has two eager loading modes: joined and subquery. joined does it all in one query, which is sometimes the most efficient way, but it does end up querying duplicate data. subquery eager load is just like your approach. I’m not sure whether PHP ORMs support this distinction.

1

The inefficiency/messiness is coming from “hydrating” your data too early and too often. By “hydrating,” I mean instantiating data objects from (what I assume are) records in your database.

You don’t always need to deal with data objects. For example, with this code…

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>$posts = Posts::getPosts();
// get the ids into an array
$post_ids = array();
foreach($posts as $post) {
array_push($post_ids, $post->id);
}
</code>
<code>$posts = Posts::getPosts(); // get the ids into an array $post_ids = array(); foreach($posts as $post) { array_push($post_ids, $post->id); } </code>
$posts = Posts::getPosts();

// get the ids into an array
$post_ids = array();
foreach($posts as $post) {
  array_push($post_ids, $post->id);
}

…I assume Posts::getPosts() fetched some rows from the database, and created a new Post data object for each row, only to extract the IDs and throw out the data objects. If you added a function like Posts::getPostIds() that returned an array of numbers, you could call it in place of that block of code.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>$post_ids = Posts::getPostIds();
</code>
<code>$post_ids = Posts::getPostIds(); </code>
$post_ids = Posts::getPostIds();

The remainder of your code brings up a tougher question. Ideally you still want to be able to do something like your original example, except you want the “comment” data objects populated from a recordset you got beforehand rather than running a separate query for each post. Maybe in place of Comments::getComments, you could add a getComments instance method to Post to handle this, and pass it a recordset.

This is all assuming you actually need to get comments for multiple posts at once; I can’t think of a situation where you’d need to do that offhand but I assume you know what you’re doing.

As for understanding how other frameworks handle ORM, I’d recommend taking a look at redbean’s documentation to see how it’s used, and if you’re interested, have a look at the source.

For the record, any ORM in any tech stack potentially has the N+1 problem. Even hand-written code can have this problem.

The way to most efficiently combat the N+1 problem is actually a JOIN in SQL. This is where software engineers absolutely need to learn SQL, and how their programming code gets converted into SQL.

The original code you posted ends up performing these SQL statements:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>SELECT * FROM posts; # returns, let's say, posts 1-20
SELECT * FROM post_comments WHERE post_id = 1;
SELECT * FROM post_comments WHERE post_id = 2;
SELECT * FROM post_comments WHERE post_id = 3;
SELECT * FROM post_comments WHERE post_id = 4;
SELECT * FROM post_comments WHERE post_id = 5;
SELECT * FROM post_comments WHERE post_id = 6;
SELECT * FROM post_comments WHERE post_id = 7;
SELECT * FROM post_comments WHERE post_id = 8;
SELECT * FROM post_comments WHERE post_id = 9;
SELECT * FROM post_comments WHERE post_id = 10;
SELECT * FROM post_comments WHERE post_id = 11;
SELECT * FROM post_comments WHERE post_id = 12;
SELECT * FROM post_comments WHERE post_id = 13;
SELECT * FROM post_comments WHERE post_id = 14;
SELECT * FROM post_comments WHERE post_id = 15;
SELECT * FROM post_comments WHERE post_id = 16;
SELECT * FROM post_comments WHERE post_id = 17;
SELECT * FROM post_comments WHERE post_id = 18;
SELECT * FROM post_comments WHERE post_id = 19;
SELECT * FROM post_comments WHERE post_id = 20;
</code>
<code>SELECT * FROM posts; # returns, let's say, posts 1-20 SELECT * FROM post_comments WHERE post_id = 1; SELECT * FROM post_comments WHERE post_id = 2; SELECT * FROM post_comments WHERE post_id = 3; SELECT * FROM post_comments WHERE post_id = 4; SELECT * FROM post_comments WHERE post_id = 5; SELECT * FROM post_comments WHERE post_id = 6; SELECT * FROM post_comments WHERE post_id = 7; SELECT * FROM post_comments WHERE post_id = 8; SELECT * FROM post_comments WHERE post_id = 9; SELECT * FROM post_comments WHERE post_id = 10; SELECT * FROM post_comments WHERE post_id = 11; SELECT * FROM post_comments WHERE post_id = 12; SELECT * FROM post_comments WHERE post_id = 13; SELECT * FROM post_comments WHERE post_id = 14; SELECT * FROM post_comments WHERE post_id = 15; SELECT * FROM post_comments WHERE post_id = 16; SELECT * FROM post_comments WHERE post_id = 17; SELECT * FROM post_comments WHERE post_id = 18; SELECT * FROM post_comments WHERE post_id = 19; SELECT * FROM post_comments WHERE post_id = 20; </code>
SELECT * FROM posts; # returns, let's say, posts 1-20

SELECT * FROM post_comments WHERE post_id = 1;
SELECT * FROM post_comments WHERE post_id = 2;
SELECT * FROM post_comments WHERE post_id = 3;
SELECT * FROM post_comments WHERE post_id = 4;
SELECT * FROM post_comments WHERE post_id = 5;
SELECT * FROM post_comments WHERE post_id = 6;
SELECT * FROM post_comments WHERE post_id = 7;
SELECT * FROM post_comments WHERE post_id = 8;
SELECT * FROM post_comments WHERE post_id = 9;
SELECT * FROM post_comments WHERE post_id = 10;
SELECT * FROM post_comments WHERE post_id = 11;
SELECT * FROM post_comments WHERE post_id = 12;
SELECT * FROM post_comments WHERE post_id = 13;
SELECT * FROM post_comments WHERE post_id = 14;
SELECT * FROM post_comments WHERE post_id = 15;
SELECT * FROM post_comments WHERE post_id = 16;
SELECT * FROM post_comments WHERE post_id = 17;
SELECT * FROM post_comments WHERE post_id = 18;
SELECT * FROM post_comments WHERE post_id = 19;
SELECT * FROM post_comments WHERE post_id = 20;

This makes for a lot of network chatter back and forth.

What you really want is this:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>SELECT posts.*,
comments.*
FROM posts
LEFT JOIN post_comments comments ON comments.post_id = posts.id;
</code>
<code>SELECT posts.*, comments.* FROM posts LEFT JOIN post_comments comments ON comments.post_id = posts.id; </code>
SELECT posts.*,
       comments.*
FROM posts
LEFT JOIN post_comments comments ON comments.post_id = posts.id;

Yes, you get “more” rows back than you need, but it is the network chatter back and forth that causes the performance bottle neck. Any good ORM should be configurable to do this, and it will intelligently handle the duplicate records for each post and glue the objects together for you.

Use the usability approach of pagination on per demand basis. Do not underload, do not overload anything: screens, buffers, loops, streams, files. Consider what and how much you need in a momentum of processing and make it your rule. No screen list can present anything bigger than the screen, no memory buffer can load a full database table, no socket can bring a day’s stock trading in a single go, nor it will be there to serve millions of individual requests. High performance computing is standing on batch microprocessing

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật