I'm trying to make an application export data from the table
I have a big table that has 200 million records and trying to find by ID 10K records and export these records in CSV file
Any solution to make it faster because it takes a lot of time while exporting data
# Controller
Validator::make($request->all(), [
'text' => ['required', 'mimetypes:text/plain'],
])->validateWithBag('filter');
$fileContent = file_get_contents($request->text);
$clients = $this->endcode($fileContent);
$this->file = "/exports/extract-data-" . Str::random(6) . '.csv';
PrepareDataJob::dispatch($clients, $request->user(), Mail::to($request->user())->queue(new DataExported()))->afterResponse();
return Inertia::render('Dashboard/Clients/Show', [
'clients' => Export::where('user_id', auth()->user()->id)->latest()->paginate(10),
]);
# PrepareDataJob File
class PrepareDataJob implements ShouldQueue
{
use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
private $clients;
private $file;
private $result = [];
private $user;
/**
* Create a new job instance.
*
* @return void
*/
public function __construct($clients, $user)
{
$this->file = "/exports/extract-data-" . Str::random(6) . '.csv';
$this->clients = $clients;
$this->user = $user;
}
/**
* Execute the job.
*
* @return void
*/
public function handle()
{
DB::table('clients')->orderBy('id')->select('unique_id', 'mobile', 'nationality')->whereIn('unique_id', $this->clients)->chunk(1000, function ($clients) {
foreach ($clients as $client) {
$this->result[] = $client;
}
});
if ($this->result > 0) {
Excel::store(new ClientsExport($this->result), $this->file);
ExportJob::dispatch($this->result, $this->file, $this->user->id);
User::find($this->user->id)->decrement('point', ($this->result));
}
}
}
# Export File
class ExportJob implements ShouldQueue
{
use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
protected $path;
protected $clients;
protected $user_id;
/**
* Create a new job instance.
*
* @return void
*/
public function __construct($clients, $path, $user_id)
{
$this->clients = $clients;
$this->path = $path;
$this->user_id = $user_id;
}
/**
* Execute the job.
*
* @return void
*/
public function handle()
{
Export::create([
'user_id' => $this->user_id,
'name' => 'Extract Data - '. Carbon::now()->toDateTimeString(),
'file' => $this->path,
'count' => count($this->clients),
]);
}
}