This article describes exporting large data sets from a Laravel application following best practices and performance optimization. In general most web-based application has data export as a common feature. But when exporting a large amount of data requires a long time where the server experiences overload, hampering other executions on the server. So it’s very important to export data with the best optimization techniques.
Background
While developing an education-related application with several complex features and functions, our client requested an option to download all students’ basic, education, family, and other relevant information in a single Excel file. In response, we initiated a thorough study and research phase to determine the most efficient way to export extensive data from our Laravel application consistently. Our goal is to ensure that end-users can seamlessly investigate the data of all students. As a result, our focus has shifted towards developing robust mechanisms for exporting large datasets.
Challenge
As we attempted to export large datasets to download all student data into a single Excel file, we encountered significant delays in preparing the dataset due to the execution of queries. This was primarily due to the substantial number of students in the system, exceeding 10 lakh. Consequently, the maximum PHP execution time of 120 seconds repeatedly expired before completing the Excel file generation. Our challenge lies in reducing the execution time and devising a method to generate the Excel file incrementally by appending the necessary data part by part.
Keywords and Definition
Queue:
Laravel Queue is a powerful tool for managing and processing time-consuming tasks in a scalable and efficient manner. Laravel Queue is a feature in the Laravel framework that allows you to defer the processing of time-consuming tasks, such as sending emails or processing large data sets, to improve the performance of your application. It provides a unified API for working with different queue backends, such as databases, Redis, Amazon SQS, and more.
Using Laravel Queue, exporting large data sets you can push tasks onto a queue and have them processed asynchronously in the background so that this helps exporting large data sets It frees up your application’s resources. and allows it to handle more requests concurrently. The queue system works by a worker listening to the queue and performing tasks as they queue up. Laravel provides a simple and expressive syntax for defining and dispatching jobs to the queue.
Jobs:
In Laravel, jobs are classes that encapsulate the logic of a specific task or job that one needs to execute asynchronously. Using jobs with the Laravel Queue system allows one to defer time-consuming tasks and improve the application’s performance. Typically, Laravel jobs help with tasks such as sending emails, processing images, generating reports, or any other task that can run in the background without blocking the main application flow. Jobs also allow exporting large data sets in Laravel to make the process smooth.
To create a job in Laravel, you can use the make:job Artisan command, which generates a new job class file. Inside the job class, you define the logic to execute when the job is dispatched. Jobs can be dispatched to the queue using the dispatch method so it adds the job to the queue for asynchronous processing. You can also specify various options for the job, such as the queue connection, delay, and priority. When a job is dispatched, it is pushed onto the queue and a worker process listens to the queue and executes the jobs as they are processed. The worker process picks up the job from the queue, executes the logic defined in the job class, and marks the job as completed.
Queue Worker:
In Laravel, a queue worker is a process that listens to the queue and executes jobs as they are pushed onto it. It is responsible for processing the queued jobs in the background. When you dispatch a job to the queue as a result it is added to a queue system of your choice, such as a database, Redis, or Amazon SQS. The queue worker process continuously checks the queue for new jobs and executes them one by one. To start a queue worker in Laravel, you can use the queue:work Artisan command.
This command starts a worker process that listens to the default queue and processes jobs as they become available. If any gigantic task like exporting large data sets we should create the jobs and by running a queue worker jobs are processed step by step as the server is less loaded. You can also specify the connection and queue name to listen to using command options. The queue worker process runs indefinitely until it is manually stopped or terminated. It continuously polls the queue for new jobs and executes them in the order they were added.
Once a job is processed, it is marked as completed and removed from the queue. Laravel provides various configuration options for queue workers, such as the number of worker processes to run concurrently, the maximum number of jobs a worker can process before restarting, and the timeout for processing a job. Exporting large data sets from a Laravel application is easy to do creating jobs as it chunks the large data into some small portions.
Methodology and Implementation
Let’s jump into the main topic, how I implemented the process of exporting bulk data into an Excel file.
Create Laravel Application
To create a Laravel application, follow the instructions described on the link below:
Steps to create Laravel Application
Create Model
Create a model named LargeDataExportStatus by running the command
php artisan make:model LargeDataExportStatuThe model and migration files are ready. Open the large_data_export_statuses_table migration file and add the following columns:
$table->string('job_batch_id');
$table->boolean('is_completed');Create Controller
Firstly, we need to create a controller. Open the terminal and execute the command:
php artisan make:controller LargeDataExportControllerCreate a new function index as follows:
public function index(){
// We need the total figure we need to export
$usersCount = User::count();
LargeDataExportJob::dispatch();
$users = User::orderByDesc('id')
->skip($this->skip)
->take($this->take)
->get();
}
Create Route
Open the web.php file under the routes directory of the Laravel application and write the following line to create a route:
Route::get('large_data_exports', [LargeDataExportController::class, 'index']);Insert bulk data into the database
The Laravel application provides a User model class and UserFactory class by default. So let’s assume that the UserFactory class is available in your application. Open the UserFactory class, and delete the line:
'email' => 'user@user.com'and add the line:
'email' => $this->faker->email()Hence, we need a unique email address each time to insert a huge number of user information in the users table, so we need to take a unique email from Laravel’s Faker library. Now open the terminal and run the command:
php artisan tinkerThen Laravel Tinker will open. Now run:
User::factory()->count(5000)->create();5000 users have been created that we will export.
Create mother jobs to create child jobs
Create a mother job `LargeDataExportBatchJob` by command
php artisan make:job LargeDataExportBatchJobWrite the following code in the handle method of the LargeDataExportBatchJob class
$totalUsers = User::count();
$chunkSize = 250;
$loopEndLimit = ceil($studentsCount / $chunkSize);
$jobs = [];
for ($i = 1; $i <= $loopEndLimit; $i++) {
$skip = $chunkSize * ($i - 1);
$jobs[] = new LargeDataExportJob($skip, $chunkSize);
}
$batch = Bus::batch($jobs)
->then(function (Batch $batch) {
// insert status, batch id in large data exports table
LargeDataExportStatus::create([
'job_batch_id' => $batch->id,
'is_completed' => true
]);
})
->catch(function (Batch $batch, Throwable $error) {
//
LargeDataExportStatus::create([
'job_batch_id' => $batch->id,
'is_completed' => false
]);
})
->dispatch();Create a job to insert rows into Excel file chunk-wise
Create a job named `LargeDataExportJob` by executing the command
php artisan make:job LargeDataExportJobNow, declare 2 private properties named skip and take
private $skip, $take;create __construct function to receive skip and take as arguments which are passed in LargeDataExportJob($skip, $chunkSize) (called in LargeDataExportBatchJob)
public function __construct($skip = 0, $take = 10){
$this->skip = $skip;
$this->take = $take;
}now we have 3 tasks, get all the user’s data, prepare an array with necessary fields to be displayed on an Excel file, open csv file, and write into it. So now create 3 methods respectively.
// get users data
$usersChunkData = $this->getUsers();
// make ready the data array to insert on csv
$data = $this->makeDataArrayToExport($usersChunkData);
// insert in csv file
$this->writeOnCsvFile($data);Create getUsers() method to fetch users data from database
public function getUsers(){
return User::orderByDesc('id')
->skip($this->skip)
->take($this->take)
->get();}
Create makeDataArrayToExport() method to prepare the data array that needs to be exported
public function makeDataArrayToExport($users){
$data = [];
foreach($users as $user){
$data[] = [
'name' => $user->name,
'email' => $user->email
];
}
return $data;
}Create writeOnCsvFile() method to open and write into CSV file
private function writeOnCsvFile($data)
{$fileName = ‘all_users.csv’;
$file = fopen($fileName, ‘a’);
foreach ($data as $fields) {
fputcsv($file, $fields);
}
fclose($file);
}
Results and Analysis
1. Set the chunk size: Determine the number of users to fetch and insert into the Excel file in each job. It is important to divide the full data into small chunks when exporting large datasets. For example, if you set the chunk size to 100, each job will fetch 100 users from the database and insert them into the Excel sheet.
2. Create a Laravel job: Generate a new Laravel job using the make:job Artisan command. This job will handle the export process.
3. Define the job logic: Inside the job class, write the logic to fetch the users from the database in chunks and insert them into the Excel file. You can use Laravel’s query builder or Eloquent ORM to retrieve the user data.
4. Use the Laravel Excel package: Install and use the Laravel Excel package (https://laravel-excel.com/) to simplify the process of exporting data to Excel. This package provides a fluent interface for creating and manipulating Excel files. You can do the task of exporting large data sets by using this package easily.
5. Configure the Excel export: Set up the Excel export with the desired file name, sheet name, and column headers. You can define the columns to include in the Excel file and format them as needed. Fetch and insert users in
Conclusion
To summarize, the ability to export large datasets in CSV format from a Laravel application is an essential function that enables developers to effectively manage and distribute extensive data. In this technical article, we have examined the implementation of this feature, delving into the complexities of optimizing performance and memory usage to efficiently handle large datasets.
This article emphasizes the practical importance of this functionality. In addition, we have successfully utilized this export feature in our UNICEF projects, where exporting large datasets plays a critical role in data management and dissemination.
References
This page was last edited on 16 July 2024, at 5:55 pm
How can we help you?
