DEV Community

Cover image for Laravel ORM vs Query Builder vs SQL: SPEED TEST!
Hesam Rad
Hesam Rad

Posted on • Edited on

Laravel ORM vs Query Builder vs SQL: SPEED TEST!

#laravel #php #optimization

Ever wondered which one of these is faster?

  • ORM
  • Query Builder
  • SQL

I don't know about you guys but for me this is a BIG question; so I looked into it and I decided to share what I got.

First of all, for small projects and personal blogs it doesn't matter which one you choose, because the database is not that big and either of these three will work just fine.

But what if you're working on an application or a website with a HUGE database?

This is where the answer to this question can be a game changer for you.

So let's find out.

I prepared a database with over 100,000 users and 100,000 articles assigned to each users. (Assigning articles to users was completely random and because of that some users might have 10 articles or have none assigned to them.)

It's fair to say that it's quite a big database, right? ;)

WARNING
I'm pretty sure everybody here already know this but it's worth mentioning that you should NEVER EVER try to get all the records in the database in one query.

If you unknowingly try to do so on a big database you could easily break you application.

For the purpose of this article I tried to get all 100,000 articles with their users.

Here's what happened:

Alt Text

See? It doesn't look good; and here's the code that caused it:



$articles = Article::with('user')
    ->get();
Enter fullscreen mode Exit fullscreen mode

The reason for this exception is that the application simply ran out of ram. The database was too big that the ram couldn't handle all the rows of information.

How to avoid this?
To avoid this kind of exception you could get the data in smaller chunks. For example we could modify the code above to avoid the exception:



$articles = Article::with('user')
    ->limit(1000)
    ->get();
Enter fullscreen mode Exit fullscreen mode

The limit(1000) limits the query to take only 1000 records at a time and this will keep a part of ram unused.

Ok; enough with the warning. Let's get to the good stuff.

In this example, I wrote queries that returned the same data using ORM, Query Builder and raw SQL; plus I calculated the time of execution and returned it alongside the fetched information so I can compare which one is faster.

Let's take a look.

ORM



$articles = Article::with('user')
    ->limit(1000)
    ->get();
Enter fullscreen mode Exit fullscreen mode

time of execution: 0.07746

Query Builder



$articles = DB::table('articles')
    ->join('users', 'articles.user_id', '=', 'users.id')
    ->limit(1000)
    ->get();
Enter fullscreen mode Exit fullscreen mode

time of execution: 0.04171

SQL



$articles = DB::select("select * FROM articles JOIN users ON articles.user_id = users.id limit 1000");
Enter fullscreen mode Exit fullscreen mode

time of execution: 0.04461

To be honest I was expecting the raw SQL to be a bit faster than Query Builder but I was wrong. (I tested a couple more times to be sure.)

So there you have it. The winner of this test is Query Builder.

Of course there's another way to make each of these methods a bit faster, but that's a topic for another post.

Until the next post, take care. 🖤

Top comments (3)

Collapse
 
lito profile image
Lito • • Edited

There are not difference between Query Builder and SQL query because Query Builder is only a query generator, and generate a string using an object is not a hard work. Both methods returns same result.

But ORM is more than a query builder, every database result is hydratated with the model, then you have a collection with 1000 Article models instead a collection of 1000 plain objects.

You can do same test with Query Builder and SQL with hydrate methods:

ORM

$articles = Article::with('user')
    ->limit(1000)
    ->get();
Enter fullscreen mode Exit fullscreen mode

Query Builder

$articles = DB::table('articles')
    ->join('users', 'articles.user_id', '=', 'users.id')
    ->limit(1000)
    ->all();

$articles = Article::hydrate($articles);
Enter fullscreen mode Exit fullscreen mode

SQL

$articles = Article::fromQuery("select * FROM articles JOIN users ON articles.user_id = users.id limit 1000");
Enter fullscreen mode Exit fullscreen mode

Cheers!

Collapse
 
leob profile image
leob •

Wow, didn't know about the "hydrate" method!

Collapse
 
hesamrad profile image
Hesam Rad •

You're right... After all using ORM is much more cleaner and maintainable but I had this question in mind for a long time and I wanted to answer it myself and share the results.
BTW, I didn't know about hydrate. What a neat idea!
Cheers buddy.

Read next

icolomina profile image

Creating focused domain applications. A Symfony approach (Returning the result)

Nacho Colomina Torregrosa -

icolomina profile image

Creating focused domain applications. A Symfony approach (Managing validation errors)

Nacho Colomina Torregrosa -

biostate profile image

Throwing Success or Failure Notifications Manually in FilamentPHP Actions

Süleyman Özgür Özarpacı -

hurayraiit profile image

Understanding PHP Types - NULL

Abu Hurayra -