Make your application scalable optimizing the ORM performance

Valerio Barbera

Hi, I’m Valerio, software engineer from Italy and CTO at Inspector.

In this article I would share a set of ORMs optimization strategies that I’m using in almost every backend service I’m working on.

I’m sure each one of us has complained about a machine or application being slow or even dead, and then spent time at the coffee machine waiting for the results of a long running query.

How can we fix that?

Let’s start!

Orm Optimization Strategy

Database is a shared resource

Why so many performance issues are caused by the database?

We often forget that each request is not independent of other requests.

If one request is slow, it’s unlikely to affect the others… right?

Database is a shared resource used by all processes that runs in your application. Even just one poorly designed access can hurt the performance of the whole system.

So, be careful with thinking “it’s okay if this piece of code isn’t optimized“. One slow database access can hurt the performance of the whole system, causing negative experiences to your users.

N+1 database queries problem

What is the N+1 problem?

This is a typical problem encoutered using an ORM to interact with the database. It’s not a SQL coding issue.

When you use an ORM like Eloquent, it isn’t always obvious what queries will be made and when. For this particular problem we talk about relationships and eager load.

Any ORM allows you to declare relationships between entities providing a great API to navigate our database structure.

Article and Author” is great example.

* Each Article belongs to an Author
$article = Article::find("1");
echo $article->author->name;

* Each Author has many Articles
foreach (Article::all() as $article)
    echo $article->title;

But we need to code with care using relations inside a loop.

Take a look on the example below.

We want add the author’s name next to the article’s title. Thanks to the ORM we can navigate the one-to-one relation between Article and Author to get its name.

It sounds really simple:

// Initial query to grab all articles
$articles = Article::all();

foreach ($articles as $article)
    // Get the author to print the name.
    echo $article->title . ' by '. $article->author->name;

We have fallen into the trap.

This loop generate 1 initial query to grab all articles:

SELECT * FROM articles;

and N queries to get the author for each article to print the “name” field. Also if the author is always the same.

SELECT * FROM author WHERE id = [articles.author_id]

N+1 queries precisely.

It may not seem like such an important problem. Fifteen or Twenty query could be appears as not an immediate issue to be addressed. Be careful and review the first part of this article:

  • Database is a resource shared by all processes.
  • Database machine has limited resources, or if are using a managed service, more database load may be imply more costs.
  • If your database is located in a seprated machine all data needs to be transfered with additional network latency.

[Solution] Use eager laoding

As mentioned in the Laravel documentation, we can easily fallen into the N+1 query problem because when accessing Eloquent relationships as properties ($article->author), the relationship data is “lazy loaded”. This means the relationship data is not actually loaded until you first access the property.

However we can load all relationships data with a simple method, so when you access Eloquent relationship as property, it will not run a new query, because tha data has already been loaded by the ORM.

This tactic is called “eager load”, supported by all ORM.

// Eager load authors using "with".
$articles = Article::with('author')->get();

foreach ($articles as $article)
    // Author will not run a query on each iteration.
    echo $article->author->name;

Eloquent provides the method with() to eager load relationships.

In this case only two queries wil be executed.

The first is needed to load all articles:

SELECT * FROM articles;

The socond is by the with() methods and it will retrieve all authors:

SELECT * FROM authors WHERE id IN (1, 2, 3, 4, ...);

Eloquent will map the data internally to be used as usual:


Optimize select statements

For long time I thought that explicitly declare the number of fields in a select query didn’t bring a significant performance improvement, so I took advantage of simplicity of just getting all the fields for my queries.

Furthermore hard coding the fields list for a specific select, is not an easy to maintain code statement.

The biggest mistake behind this argument is that it could be true from the database perspective.

But we are using the ORM, so the data selected from the database will be loaded in memory on the PHP side to be managed by the ORM. The more fields we grab the more memory the process will take.

Laravel Eloquent provides the “select” method to limiting the query to only the columns we needs:

$articles = Article::query()
    ->select('id', 'title', 'content') // The fields you need

Excluding fields PHP does not have to process this data, so you can significantly reduce the memory consumption.

Not selecting everything can also improve sorting, grouping and join performance because the database can save memory that way.

Use MySQL views

Views are SELECT queries built on the top of other tables and stored in the database.

When we performs a SELECT against one or more table, the database needs to compile your SQL statement, verify that it doesn’t contain errors, and execute the data selection.

Views are pre-compiled SELECT statements, so MySQL can runs the underline query immediately.

Furthermore MySQL is generally smarter than PHP when it comes to filtering data. Compared to using collections or array functions in PHP, there’s a big performance gain.

If you want learn more about MySQL capalibility for database intensive applications, take a look on this amazing website:

Attach an Eloquent Model to a View

Views are “virtual tables”. They appears as normal tables from the ORM point of view.

That’s why we can create an Eloquent Model to query data inside the View.

class ArticleStats extends Model
    * The name of the view is the table name.
    protected $table = "article_stats_view";
    * If the resultset of the View include the “author_id”
    * we can use it to retrieve the author as normal relation.
    public function author()
        return $this->belongsTo(Author::class);

Relations works as usual, also casting, pagination, etc, and there no more performance impact.


Well done!

I hope that one or more of this tips can help you to create a more solid and scalable software product.

I have used Eloquent ORM to write code exmaples, but you can replicate these strategies with all well known ORMs in the same way. As I often say, tools should helps us to implement an efficient strategy. Strategic thinking is the key to give a long term perspective to our products.

New to Inspector?

Are you looking for a “code-driven” monitoring tool instead of having to install things at the server level?

Get a monitoring environment specifically designed for software developers avoiding any server or infrastructure configuration.

Thanks to Inspector, you will never have the need to install things at the server level or make complex configuration in your cloud infrastructure to monitor your application in real-time.

Inspector works with a lightweight software library that you can install in your application like any other dependencies. In case of Laravel you have our official Laravel package at your disposal. Developers are not always comfortable installing and configuring software at the server level, because these installations are often managed by external teams, and they are out of the software development lifecycle.

Visit our website for more details:

Related Posts

How to monitor your Laravel application by services not by hostnames

Hi, I’m Valerio, software engineer, founder & CTO at Inspector. This post was born after responding to a support request by a developer who asked me how he can monitor his application by services, not by hostnames. We have investigated this request and here is the solution we have found. The company is developing the

How code-driven monitoring tools can help you deliver successful software products

Hi, I’m Valerio, software engineer, founder & CTO at Inspector. In this article I would like to share my experience with you about why software developers should always prefer code-driven instead of infrastructure-driven monitoring tools.  Understanding their different approaches can help you better organize your team, stay agile and fast during delivery times, and quickly

How to prevent users from registering into your app with insecure passwords

Hi, I’m Valerio, software engineer and CTO at Inspector. About one year ago one of our accounts on an external platform has been hacked. Our credit card was attached to this account so we had to warn the bank to block it. Fortunately, there were no consequences, neither for our bank account, nor for our