Have you ever wondered why your application is slow despite the fact that you've already fixed the code?
The application's performance is not determined by the application itself. Both the application and the database must be tuned jointly. Consider the following scenario: 100 clients enter a large, fancy hotel (application), but only two waiters are available (database). How can two waiters service all of the clients without holding them in line for an extended amount of time? Finally, the customer leaves the hotel, never returning.
However, in this two-part article, we will go through 18 tips that, if adopted, would increase the performance of your Laravel application.
1. Retrieving large data
This tip focuses on optimizing your application's memory usage while dealing with large datasets.
if your application has to process a large number of records, Instead of collecting all of the results at once, you can retrieve a subset of them and process them in groups.
We would generally do something like this to get a large set of data from a table called products.
$products = Product::all(); // when using eloquent
$products = DB::table('products')->get(); // when using query builder
foreach ($products as $product){
// Process products
} All of the records in the products table will be retrieved and processed in the examples above. What if there are a million rows in this table? We'll be out of memory in no time.
We can retrieve a subset of results and process them as follows to prevent problems when dealing with large datasets.
option 1: Using chunk
// when using eloquent
$products = Product::chunk(100, function($products){
foreach ($products as $product){
// Process products
}
});
// when using query builder
$products = DB::table('products')->chunk(100, function ($products){
foreach ($products as $product){
// Process products
}
}); In the example above, 100 records are retrieved from the products table, processed, and then another 100 records are retrieved and processed. This process will be repeated until all of the records have been processed.
This method will result in more database queries, but it will be memory efficient. Processing huge datasets are usually done in the background. To avoid running out of memory when processing large datasets, it's fine to perform more queries in the background is fine.
option 2: Using cursor
// when using eloquent
foreach (Product::cursor() as $product){
// Process a single product
}
// when using query builder
foreach (DB::table('products')->cursor() as $product){
// Process a single product
} The above example will do a single database query, retrieve all of the records from the table, and hydrate each eloquent model individually. To retrieve all of the posts, this method will use only one database query. However, PHP generator is used to reduce memory use.
When will you be able to use this?
Though this drastically reduces memory consumption on the application level, the memory usage on the database instance will still be larger because we are retrieving all of the information from a table.
It is preferable to utilize a cursor when your web app is operating on less RAM but the database instance uses more. If your database instance does not have enough memory, you should stick to chunking.
option 3: Using chunkById
// when using eloquent
$products = Product::chunkById(100, function($products){
foreach ($products as $product){
// Process product
}
});
// when using query builder
$products = DB::table('products')->chunkById(100, function ($products){
foreach ($products as $product){
// Process product
}
}); The main difference between chunk and chunkById is that chunk obtains data based on offset and limit. chunkById, on the other hand, obtains database results based on an id field. This id field is normally an integer field, and in most circumstances, it is auto-incrementing.
The chunk and chunkById searches were as follows.
chunk
select * from products offset 0 limit 100
select * from products offset 101 limit 100 chunkById
select * from products order by id asc limit 100
select * from products where id > 100 order by id asc limit 100 Limiting with offset is often slower, and we should try to avoid it.
chunkById query will be substantially faster because it uses the id column, which is an integer, and also uses a where clause.
When can chunkById be used? - If the primary key column in your database table is an auto-incrementing field.
2. Select only the columns you need
Usually, we would execute the following to retrieve results from a database table.
products = Product::find(1); //When using eloquent
$products = DB::table('products')->where('id','=',1)->first(); //When using query builder The query generated by the above code is shown below.
select * from products where id = 1 limit 1 The query performs a select *, as you can see. This indicates that it is obtaining every column from the database table. This is good if we absolutely require all of the table's columns.
Instead, if we only require a few columns (id, name), we can extract them individually as seen below.
$products = Product::select(['id',name])->find(1); //When using eloquent
$products = DB::table('products')->where('id','=',1)->select(['id',name])->first(); //When using query builder The query generated by the above code is shown below.
select id,name from products where id = 1 limit 1 3. When you only require one or two columns from a database, use pluck.
This tip focuses on the time spent after the database results have been retrieved. It has no effect on the query time.
As I previously stated, we would do the following to extract specified columns.
$products = Product::select(['name','slug'])->get(); //When using eloquent
$products = DB::table('products')->select(['name','slug'])->get(); //When using query builder When the above code is run, the following happens behind the scenes.
Executes a database query to select name and slug from products.
For each row it retrieves, it creates a new Product model object (For query builder, it creates a PHP standard object)
With the Product models, creates a new collection.
The collection is returned.
To get to the results, we'd execute the following code:
foreach ($products as $product){
// $product is a Product model or php standard object
$product>name;
$product>slug;
} The overhead of hydrating the Product model for each and every row and constructing a collection for these objects is added to the above approach. This is the ideal option if you only require the Product model instance and not the data. However, if you only require those two values, you can proceed as follows.
$products = Product::pluck('name', 'slug'); //When using eloquent
$products = DB::table('products')->pluck('name','slug'); //When using query builder When the above code is run, the following happens behind the scenes.
Executes a database query to select name, slug from products.
The name is the array value, while the slug is the array key.
Returns the array(array format: [ slug => name, slug => name]),
To get to the results, we'd execute the following:
foreach ($products as $slug => $name){
// $name is the name of a product
// $slug is the slug of a product
} You can do something like this if you only want to obtain one column.
$products = Product::pluck('name'); //When using eloquent
$products = DB::table('products')->pluck('name'); //When using query builder
foreach ($products as $name){
// $name is the name of a product
} The method shown above avoids the need to create Product objects for each row. As a result, memory utilization and processing time for query results are reduced.
“Only use the above way on fresh code. Going back and refactoring your code to follow the above recommendation, in my opinion, is not worth the work spent. Only refactor old code if it is processing massive datasets or if you have spare time.”
4. Count rows using query instead of collection
We would generally do the following to count the total number of rows in a table:
$products = Product::all()->count(); //When using eloquent
$products = DB::table('products')->get()->count(); //When using query builder The following query will be generated as a result of this.
select * from products The above method retrieves all of the table's rows, loads them into a collection object, then counts the results. When the database table has fewer rows, this works perfectly. However, as the table increases, we will shortly run out of memory.
Instead of using the above method, we can simply count the total number of rows in the database.
$products = Product::count(); //When using eloquent
$products = DB::table('products')->count(); //When using query builder The following query will be generated as a result of this.
select count(*) from products “Counting rows in SQL is a time-consuming operation that works poorly when the database table has a large number of rows. It's best to stay away from counting rows as much as possible.”
5. By using an eager loading relationship, you can avoid N+1 queries.
You've probably heard this tip a million times. As a result, I'll keep it as brief and straightforward as possible. Let's assume you are in the following situation.
class ProductController extends Controller {
public function index() {
$products = Product::all();
return view('products.index', ['products' => $products ]);
}
} // products/index.blade.php file
@foreach($products as $product)
<li>
<h3>{{ $product->name }}</h3>
<p>Category: {{ $product->category->name }}</p>
</li>
@endforeach The code above retrieves all of the products and displays their names and categories on the webpage. It also assumes your product model has a category relationship.
The following queries will be performed when the above code is executed.
select * from products // Assume this query returned 5 products
select * from categories where id = { product1.category_id }
select * from categories where id = { product2.category_id }
select * from categories where id = { product3.category_id }
select * from categories where id = { product4.category_id }
select * from categories where id = { product5.category_id } As you can see, we have one query for retrieving products and five queries for retrieving product categories (Since we assumed we have 5 products.) As a result, it does a new query for each product it retrieves in order to retrieve its category.
As a result, if there are N products, it will generate N+1 inquiries ( 1 query to retrieve products and N queries to retrieve category for each product). This is referred to as the N+1 query problem.
To avoid this, eager load the relationship between categories and products as shown below.
$products = Product::all(); // Avoid doing this
$products = Product::with(['category'])->get(); // Do this instead The following queries will be performed when the above code is executed.
select * from products // Assume this query returned 5 posts
select * from categories where id in( { product1.category_id }, { product2.category_id }, { product3.category_id }, { product4.category_id }, { product5.category_id } ) 6. Eager load nested relationship
From the above example in tip 5, consider the category belongs to a catalog, and you want to display the catalog name as well. As a result, in the blade file, you'd do as follows.
@foreach($products as $product)
<li>
<h3>{{ $product->name }}</h3>
<p>Category: {{ $product->category->name }}</p>
<p>Category Catalog: {{ $product->category->catalog->name }}</p>
</li>
@endforeach This will lead to the following queries.
select * from products // Assume this query returned 5 products
select * from categories where id in( { product1.category_id }, { product2.category_id }, { product3.category_id }, { product4.category_id }, { product5.category_id } )
select * from catalogs where id = { category1.catalog_id }
select * from catalogs where id = { category2.catalog_id }
select * from catalogs where id = { category3.catalog_id }
select * from catalogs where id = { category4.catalog_id }
select * from catalogs where id = { category5.catalog_id } Even though we are eager loading category relationships, it is still generating more queries, as you can see. We are not eager loading the catalog relationship on categories.
This can be fixed by doing the following.
$products = Product::with(['category.catalog'])->get(); The following queries will be performed as a result of running the above code.
select * from products // Assume this query returned 5 posts
select * from categories where id in( { product1.category_id }, { product2.category_id }, { product3.category_id }, { product4.category_id }, { product5.category_id } )
select * from catalogs where id in( { category1.catalog_id }, { category2.catalog_id }, { category3.catalog_id }, { category4.catalog_id }, { category5.catalog_id } ) As a result, we reduced the total number of queries from 11 to 3 by eager loading the nested relationship.
7. Do not load belongsTo relationship if you just need its id
Imagine you have two tables, one for products and the other for categories. The category id field in the Products database represents a belongsTo relationship in the categories table.
We would generally do the following to obtain the category id of a product:
$product = Product::findOrFail(<product id>);
$product->category->id; As a result, these two queries would be executed.
select * from products where id = <product id> limit 1
select * from categories where id = <product category id> limit 1
Instead, perform the following to obtain the category id directly.
$product = Product::findOrFail(<product id>);
$product->category_id; // products table has a column category_id which stores id of the category “When can I use the method outlined above?
You can utilize the above strategy when you know that an entry in the categories table will always exist if it is referenced in the products table.”
8. Avoid unnecessary queries
We frequently perform database queries that aren't required. Consider the following example.
<?php
class ProductController extends Controller {
public function index() {
$products = Product::all();
$finished_products = FinishedProduct::all();
return view('products.index', ['products' => $products, 'finished_products' => $finished_products ]);
}
}
The code above retrieves rows from two distinct tables (products and finished products) and sends them to view. The following is a snippet of the view file.
// products/index.blade.php
@if( request()->user()->roleAdmin() )
<h2>Finished Products</h2>
<ul>
@foreach($finished_products as $product)
<li>
<h3>{{ $product->name }}</h3>
<p>Finished At: {{ $product->finished_at }}</p>
</li>
@endforeach
</ul>
@endif
<h2>Products</h2>
<ul>
@foreach($products as $product)
<li>
<h3>{{ $product->name }}</h3>
<p>Created At: {{ $product->created_at }}</p>
</li>
@endforeach
</ul>
Only a user who is an admin can see $finished_products, as you can see above. These products are hidden from the rest of the users.
The issue here is that when we are doing:
$products = Product::all();
$finished_products = FinishedProduct::all(); We have two queries. One for the records in the products table, and another for the records in the finished_products table.
Only the admin user has access to the records in the finished_products table. Despite the fact that these records are not visible to all users, we continue to run the query to obtain them.
To prevent this unnecessary query, we can change our logic to the following.
$products = Product::all();
if( request()->user()->roleAdmin() ){
$finished_products = FinishedProduct::all();
} We are making two queries for the admin user and one query for all other users by modifying our logic to the above code snippet.
Conclusion
In this article, we covered the first eight (8) tips; in the next article, we will cover the next eight (8) tips for optimizing the performance of your Laravel application.