Eager Loading = Query performance?

Show the code.

collection with some relations and it makes the same amount of queries using Eager Loading or not

Not at all. Eager loading perform one query per relationship regardless of the number of models in your collection.

Without it, a query is executed for each relationship of each model in your collection.

M = number of models. R = number of relationships. With eager loading R queries executed. Without it M * R queries executed.

For only one model, it is effectively the same number of queries as M = 1.

So the thing is eager loading allow to execute a constant number of queries to retrieve the data.

If your using or can use chrome check out the clockwork plugin. It will out put your queries from a local environment. Experiment with eager loading.

I have found that sometimes your right, it doesn't cut down on the number of queries for example one to one. But if you notice the time line for the query execution you should always see a faster time with eager loading as it retrieves the data in one go instead of two or more separate times.

With out eager loading when the controller is hit via the route URL, it will execute the query and send the data on. Assuming a view, when the view is loading any none eager loaded relationships called with then execute another query as the data isn't available.

So first query 200ms, loads view 200ms, needs to execute another query at 200ms. Total time 600ms. Or eager load the relationships and the query may take 300ms so a total of 500ms. More relationships and/or nested or many to many will benefit even more.

Thank you guys.

An example of the code for @toniperic :

$flights = Flight::with('passengers')->get();

I get your great explanation @pmall and @jekinney but it does not seem to work for me, I'm tracking the SQL queries and it shows the same number when eager loading relations or not.

Thanks for the clockwork plugin, did not know it, I used another desktop monitor.

I'm tracking the SQL queries and it shows the same number when eager loading relations or not.

Here there must be two queries, one for flights and one for passengers.

Of course, if you then use $flight->passengers()->get() or $flight->passengers()->count() an extra query is executed. Eager loading works for loading $flight->passengers collection.

@jekinney

I have found that sometimes your right, it doesn't cut down on the number of queries for example one to one.

Of course it cut down the number of queries for one to one relationships. For example, if you have a collection of comments having one author, only two queries are executed instead of (number of comments + 1) number of queries.

@pmail

one to one wouldn't return a collection of many (Comments in your example).

But you had a user and a user profile (user hasOne profile and profile belongTo user) no, eager loading doesn't cut down on 2 queries to one, it will always be two queries. BUT it gets the information all at once as I stated versus two separate query calls thus speeding up the application as I stated. @pmail, try it out!!!!!

one to one wouldn't return a collection of many (Comments in your example).

I was talking about a collection of comments, with a belongs to author relationship.

eager loading doesn't cut down on 2 queries to one, it will always be two queries. BUT it gets the information all at once as I stated versus two separate query calls thus speeding up the application as I stated

You are contradicting yourself here.

$comment = Comment::with('author')->first();
echo $comment->author;

Two queries

$comment = Comment::first();
echo $comment->author;

Two queries

$comments = Comment::with('author')->get();
foreach ($comments as $comment) {
    echo $comment->author;
}

Two queries

$comments = Comment::all();
foreach ($comments as $comment) {
    echo $comment->author;
}

1 + (number of comments) queries.

@pmall @jekinney

After some tests I realized that it works as it should and just like you explained with some queries but not with others (it executes one query per each relation, per item).

Could this problem be related to other relationships?

E.g. Passenger belongs to Flight and also belongs to Group, should I define this with polymorphic or it is not necessary? Thought that maybe it could be the reason.

@pmail not a contradiction but maybe a misunderstanding. Your examples still don't show a one to one. A comment belongsTo a user, while the inverse is hasMany, not hasOne.

Any case, that doesn't help the op which tested and proved my point.

@GastonUy

Could you post your relationships set in your model and the query? I am interested as a fyi besides helping you out.

Your examples still don't show a one to one. A comment belongsTo a user, while the inverse is hasMany, not hasOne.

It works exactly the same way. Replace comment by user and author by profile in my example above if it helps you understand. If it doesn't work this way in your code there is a problem with it.

$user = User::with('profile')->first();
echo $user->profile;

Two queries

$user = User::first();
echo $user->profile;

Two queries

$users = User::with('profile')->get();
foreach ($users as $user) {
    echo $user->profile;
}

Two queries

$users = User::all();
foreach ($users as $user) {
    echo $user->profile;
}

1 + (number of users) queries.

@GastonUy show us some code. You can always have a constant number of queries but sometimes it is misleading :)

E.g. Passenger belongs to Flight and also belongs to Group, should I define this with polymorphic or it is not necessary? Thought that maybe it could be the reason.

No it is not necessary at all and I feel it is not suited here. $passengers = Passenger::with('flight', 'group')->get() should work as expected.

@pmall @jekinney Thanks, just realized what was the problem, and now the expected queries are excecuted, no matter how many 'passengers' I got :)

I had appends attributes that executed those extra queries haha

The relationships are ok, just simple One to Many

Flight class:

public function passengers()
{
    return $this->hasMany('Passenger');
}

Group class:

public function passengers()
{
    return $this->hasMany('Passenger');
}

Passenger class:

public function flight()
{
    return $this->belongsTo('Flight');
}

public function group()
{
    return $this->belongsTo('Group');
}

Thanks again!

lol

@GastonUy just saying, I was loling because I provided you a full explanation and you put your own answer as accepted answer ;)

@pmall you know I was thinking about it right now, I just did it without thinking, it was in order to close the thread as I realized it was my mistake, but you are totally right, I'm sorry :)

Thanks mate you are welcome :)