Stop Blaming the ORM: How to Write Fast Eloquent Queries

In the Laravel ecosystem, Eloquent ORM is a great and convenient tool that greatly simplifies working with a database. With Eloquent, you can work with your database as if you are working with ordinary PHP objects. However, there is rumour that eloquent is heavy and comparatively slow in performance. Because of this misconception, many developers try to replace Eloquent queries with raw SQL queries or even with the Laravel Query Builder when it's not necessary.

In this article, we will explore what makes Eloquent so great and convenient. We will see what internal mechanisms make Eloquent efficient and in which particular cases and scenarios Eloquent is better than using the Query Builder. We will also compare how Eloquent and Query Builder handle hydration of data and what differences there are when we use such methods as toBase(), pluck(), value(), exists(), eager loading, aggregates, and columns selection.

Understanding the "Overhead" of Eloquent

The main reason that Eloquent is often portrayed as slow is due to the "hydration" process of the objects retrieved. In other words, when using Eloquent to query the database you are not simply getting an array of rows, but rather a collection of PHP objects with populated values and relationships. This comes at the small price of a slightly higher memory consumption than simply using the Query Builder would.
But it is a very small price to pay for the added productivity of working with eloquent models and collections. This is why most applications should be fine with using Eloquent as their primary means of interacting with the database without having to worry about optimizations related to the hydration process.

Eloquent vs. Query Builder

Laravel's Query Builder allows you to build database queries in a clean manner using method chaining. While the Query Builder provides a fluent interface for creating and executing database queries, Eloquent is an extension of the Query Builder that allows you to work with your database tables as PHP objects.

// Using Eloquent - returns a collection of Post model instances
$posts = App\Models\Post::all();
foreach ($posts as $post) {
    echo $post->title; // Accessing object properties
}

// Using Query Builder - returns a collection of stdClass objects
$postsQb = DB::table('posts')->get();
foreach ($postsQb as $post) {
    echo $post->title; // Accessing object properties, but it's a generic object
}

As you can see, syntax is similar, but return types are different. The "slowness" is when you get thousands of rows, and Eloquent has to instantiate thousands of model objects. The difference between the two approaches in most cases when you work with small sets of data, it's barely noticeable.

When You Need Eloquent Already Optimized: toBase()

Sometimes you want the convenience of Eloquent's model definitions (scopes, global scopes, soft deletes, etc.) but you don't need the full model hydration. Eloquent has a toBase() method which essentially falls back to Query Builder and returns raw stdClass objects instead of full model instances.

// Using Eloquent to get raw data, bypassing model hydration
$rawPosts = App\Models\Post::where('status', 'published')
    ->toBase() // Crucial for performance here
    ->get();

foreach ($rawPosts as $post) {
    echo $post->title; // Accessing properties of stdClass object
}

But it's useful when you're fetching a huge set of records just for display or some other processing that doesn't require you to do any further Eloquent operations on them (e.g. updating or saving relationships).

Retrieving Specific Columns: pluck() and value()

When you only need select columns of data, hydrating model instances (or even stdClass objects) for every record can be very wasteful. Eloquent has some highly optimized methods for these cases.

Retrieving a List of Values with pluck()

The pluck() method is useful for getting a list of values from a single column, or even a list of values based on a single column.

// Get a list of all post titles
$titles = App\Models\Post::pluck('title');
// $titles will be a Laravel Collection: ['Post Title 1', 'Post Title 2', ...]

// Get a list of post titles, keyed by their IDs
$titlesWithIds = App\Models\Post::pluck('title', 'id');
// $titlesWithIds: [1 => 'Post Title 1', 2 => 'Post Title 2', ...]

pluck() executes a `SELECT title FROM posts` query and directly returns a collection of values, completely bypassing model hydration. This is incredibly efficient.

`value()` for a Single Column Value

If you need just one column from a single record, `value()` is your go-to method.

// Get the title of the post with ID 1
$postTitle = App\Models\Post::where('id', 1)->value('title');

This executes a `SELECT title FROM posts WHERE id = 1 LIMIT 1` query and returns the value directly and avoids model instantiation.

Checking for Existence: `exists()`

A common mistake is to retrieve records just to check if they exist. For instance, `Post::where('user_id', $userId)->get()->isNotEmpty()` is less efficient than `exists()`.

// Inefficient way to check existence
$userHasPosts = App\Models\Post::where('user_id', $userId)->get()->isNotEmpty();

// Efficient way using exists()
$userHasPostsEfficient = App\Models\Post::where('user_id', $userId)->exists();

The exists() method will fire a `SELECT EXISTS(...)` or `SELECT 1 FROM ... LIMIT 1` query, which will return a boolean value without actually fetching any data. This can be extremely useful in cases where you just need a true/false response.

