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 with ORM optimization strategies.

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:

$article->author->name;

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

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: https://www.mysqltutorial.org/

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.

Conclusion

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.

Application monitoring

If you found this post interesting and want to drastically change your developers’ life for the better, you can give Inspector a try.

Inspector is an easy to use Code Execution Monitoring tool that helps developers to identify bugs and bottlenecks in their application automatically. Before customers do.

screenshot inspector code monitoring timeline

It is completely code-driven. You won’t have to install anything at the server level or make complex configurations in your cloud infrastructure.

It works with a lightweight software library that you can install in your application like any other dependency. Check out the supported technologies in the GitHub organization.

Create an account, or visit the website for more information: https://inspector.dev

Related Posts

cloud costs savings with code execution monitoring

How to save thousands of dollars in cloud costs with Code Execution Monitoring

Hi, I’m Valerio, software engineer, co-founder and CTO at Inspector. In this article I want to show you a monitoring strategy to help you save thousands of dollars a year on cloud costs. The typical scenario where you can get the most payback from this strategy is when your application runs on multiple servers. If

The 5 Best Application Monitoring Tools for 2022

When it comes to application monitoring software, there are some great tools available to make our lives easier. We’ve compiled a list of the very best. No offense intended but the truth is customers are no longer interested in what you have to do to make your product work when they need it. They only

What Are Source Maps and How to Properly Use Them

You are debugging a web app for a client but the minified version of the Javascript and CSS code makes it impossible to understand what statements the browser is actually executing. You could break down the original code line by line in your editor putting some “console.log()” statements here and there, or try debugging it

Join the "Scalable Applications" community

Learn from other developers' experience.
Join the international community of developers to build scalable applications.

Inspector customer feedback
2020 © Inspector S.R.L. - VAT: 09552901218 - Progetto agevolato con la misura Resto al SUD RSUD0000000 - CUP C61B21012300008

How to build scalable applications

Get the e-book about the Inspector scalability journey.