Level 80
Aug 4, 2020
22
Level 13
What is the best way to export 1 million records
its took 10 minutes for 1 millions records dowload so how to reduce time for 1 click to downlaod in seconds
public function query()
{
$query = Employee::query()->select($this->getcol())
->leftJoin('employee_bank_details', function($join) {
$join->on('employees.id','=','employee_bank_details.employee_id')
->where('employee_bank_details.pay_type','=',4);
})
->join('company_onboarding_form1 as cf','employees.employer_id','cf.id')
->leftJoin('cities','employees.employee_city_id','cities.id')
->leftJoin('states','employees.employee_state_id','states.id')
->leftJoin('countries','employees.employee_country_id','countries.id')
->leftJoin('cities as ccity','cf.company_city','ccity.id')
->leftJoin('states as cstate','cf.company_state','cstate.id')
->where('employees.employer_id',$this->employer_id);
return $query;
}
controller is here
public function exportToExcel(Request $request, $companyOnboardingId)
{
ini_set('max_execution_time', 300);
$cdt = Carbon ::now();
$export = 'xls';
$fileName = 'company-employees'.$companyOnboardingId.'-'.$cdt->format('YmdHis');
return Excel::download(new CompanyEmployeeExcelExport($companyOnboardingId,$searchArray), $fileName.'.'.$export);
}
Level 13
@martinbean thank you for reply i try csv file then it take 15 second for 10k records but i want maximum 3 to 5 second to dowload file how to do it
public function exportToExcel(Request $request, $companyOnboardingId)
{
ini_set('max_execution_time', '-1');
$cdt = Carbon ::now();
$export = 'CSV';
$fileName = 'company-employees'.$companyOnboardingId.'-'.$cdt->format('YmdHis');
return Excel::download(new CompanyEmployeeExcelExport($companyOnboardingId,$searchArray), $fileName.'.'.$export);
}
Level 80
@van1310 I told you how to do it in my previous reply.
Level 13
@martinbean but my client requirment is xls format so how can i do it?
Level 80
@van1310 Also, don’t create multiple topics for the same issue. You already had this thread here: https://laracasts.com/discuss/channels/laravel/optimize-laravel-export-time
1 like
Level 13
@martinbean ok sorrry for that
Level 6
i try csv file then it take 15 second for 10k records but i want maximum 3 to 5 second to dowload file how to do it
Going from 10 minutes down to 3 to 5 seconds seems unlikely for a million records from a query that has eight joins on it.
1 like
Level 13
@tinfoilman so how to do it
Level 122
Its an unreasonable objective, besides xls only supports 64k rows
2 likes
Level 102
Create the file beforehand (every day at midnight?) and just have the user download the file from then
1 like
Level 13
Level 122
Why not just time the query. It will be a fraction of a second. Writing to a file is slow.
If your query is slow then you are probably missing some required indexes. If you don't know how long the query takes then that is a worry
1 like
Level 13
@snapey sir when i try in database this query it will work and output will come in a second then actaully my file is slow i think so how to find out my file why slow working?
Level 122
Do what @sinbeck suggests and create the file in advance.
Perhaps you could append just the latest rows if you need an up to date report midday.
1 like
Level 13
Level 102
Great, then do as we already suggested.
- Create file at midnight with all records up to that time
- When user requests file, get all records for that day and append them to the file
- Push the file to the user
1 like
Level 6
@van1310 how big was the file size when you got it to work in 10 minutes?
We don't know your use case, but it seems like a bad situation to need to export a million records so quickly. You're just not going to be able to write a file that quickly and send it over. It seems to defeat the purpose of using a database in the first place. If you must have data in excel, it would be better if you could just get away with only exporting the records that have changed, then have the end user overwrite the old data with new as needed.
1 like
Level 80
Hi. I need to fly from the UK to US in 10 minutes. How to do it?
1 like
Level 13
@martinbean @tinfoilman ok sir i understand but my client said in one click export whatever data into database sir so how can i understand to my client its not possible?
Level 75
Print out this post here, and show the replies.
1 like
Level 122
We are saying it is possible.
Run the export at night with a cron job. When the user clicks on the export, just get the records for today and APPEND them to the previously created file. Send the file to the user.
If they don't need it to be up to the minute then just download the latest file created in batch.
You have to be realistic about what is possible. Try creating an excel file (with excel), add a million rows and a) see how long it takes to save and b) how impossible it is to work with (because of the size)
2 likes
Level 122
or,
client said in one click export
client clicks button, your page says thankyou, your file is being prepared and will be emailed to you when it is ready.
There are many on-line services that use this approach. If you have an amazon affiliate account, and ask for a report, they tell you its queued and to come back later when it is ready.
6 likes
Please or to participate in this conversation.