Eager loading vs Lazy loading???

[eluser]rei[/eluser]
Which is really faster?? To LAZY LOAD or to EAGER LOAD many associations w/ large data sets??? Because based on my research eager loading is faster because it queries all the data at once or minimizes the number of queries to be executed. But based on my experience why is lazy loading faster? Here is the scenario: users table have many posts and each posts has many comments and each comment has many author. Im using PHP Active Record

I dump many data in the tables in the database:
47 total rows in the users table
1305 total rows in the posts table
1138 total rows in the comments table
788 total rows in the authors table

Here is the benchmark:

BENCHMARKS

EAGER LOADING:
Loading Time: Base Classes 0.0216
Controller Execution Time ( Welcome / Eager ) 16.1154
Total Execution Time 16.1371

LAZY LOADING:
BENCHMARKS
Loading Time: Base Classes 0.0120
Controller Execution Time ( Welcome / Lazy ) 4.0325
Total Execution Time 4.0446

[eluser]srpurdy[/eluser]
I think it really depends on the data set and what your doing with it though. I think there is more variables to consider. How good the server is your using for that benchmark. How well mysql is optimized. For example if you have heavy disk i/o because of mysql not able to cope. This will hurt the performance of lazy loading. I would also put some benchmark mark points in your code so you can see what code is causing the bottleneck, and also a cpu/memory restricted system will likely have more issues with the "eager loading"

With those benchmarks are you loading all that data on 1 page? Seems a bit slow in both cases to me. I've done table scans across 30,000 records X 3 to get a result in under 0.1 seconds So I find those numbers crazy. What are you doing exactly in these benchmarks?

I'm not really that familiar with those terms though. I guess you mean in cases where you stack queries to get your results?

I've actually had the opposite in terms of benchmarks. In heavy data situations (not so much how many rows, but in cases where the rows I'm extracting have a lot of data in them) where a stacked queries caused as much as an extra 0.1 load per stack. So eventually having 10 stacks the total execution was close to 1 second. Where when I optimized the query the result was loading the same data in 0.2 seconds, and it scaled up to any amount of stacks with pretty much the same exact load.

[eluser]rei[/eluser]
okay sir thanks, I will try to put some benchmark points in the code later.

nwei here is the source:

Code:

function eager()
{
  // eager load
    $users = User::find('all', array('include' => array('posts' => array('comments' => array('blogs')))));

    foreach ($users as $user) {
     echo 'User Name: ' . $user->name;
     echo '<br>';

     foreach ($user->posts as $post) {
      echo 'Post: ' . $post->content;
      echo '<br>';

      //print_r($post->comments);


      foreach ($post->comments as $comment) {
       echo 'Comment: ' . $comment->comment;
       echo '<br>';

       foreach ($comment->blogs as $blog) {
        echo $blog->name;
       }

      }
     }
    }
}




function lazy()
{
  // lazy load
  $users = User::find('all');

    foreach ($users as $user) {
     echo 'User Name: ' . $user->name;
     echo '<br>';

     foreach ($user->posts as $post) {
      echo 'Post: ' . $post->content;
      echo '<br>';

      //print_r($post->comments);


      foreach ($post->comments as $comment) {
       echo 'Comment: ' . $comment->comment;
       echo '<br>';

       foreach ($comment->blogs as $blog) {
        echo $blog->name;
       }

      }
     }
    }
}

I'm using PHP Active Record. and I'm only benchmarking this in my laptop localhost. xampp server

[eluser]toopay[/eluser]
Agree with @spurdy, most of the time eager loading (less query to execute) wins. It seems either your ORM was not optimal for recursive relationship like that (check the executed queries to ensure) or your ORM was not efficient on object manufactoring process.

[eluser]rei[/eluser]
@toopay - I think you are correct sir, I think the problem is in the ORM I am using right now, I think its a problem of the PHP AR on object manufactoring process. Because the query they are executing is alright. Its like this one:

Code:

SELECT * FROM users WHERE id IN (1,2,3,4,5,6,7)

Nwei I will try your GAS ORM and benchmark it because you have a quick fix now Thanks