0

I wrote a query in Laravel which is:

$policy = DB::table('policies')-> join('customers','policies.customer_id','=','customers.id')-> join('cities','customers.city_id','=','cities.id')-> join('policy_motors','policies.id','=','policy_motors.policy_id')-> join('vehicle_makes','policy_motors.vehicle_make','=','vehicle_makes.id')-> join('vehicle_models','policy_motors.vehicle_model','=','vehicle_models.id')-> select('policies.policy_number','policies.insurance_premium','policies.commission', 'policies.effective_start_date', 'policies.effective_end_date','customers.name_en', 'customers.address1','customers.address2','cities.name_en','policy_motors.policy_type', 'vehicle_makes.name_en','vehicle_models.name_en')-> where('policies.policy_number','=','DB202017036583')->first();

This query worked perfectly on my Mac. However, when my colleague ran the same query on his Windows machine, it was taking forever. So he wrote one himself, that is:

$policy = Policy::with('customer', 'motor', 'user')-> where('policy_number', 'RK202117017053')->first();

His query worked perfectly on his Windows and my Mac.

Questions: 1. Although my query is selecting only required columns, it is taking forever. But his query, which takes all the columns of the joined table executes faster. Why is that happening?

2. What difference does it make to run a query on different machines, the time difference should be that significant?

Improve this question
9
  • I'd check whether the bottleneck is in the database, running the raw SQL query in a DB console. Maybe the tables are index optimized in the MAC but not in the Windows machine? Assuming you are using local databases, of course... Commented Jan 9, 2018 at 12:07
  • We ran the same query in raw SQL in phpMyAdmin on Mac and Windows, it was taking the same amount of time. I mean using phpMyAdmin on Windows, raw SQL query was running fine. Commented Jan 9, 2018 at 12:14
  • In any case, that's proof that you'd better use eloquent rather than sql construction if you can avoid it. Commented Jan 9, 2018 at 12:22
  • Are you sure Eloquent always generates optimized queries? I doubt that. Commented Jan 9, 2018 at 12:38
  • 1
    Also, you can do a query using Eloquent and eager loading and then check the log files. You will see that many queries are made, not only one as in Query Builder Commented Jan 9, 2018 at 12:55

2 Answers 2

Reset to default
1
  1. Although my query is selecting only required columns, it is taking forever. But his query, which takes all the columns of the joined table executes faster. Why is that happening?

Even though your query is only selecting a few columns, it does a lot of sub-queries to the table that, if they don't have a proper index, will cause a long run time execution.

His query is faster because the way laravel do eager loading. Laravel do not do sub-queries on the same query, it does a lot of query and the make a relation using collections. What I mean is basically that your query runs a lot of inside queries while your partner's do multiple queries and then merge them using collections

  1. What difference does it make to run a query on different machines, the time difference should be that significant?

Also, there may be some difference if the queries are running locally. Usually SQL consults take ram and processor power to do searching and joining, so if your PC is running low for whatever reason it will take more time than a PC in the right conditions. But if the SQL machine is in the cloud there shouldn't be any difference in execution

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@Amarnasan please comment.
0

The reason why second query is faster is it's using eager loading

it eager loads relationships

Take a look at this link Eager Loading

Improve this answer

3 Comments

Your point is very ambiguous. Eager loading loads when the property is first accessed. DB::table('...')->first() does the same thing.
I don't think you can apply the concept "eager loading" when you are using DB::table, because you are not creating models that can be "navigated and loading" after, but straight Database table results.
@FalakMarri You are talking there about Query Builder which is not the same as Elqouent

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.