Hello! I'm Valerio, an Italian developer and CTO of the platform
Inspector.dev.
In this article, I will share a set of ORM optimization strategies that I use when developing backend services.
I'm sure each of us had to complain that the server or application is running slowly (or even not working at all), and while away the time at the coffee machine waiting for the results of a long request.
How to fix it?
Let's find out!
The database is a shared resource
Why is the database causing so many performance problems?
We often forget that no query is independent of others.
We think that even if some query is slow, it hardly affects others ... But is it really so?
A database is a shared resource used by all processes that run in your application. Even one poorly designed method of accessing the database can disrupt the performance of the entire system.
Therefore, do not forget about the possible consequences, thinking: "It's okay that this piece of code is not optimized!" One slow access to the database can lead to its overload, and this, in turn, can negatively affect the user experience.
N + 1 database query problem
What is the N + 1 problem?
This is a common problem when using an ORM to interact with a database. It is not about writing SQL code.
When using an ORM system like Eloquent, it is not always obvious which queries will run and when. In the context of this particular problem, let's talk about relationships and eager loading.
Any ORM system allows you to declare relationships between entities and provides a great API for navigating your database structure.
Below is a good example for the "Article" and "Author" entities.
/*
* 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;
}However, when using relationships within a loop, you must write your code carefully.
Take a look at the example below.
We want to add the author's name next to the article title. With ORM, you can get the author's name using a one-to-one relationship between the article and the author.
Everything seems to be 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;
}But then we fell into a trap!
This loop generates one initial request to get all articles:
SELECT * FROM articles;and N more queries to get the author of each article and display the value of the "name" field, even if the author is always the same.
SELECT * FROM author WHERE id = [articles.author_id]We receive exactly N + 1 requests.
This may not seem like a big deal. Well, let's make fifteen or twenty extra requests - no big deal. However, let's go back to the first part of this article:
- — , .
- , , .
- , .
:
According to the Laravel documentation, there is a good chance that you will run into the N + 1 query problem, because when you access Eloquent relationships as properties (
$article->author), the relationship data is lazy loaded.
This means that the relationship data is not loaded until you first access the property.
However, using a simple method, we can load all of the relationship data at once. Then, when accessing the Eloquent relation as a property, the ORM system will not execute a new query because the data has already been loaded.
This tactic is called "eager loading" and is supported by all ORMs.
// 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 a method
with()for eagerly loading relationships.
In this case, only two queries will be executed.
The first one is needed to download all articles:
SELECT * FROM articles;The second one will be executed by the method
with()and will fetch all authors:
SELECT * FROM authors WHERE id IN (1, 2, 3, 4, ...);Eloquent's internal engine will map the data and it can be accessed in the usual way:
$article->author->name;Optimize your operators select
For a long time, I thought that explicitly declaring the number of fields in a fetch query did not lead to a significant performance improvement, so for simplicity I got all the fields in my queries.
Also, rigidly specifying the list of fields in a specific select statement makes it harder to further maintain such a piece of code.
The biggest pitfall of this argument is that from a database perspective, this may indeed be true.
However, we work with an ORM, so the data selected from the database will be loaded into memory on the PHP side so that the ORM system will manage it further. The more fields we capture, the more memory the process will take.
Laravel Eloquent provides a select method to restrict the query to only the columns we need:
$articles = Article::query()
->select('id', 'title', 'content') // The fields you need
->latest()
->get();By excluding fields, the PHP interpreter does not have to process unnecessary data, so you can significantly reduce memory consumption.
Avoiding full fetch can also improve performance for sorting, grouping, and merging, as the database itself can save memory as a result.
Use views in MySQL
Views are SELECT queries based on other tables and stored in the database.
When we SELECT one or more tables, the database first compiles our SQL statement, makes sure it is free of errors, and then fetches the data.
A view is a precompiled SELECT statement that, when processed, MySQL immediately executes the underlying internal query of the view.
Also, MySQL is usually smarter than PHP when it comes to filtering data. There are significant performance gains when using views over using PHP functions to process collections or arrays.
If you'd like to learn more about MySQL's capabilities for developing database-intensive applications, check out this great site: www.mysqltutorial.org
Link an Eloquent Model to a View
Views are also referred to as "virtual tables". From the ORM's point of view, they look like regular tables.
Therefore, you can create an Eloquent model to query the data that is in 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);
}
}Relationships work as usual, as do coercions, pagination, and so on. And there is no performance penalty.
Conclusion
I hope these tips will help you develop more reliable and scalable software.
All of the code examples are written using Eloquent as the ORM, but keep in mind that these strategies work the same for all major ORMs.
As I often say, we need tools to implement effective strategies. And if there is no strategy, then there is nothing to talk about.
Thank you very much for reading the article to the end. If you want to know more about Inspector, I invite you to our website www.inspector.dev . Do not hesitate to write to the chat if you have any questions!
Previously posted here: www.inspector.dev/make-your-application-scalable-optimizing-the-orm-performance
Read more: