CODEWITHSUNDEEP
mysqllaravel
Falak Marri
Falak Marri

Reputation: 211

Long query retrieval time for a simple query in Laravel

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?

Upvotes: 0

Views: 1617

Answers (2)

Luis felipe De jesus Munoz
Luis felipe De jesus Munoz

Reputation: 7933

  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

Upvotes: 1

Mahdi Younesi
Mahdi Younesi

Reputation: 7509

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

Upvotes: 0

Related Questions