Solving the N+1 Problem with Eager Loading

One of the most common performance issues with ORMs, especially ones that get talked about in relation to Eloquent specifically, is the N+1 problem. This is when you query for a set of models, and then inside a loop, query each one for a relationship. This results in a total of N+1 queries (1 for the original query, and then N for the items in a collection)

// N+1 problem: Inefficient
$posts = App\Models\Post::all();
foreach ($posts as $post) {
    echo $post->user->name; // Each call to $post->user executes a new query
}

// Eager Loading: Efficient with 'with()'
$postsWithUsers = App\Models\Post::with('user')->get();
foreach ($postsWithUsers as $post) {
    echo $post->user->name; // 'user' relationship is loaded in a single extra query
}

Using with('relationship') will instruct Eloquent to perform a single additional query to retrieve all of the related models for the initial set, dramatically reducing the number of queries executed. This makes Eloquent extremely efficient when dealing with relationships.

Using Aggregates

Like the Query Builder, Eloquent provides convenient methods for performing common database aggregates such as count(), sum(), avg(), max(), and min(). These methods have the same effect as their database counterparts and return their results as PHP types, rather than as hydrated models.

// Get the total number of posts
$totalPosts = App\Models\Post::count();

// Get the sum of views for published posts
$totalViews = App\Models\Post::where('status', 'published')->sum('views');

// Get the maximum likes for a post
$maxLikes = App\Models\Post::max('likes');

These are highly optimized database operations that do not make use of Eloquent's hydration process at all.

Selecting Only Required Columns

By default, when you call Model::all() or Model::get(), Eloquent will fetch all columns from the table (SELECT ). While convenient, if you only need a few columns, fetching everything can be a waste of bandwidth and resources (amount of data for Eloquent to hydrate).

You can explicitly specify the columns you need by using the select() method

// Inefficient: Fetches all columns
$allColumnsPosts = App\Models\Post::all();

// Efficient: Fetches only 'id' and 'title' columns
$selectedColumnsPosts = App\Models\Post::select('id', 'title', 'published_at')->get();

foreach ($selectedColumnsPosts as $post) {
    echo $post->id . ': ' . $post->title;
    // $post->content would be null here as it was not selected
}

When you specify particular columns, Eloquent will only populate those attributes on the model instance, which can reduce the amount of data hydrated into the model. Make sure to always select the primary key (id) when you're going to perform any other Eloquent operations like save(), delete(), etc.

When Does Dropping to Query Builder Make Sense?

Despite all of these optimizations, there are still cases when using Query Builder methods, or even raw SQL, can be more performant than using Eloquent:

Large scale operations: Using Query Builder’s update() and delete() methods are much more performant for bulk operations that don’t require model events or soft deletes.

Very complex queries: Query Builder (or raw SQL) is still a better option for very complex queries or joins involving stored procedures that would be too tedious to recreate in Eloquent’s ORM style.

Reporting: Using the Query Builder or toBase() is much more performant if you are doing large scale operations to get information for reporting that doesn’t require models.

Very large performance bottlenecks: If you have done all other optimizations for a specific operation and it still turns out that model hydration is the bottleneck for a very heavily-trafficked system, it might be worth it to drop down to the Query Builder for that specific endpoint.

Best Practices to Keep Eloquent Fast

Eager Loading is Your Friend: always use with() to avoid N+1 issues
Select Only What You Need: use select() to fetch only columns you need
Use Optimized Methods: use pluck(), value(), exists() and aggregation methods (count(), sum()) whenever possible
Consider toBase(): if you need to use Eloquent's query builder capabilities, but don't need hydrated models
Profile Your Queries: use Debugbar or Telescope (or similar package) to profile your database queries and see what's really happening
Database Indexing: have you indexed your database? It's important that your database can utilize indexes on WHERE and JOIN conditions, which requires proper indexing

Conclusion

Eloquent is a brilliant ORM with excellent developer experience and productivity. The myth about it being slow is just that - a myth. Of course, it can be slow if you use it incorrectly, but this applies to any piece of software. By learning about Eloquent's internals and using such methods as eager loading, pluck(), value(), exists(), and toBase() wisely, you will get amazing results in terms of performance.

Besides, you should only resort to using the Query Builder or raw SQL when you have no other option. A properly used Eloquent ORM with correct relationships and loadings will provide you with incredible performance for your database queries. And you can achieve it without even diving into the low-level implementation details. So, think carefully before deciding to use Eloquent is better in your case. It is usually not worth it.

Thank you for reading this article 😊

For any query, do not hesitate to comment 💬