Level 102
Excel can have a maximum of just over 1 million rows, so you cannot add 5 million rows to it. (the max is 1,048,576 rows I believe)
Jan 11, 2023
7
Level 2
My eloquent query is getting failed for rows over 5 Million
I am running a background job to export the rows.
My query is
$chain = [];
$loop = 0;
Submission::with(['referredBy', 'referredTo'])
->where('project_id', $this->project->id)
->orderBy('position', 'asc')
->chunk(64000, function ($submissions) use (&$loop, &$chain) {
// Add a job to the batch for each chunk of submissions
// logger('chunk: ' . $loop);
$keys = $submissions->pluck('id');
$chain[] = new ExportSubmissionsFastExcel($this->waitlist, $keys, true, $this->uniqueKey, $loop);
$loop++;
});
// logger('chain: ' . count($chain));
Bus::batch($chain)->then(function (Batch $batch) {
// all jobs finished successfully...
})->catch(function (Batch $batch, Throwable $e) {
// First batch job failure detected...
// logger('one of the jobs in the batch failed...');
})->finally(function (Batch $batch) {
// The batch has finished executing...
// logger('the batch finished executing...');
})->name($this->batchName)->onQueue('export')->dispatch();
How do I optimize it?
Level 2
@Sinnbeck That is why I am chunking for 64k rows in the excel file. Then combine it all in a zip.
Level 102
@navneet Ah sorry missed that. then explain how it is failing please :)
Level 2
After all exports, I run the bus and complete jobs like this. It works for 500k submissions.
Bus::batch($chain)->then(function (Batch $batch) {
// all jobs finished successfully...
$batchName = $batch->name;
// extract key and waitlist id from batch name
$batchName = explode('-', $batchName);
$uniqueKey = $batchName[1];
$public_key = $batchName[2];
ExportWaitlistSubmissionsCompleted::dispatch($public_key, $uniqueKey)->onQueue('export')->delay(now()->addSeconds(1));
})->catch(function (Batch $batch, Throwable $e) {
// First batch job failure detected...
// logger('one of the jobs in the batch failed...');
})->finally(function (Batch $batch) {
// The batch has finished executing...
// logger('the batch finished executing...');
})->name($this->batchName)->onQueue('export')->dispatch();
Level 102
Btw. Why are your loading 'referredBy', 'referredTo'? You dont seem to use it? You just pass the ID's to the export?
Level 2
@Sinnbeck 5 Million rows are not going in the same excel file.
On the logs, the query is not getting completed. It just keeps loading it. It does not even hit the Bus::batch($chain) command.
Level 2
@Sinnbeck You are right, these are not required as of now. I will remove those.
Please or to participate in this conversation.