Level 61
Try to use whereIn for trip_id and then key results by that trip_id or something that makes sense in order to get an easy match with the remaining data.
1 like
Jan 20, 2020
9
Level 1
Database performance improvement: searching through 500,000+ rows
Hi,
I have a job that takes quite some time (near or more than a minute). I would like to find any opportunities to improve my code for better performance.
Currently, the function goes through $entity (up to 1,200) and try to find a corresponding $trip. There is more than 500,000 trips (records).
foreach ($feed->getEntity() as $entity) {
/*
* Check if trip is in database
*/
$trip = Trip::where([['agency_id', '=', $this->agency->id], ['trip_id', '=', $entity->getVehicle()->getTrip()->getTripId()]])
->remember(60 * 60 * 24)
->cacheTags('trip_queries')
->select('id')
->first();
}
Over the time, I have made some improvements, the first one being to select only the id field, wich resulted in a big improvement. Then, I added the Watson\Rememberable package, wich didn't had any major difference.
Currently, when there is a lot of vehicles, the job timeout after 60 seconds (I don't want to extend the timeout, as I want the same job to run every minute).
Any ideas on improvements?
Thank you!
Level 122
bit worrying that you don't mention indexes anywhere in your question?
You would definitely need an index of agency_id +trip_id
Must watch this; https://youtu.be/HubezKbFL7E
2 likes
Level 61
Can confirm that is a great video.
Level 1
@bugsysha Not sure about how to use whereIn, any example?
I also want to precise that trip_id is not an integer, but a string (see the migration).
Level 1
@snapey I am not familiar but I'll definitively watch this video. I'll try something and see what it gives. Thanks!
Level 2
I find writing my SQL first before refactoring it to Eloquent ORM is helpful. Like the comment above mentioned, there isn't any mention of indexes. Without knowing your schema, it can be hard to find what to optimize.
I say this as my background and current job is a DBA :)
500k rows typically isn't enough to warrant a 1 minute response time unless you're not indexing or you're indexing large text bodies. If you could post your schema or share a bit more, I'd be happy to help you optimize your query!
Level 61
If whereIn is not what you need then I would suggest to pull in Laravel Debugbar and enable models in configuration so you can see how many queries and hydrated models does your code generate.
Use this video https://reinink.ca/articles/viewing-model-counts-in-the-laravel-debugbar
Level 10
Are you eager loading your relationships?
‘’’ foreach ($feed->getEntity() as $entity) ‘’’ Your getEntity() method should eager load your vehicle and trip relationships. Other wise as you iterate over your foreach your making new queries to find the vehicle and trip.
Try: ‘’’ foreach ($feed->load(‘vehicle.trip’) as $entity) ‘’’
Also, not sure if you can use cursor on above.. ‘’’ foreach ($feed->load(‘vehicle.trip’)->cursor() as $entity) ‘’’ https://laravel.com/docs/6.x/eloquent#chunking-results
Level 1
Using ['trip_id', 'agency_id'] index was the best solution, running each job about 4x faster.
For reference, here is my schema: https://github.com/FelixINX/montreal-transit-tracker/blob/master/database/migrations/2019_06_25_194911_create_transit_tracker_tables.php
Please or to participate in this conversation.