Laravel ORM vs Query Builder vs SQL: SPEED TEST!

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:

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

$articles = Article::with('user')
    ->get();

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();

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();

time of execution: 0.07746

Query Builder

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

time of execution: 0.04171

SQL

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

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. 🖤

Comments

[Lito]

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();

Query Builder

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

$articles = Article::hydrate($articles); 

SQL

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

Cheers!

[leob]

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

[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.