Level 73
I take it you are running them both directly after each other. Since they are the same the database uses the cached query on the second run and thus the second run is faster.
Jan 6, 2022
23
Level 9
Laravel DB Query is slower than plain PDO implementation. Why?
Can someone explain to me, why the plain PDO implementation with PDO is waaayyy faster than the framework implementation? To be clear, we do not talk about query optimization and adding indexes or not. I just figured out, that the laravel way is slower than the plain way and I wish to know how to solve this.
greetings, J
P.S.: forget to mention, for reproduction I use a existing database with a clearly fresh laravel installation without any extras. just laravel new sample and added the code above to routes/console.php
Level 1
@Tray2 That is not correct. When you use PDO it has to create its own separate connection to the Database so caching is not it.
Level 73
@Npdlink352 Yes, but I'm talking about the database, the query and execution plan is already cached the second time, thus making it faster.
Level 102
Is it the same if you swap them?
Level 9
nope, order doesn't matter. No caching, nothing.
Reproduce your self
- seed 100Mio users.
- modify the query above to
$query = \DB::from('users')->select('name', 'email', 'password') - run it. You will see a time gab between.
- feel free to change execution order of both queries
select `name`, `email` from `users`
pure PHP implementation
Query Execution Time4.2185919284821
DB implementation by framework
Query Execution Time8.6809899806976
Level 73
@itwasntme Why the hell are you doing an unconditional select from a table with that many rows?
Of course it takes a bit longer with Laravel since it returns a collection of user instances while your pdo query just returns an array.
Level 9
@Tray2 'a bit'? a bit would be fine, but the original one tokes waaaaayyyy longer than a bit
Level 73
@itwasntme Show me the SQL it generates.
Level 9
this is the original one. You should get that output if you run $query->toSql() in tinker or so.
select `f`.`fake_call_id`, GROUP_CONCAT(f.event_id) as events from (select `event_id`, CONCAT(service_version_id, "-", service_call_id, "-", service_slot_id, "-", round_trip_number) as fake_call_id from `service_schedules` order by `service_call_id` asc, `service_version_id` asc, `service_slot_id` asc, `round_trip_number` asc) as `f` group by `f`.`fake_call_id`
Level 73
@itwasntme First off I would remove all the ordering since it does nothing but slows the query down. Then I would add a where clause to limit the result or use pagination, however this query should do the same as the one you wrote. You might need to use order by on the outer query and not on the inline view like you did.
SELECT
fake_call_id,
GROUP_CONCAT(event_id) AS events
FROM (
SELECT
event_id,
CONCAT(service_version_id, "-", service_call_id, "-", service_slot_id, "-", round_trip_number) AS fake_call_id
FROM
service_schedules)
GROUP BY fake_call_id
Level 73
@itwasntme I also recommend installing the laravel debugbar. It will help you figure out what the problem is.
Level 9
Hej @Tray2,
thanks for that. the sorted columns are index based. ordering should be very fast, but I will remove them. Pagination or limiting is not possible for reasons, we have to work with the complete resultset here. Keep in mind that we have something around 4.3mio results in the answer.
to be clear, it is not the SQL which makes this stuff slow. running it in mysql-client on CLI or tablePlus results in ~53sec execution time. which is fine, because the query reduces 23.5mio rows down to 4.3mio
Level 73
@itwasntme Then you really should consider chunking the result.
Level 9
Hej @Tray2 ,
that is indeed true, but it doesn't explain the gap in execution time. And I hope to get a valid answer to the question "Why is it that slow?"
Level 73
@itwasntme Could be that the server needs to swap when you run the query through the query builder and it doesn't when it runs through pdo.
Level 1
@itwasntme I can assure you that the reason is because PDO is closer to the MySql engine commands than Laravel query builder.
You kind of see this same reaction between Laravel and CodeIgniter, although I like Laravel more for its easy writing but both CodeIgniter and PDO are faster in query execution. I use PDO all the time when processing large numbers of records.
NOTE: PDO is a good idea if using ngneX instead of Appache due to its internal response timer, unless you configure it to have a large timeout.
Level 102
Try setting the pdo one to also return stdClass
$result = $stmt->fetchAll(PDO::FETCH_OBJ);
Level 102
Did you try this?
I just tested creating 2mill. arrays in an array vs. 2mill. stdClasses in an array. The array version was MUCH faster, and laravels DB::query() uses stdClass
Level 9
hej @Sinnbeck,
you can change this in laravel with
\Event::listen(Illuminate\Database\Events\StatementPrepared::class, function ($event) {
$event->statement->setFetchMode(PDO::FETCH_ASSOC);
});
Level 102
@itwasntme Ah didnt know you did that in your code. But it changes nothing then I assume?
Level 9
hej @Sinnbeck,
I'm playing around with but didn't get that big improvement compared to the self-made PDO version. I'd also played around with buffered and unbuffered connections.
Level 9
There intersting part is, that I inspect the query while running with show full processlist. I can cleary see that the query is executing. But execution time of my selfmade PDO is lower than execution time with the Frameworks one. It goes deeply down to MySQL. And I think, it should have something todo with the connection parameters.
Level 4
@itwasntme Know this is fairly old, but did you ever find any solution. I'm going down a rabbit hole. When Laravel grabs 20K indexed records, mysql stays in an executing mode for nearly 7 minutes. When running directly on mysql (first, second, after reboot, cached, not cached) it takes between 0.3 and 1.2 seconds. I can't figure it out for the life of me and this is the closest scenario I've found searching.
Please or to participate in this conversation.