7

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:

$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:

$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?

migrated from codereview.stackexchange.com Jul 9 '14 at 5:12

This question came from our site for peer programmer code reviews.

  • Somewhat tangential, but I had immense difficulty with this exact problem in Rails. You just finish up having to do work that should be left to the ORM. – david.pfx Jul 9 '14 at 11:25
  • 1
    Any reason why you couldn't just set it up to do a JOIN? – Mason Wheeler Jul 9 '14 at 12:40
4

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.

  • I'm nitpicking, but subquery loading in SQLAlchemy is not exactly like the OP's second approach. The difference is that it uses a nested SELECT (SELECT x FROM (SELECT y ...))), so that the programmer doesn't have to provide parent object IDs (here: posts) explicitly. – Xion Jul 12 '14 at 20:50
4

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

$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.

$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.

0

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:

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:

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.

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.