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. 🖤
Top comments (3)
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
Query Builder
SQL
Cheers!
Wow, didn't know about the "hydrate" method!
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.