I have a problem with my code where I will export to CSV which has more than 100,000 data, here I have used chunk 2 times, the first is the getData variable, where this variable takes the AssetRepository function in another class, and the other is my use when foreach, if I load 1000 data using a limit, the data can be exported. Is it possible to load the data without changing max_execute_time on php.ini and only using chunk?
if you can, how can I optimize my code ?
in this case, I'm using PostgreSQL.
here is the code for AssetRepository.php
class AssetRepository
{
public $query = null;
private $trashed = false;
public static function query()
{
$repo = new AssetRepository();
$repo->query = DB::table('assets as a')
->select(
DB::raw("distinct a.id"),
"a.id",
"a.duration as duration",
DB::raw("COALESCE( NULLIF(a.qr_code,'') , 'QR Code Not Set' ) as qr_code"),
"a.material_no",
DB::raw("COALESCE( NULLIF(a.serial_no,'') , 'Serial No Not Set' ) as serial_no"),
"a.sbu_id",
"a.pop_id",
"a.building_id",
"a.type_id",
"asset_to_sid.cust_id",
"a.category_id",
"a.brand_id",
"a.model_id",
"a.id as id",
"b.name as model",
"b2.name as brand",
"p.name as pop",
"p2.name as sbu",
"q.name as building",
"a.updated_at",
"a.created_at",
"a.deleted_at",
'a.eos',
'a.eol',
"s.name as sts",
"c.name as category",
"a.app_code",
"a.name",
"a.status_approval as status_approval",
"a.approval_notes",
"a.approval_activities",
"a.habis_masa_garansi as habis_masa_garansi",
"permission_approval.action as action_approval",
DB::raw("CONCAT(u.first_name, ' ', u.last_name) as username"),
DB::raw("CONCAT(u2.first_name, ' ', u2.last_name) as username2"),
DB::raw("CONCAT(u3.first_name, ' ', u3.last_name) as approved_by"),
DB::raw("CASE WHEN q2.name is null THEN 'Not Set' ELSE q2.name END as room"),
DB::raw("CASE WHEN cast(a.installation_year as text) is null THEN 'Not Set' ELSE cast(a.installation_year as text) END as installation_year"),
DB::raw("CASE WHEN cast(b.mpls_hierarchy as text) is null THEN 'Not Set' ELSE cast(b.mpls_hierarchy as text) END as mpls_hierarchy"),
DB::raw("CASE WHEN cast(a2.name as text) is null THEN 'Not Set' ELSE cast(a2.name as text) END as rack"),
DB::raw("CASE WHEN cast(a.remark1 as text) is null THEN 'No Data' ELSE cast(a.remark1 as text) END as remark1"),
DB::raw("CASE WHEN cast(a.remark2 as text) is null THEN 'No Data' ELSE cast(a.remark2 as text) END as remark2"),
DB::raw("CASE WHEN cast(a.remark3 as text) is null THEN 'No Data' ELSE cast(a.remark3 as text) END as remark3"),
DB::raw("CASE WHEN cast(a.remark4 as text) is null THEN 'No Data' ELSE cast(a.remark4 as text) END as remark4"),
DB::raw("CASE WHEN cast(a.remark5 as text) is null THEN 'No Data' ELSE cast(a.remark5 as text) END as remark5"),
DB::raw("CASE WHEN cast(a.desc as text) is null THEN 'No Data' ELSE cast(a.desc as text) END as notes"),
DB::raw("CASE WHEN a.c_status = 1 THEN 'Complete' ELSE 'Not Complete' END AS complete"),
DB::raw("CASE WHEN a.c_status = 1 THEN 'btn-primary' ELSE 'btn-warning' END AS btn"),
DB::raw("CASE WHEN p.offline_sts = 1 THEN 'Offline' ELSE 'Online' END AS offline_sts"),
DB::raw("CASE WHEN p.offline_sts = 1 THEN 'btn-default' ELSE 'btn-info' END AS offline_btn"),
DB::raw("CASE WHEN p.offline_sts = 1 THEN 'disabled' ELSE 'enabled' END AS disableds")
)
->leftJoin('assets as a2', 'a.rack', '=', 'a2.id')
->join('kategoris as c', 'a.asset_category', '=', 'c.id')
->join('users as u', 'a.updated_by', 'u.id')
->join('users as u2', 'a.created_by', 'u2.id')
->leftJoin('users as u3', 'a.role_approval', 'u3.id')
->join('sbus as p', 'p.id', '=', 'a.pop_id')
->join('sbus as p2', 'p2.id', '=', 'a.sbu_id')
->leftJoin('pops as q', 'a.building_id', '=', 'q.id')
->leftJoin('pops as q2', 'a.room_id', '=', 'q2.id')
->leftJoin('brands as b', 'a.model_id', '=', 'b.id')
->leftJoin('permission_approval', 'a.permission_approval_id', '=', 'permission_approval.id')
->leftJoin('asset_to_sid', 'a.id', '=', 'asset_to_sid.asset_id')
->join('brands as b2', 'a.brand_id', '=', 'b2.id')
->join('statuses as s', 's.id', '=', 'a.status')
->leftJoin('statuses as ss', 'p.type', '=', 'ss.id')
->orderBy('a.updated_at', 'desc');
return $repo;
}
public function getQuery()
{
return $this->query ?? self::query();
}
public function get()
{
if (!$this->trashed) {
return $this->getQuery()->whereNull('a.deleted_at')->get();
}
return $this->getQuery()->get();
}
}
dan ini untuk export pada AssetController.php
public function exportAll(Request $request)
{
$data = AssetRepository::query(); //From AssetRepository Function
$headers = array(
'Content-Type' => 'text/csv',
'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
'Content-Disposition' => 'attachment; filename=export.csv',
'Expires' => '0',
'Pragma' => 'public',
);
$response = new StreamedResponse(function () use ($data) {
$handle = fopen('php://output', 'w');
$getData = $data->get();
$remark = Remark::all(['id','label','type']);
$remarkAsset = RemarkAsset::all(['asset_id','value','remark_id']);
$getHeader = array_keys((array)$getData[0]);
$newArray = array();
$setHeader = array();
foreach ($getHeader as $header) {
$setHeader[$header] = $header;
}
$remarkHeader = []; //result
foreach ($remark as $headerRemark) {
$remarkHeader[] = array(
'id' => $headerRemark['id'],
'label' => $headerRemark['label'],
'type' => $headerRemark['type']
);
$setHeader[$headerRemark['type']] = $headerRemark['type'];
}
$remarkAssets = [];
foreach ($remarkAsset as $assetRemark) {
$remarkAssets[] = (array)array(
'asset_id' => $assetRemark['asset_id'],
'value' => $assetRemark['value'],
'remark_id' => $assetRemark['remark_id']
);
}
array_push($newArray, (object)$setHeader);
// $coountData = count($getData) / 4;
$chunk = collect($getData);
$chunk->chunk(500);
foreach ($chunk as $data) {
$theKey=array_keys(array_combine(array_keys($remarkAssets), array_column($remarkAssets, 'asset_id')),$data->id);
foreach ($remarkHeader as $head) {
$countKey = count($theKey);
if ($countKey > 0) {
$valueRemark = '';
foreach ($theKey as $key) {
if ($remarkAssets[$key]['remark_id'] == $head['id']) {
$valueRemark = $remarkAssets[$key]['value'];
}
}
$data = (array)$data;
$data[$head['type']] = $valueRemark;
$data = (object)$data;
} else {
$data = (array)$data;
$data[$head['type']] = '';
$data = (object)$data;
}
}
array_push($newArray, $data);
}
$chunkArray = collect($newArray);
$chunkArray->chunk(500);
foreach ($chunkArray as $datas) {
if (is_object($datas))
$datas = (array)$datas;
fputcsv($handle, $datas);
}
fclose($handle);
}, 200, $headers);
return $response->send();
}
if necessary ignore AssetController.php it is the query used in my code
Maybe this query returns so many elements PHP spends most of the time just wrapping a Collection object around them. If you want to see how much time is spent on the query itself, you could run it directly on your PostgreSQL Server, the console (php artisan tinker) or use DB::listen in your code
public function exportAll(Request $request)
{
// PHP >= 7.4.0
DB::listen(fn($query) => dump($query->sql, $query->bindings, $query->time));
// PHP < 7.4.0
DB::listen(function ($query) { dump($query->sql, $query->bindings, $query->time); });
...
}
If the Collection wrapping is the issue, try using a LazyCollection. It's available since Laravel 6.0. You use it by calling $data->cursor() instead of $data->get().
A LazyCollection is basically an object you can iterate over and use some Collection methods on. They allow you to work with the data without the overhead of building a big Collection for X amount of rows.
I'll repost your exportAll function with some changes I think will positively impact performance.
public function exportAll(Request $request)
{
$data = AssetRepository::query(); //From AssetRepository Function
$headers = array(
'Content-Type' => 'text/csv',
'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
'Content-Disposition' => 'attachment; filename=export.csv',
'Expires' => '0',
'Pragma' => 'public',
);
$response = new StreamedResponse(function () use ($data) {
$handle = fopen('php://output', 'w');
/**
* Use a LazyCollection instead
* $getData = $data->get();
*/
$getData = $data->cursor();
$remark = Remark::all(['id','label','type']);
$remarkAsset = RemarkAsset::all(['asset_id','value','remark_id']);
/**
* Since we are using a LazyCollection,
* we can't treat $getData as an array directly.
*
* $getHeader = array_keys((array)$getData[0]);
*/
$getHeader = array_keys((array)$getData->get(0));
$newArray = array();
/**
* This can be achieved with array_combine
*
* $setHeader = array();
*
* foreach ($getHeader as $header) {
* $setHeader[$header] = $header;
* }
*/
$setHeader = array_combine($getHeader, $getHeader);
/**
* $remarkHeader is unnecesary. You can just call $remark->toArray() instead.
* Also, what you're trying to do with the following foreach can be done with
* a combination of array_merge and array_combine
*
* $remarkHeader = []; //result
*
* foreach ($remark as $headerRemark) {
* $remarkHeader[] = array(
* 'id' => $headerRemark['id'],
* 'label' => $headerRemark['label'],
* 'type' => $headerRemark['type']
* );
*
* $setHeader[$headerRemark['type']] = $headerRemark['type'];
* }
*/
$setHeader = array_merge(
$setHeader,
array_combine(
$remark->pluck('type')->toArray(),
$remark->pluck('type')->toArray()
)
);
/**
* Again, $remarkAssets is unnecessary. All you're doing with this loop
* is the same as calling $remarkAsset->toArray()
*
* $remarkAssets = [];
* foreach ($remarkAsset as $assetRemark) {
* $remarkAssets[] = (array)array(
* 'asset_id' => $assetRemark['asset_id'],
* 'value' => $assetRemark['value'],
* 'remark_id' => $assetRemark['remark_id']
* );
* }
*/
array_push($newArray, (object)$setHeader);
// $coountData = count($getData) / 4;
/**
* $getData is already a Collection. Here, you're telling PHP to rebuild it
* for no reason. For large collections, this adds a lot of overhead.
* You can already call the chunk method on $getData anyways.
* You could do $chunk = $getData->chunk(500) for example.
* It's not even necessary to make a new variable for it since you won't use
* $chunk again after this.
*
* $chunk = collect($getData);
* $chunk->chunk(500);
*
* Also, according to the docs you're not using chunk properly.
* https://laravel.com/docs/6.x/collections#method-chunk
* You're supposed to loop twice because the chunk method doesn't alter the collection.
* If you run
* $chunk->chunk(500)
* foreach($chunk as $data) { ... }
* You're still looping over the entire Collection.
* Since your code is not made to work with chunks, I'll leave it like that
*
* foreach ($chunk as $data) {
*/
foreach ($getData as $data) {
/**
* This seems to return an array of the keys of $remarkAssets
* where 'asset_id' is equal to $data->id.
* You can achieve this through Collection methods on $remarkAsset instead.
*
* $theKey = array_keys(
* array_combine(
* array_keys($remarkAssets),
* array_column($remarkAssets, 'asset_id')
* ),
* $data->id
* );
*
* Since there is no real need to return an array, I'll leave $theKey as a collection.
*/
$theKey = $remarkAsset->where('asset_id', $data->id)->keys();
/**
* Since $remarkHeader doesn't exist in this context, we use $remark instead
*
* foreach ($remarkHeader as $head) {
*
* Since $theKey is a collection, the count is obtained
* through the count() Collection method. Also, since you don't
* ever use $countKey again, you could inline it instead.
*
* $countKey = count($theKey);
*
* if ($countKey > 0) {
*/
foreach ($remark as $head) {
if ($theKey->count() > 0) {
$valueRemark = '';
foreach ($theKey as $key) {
/**
* Since $remark is a collection and $head an object
* the following if statement needs to be rewritten
*
* if ($remarkAssets[$key]['remark_id'] == $head['id']) {
* $valueRemark = $remarkAssets[$key]['value'];
* }
*/
if ($remark->get($key)->remark_id == $head->id) {
$valueRemark = $remark->get($key)->value;
}
}
/**
* $data being a stdClass, you can just set the property instead of
* going through the trouble of casting it as an array, setting a value
* and then re-casting it as an object.
*
* $data = (array)$data;
* $data[$head['type']] = $valueRemark;
* $data = (object)$data;
* } else {
* $data = (array)$data;
* $data[$head['type']] = '';
* $data = (object)$data;
*/
$data->{$head['type']} = $valueRemark;
} else {
$data->{$head['type']} = '';
}
}
array_push($newArray, $data);
}
$chunkArray = collect($newArray);
/**
* As explained earlier, your use of chunk() doesn't do anything.
* We can then safely remove this line.
*
* $chunkArray->chunk(500);
*/
foreach ($chunkArray as $datas) {
if (is_object($datas))
$datas = (array)$datas;
fputcsv($handle, $datas);
}
fclose($handle);
}, 200, $headers);
return $response->send();
}
public function exportAll(Request $request)
{
$data = AssetRepository::query(); //From AssetRepository Function
$headers = array(
'Content-Type' => 'text/csv',
'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
'Content-Disposition' => 'attachment; filename=export.csv',
'Expires' => '0',
'Pragma' => 'public',
);
$response = new StreamedResponse(function () use ($data) {
$handle = fopen('php://output', 'w');
$getData = $data->cursor();
$remark = Remark::all(['id','label','type']);
$remarkAsset = RemarkAsset::all(['asset_id','value','remark_id']);
$getHeader = array_keys((array)$getData->get(0));
$newArray = array();
$setHeader = array_combine($getHeader, $getHeader);
$setHeader = array_merge(
$setHeader,
array_combine(
$remark->pluck('type')->toArray(),
$remark->pluck('type')->toArray()
)
);
array_push($newArray, (object)$setHeader);
foreach ($getData as $data) {
$theKey = $remarkAsset->where('asset_id', $data->id)->keys();
foreach ($remark as $head) {
if ($theKey->count() > 0) {
$valueRemark = '';
foreach ($theKey as $key) {
if ($remark->get($key)->remark_id == $head->id) {
$valueRemark = $remark->get($key)->value;
}
}
$data->{$head['type']} = $valueRemark;
} else {
$data->{$head['type']} = '';
}
}
array_push($newArray, $data);
}
$chunkArray = collect($newArray);
foreach ($chunkArray as $datas) {
if (is_object($datas))
$datas = (array)$datas;
fputcsv($handle, $datas);
}
fclose($handle);
}, 200, $headers);
return $response->send();
}
You could also use Lazy Collections for Remark an RemarkAsset models like so
$remark = Remark::select('id','label','type')->cursor();
$remarkAsset = RemarkAsset::select('asset_id','value','remark_id')->cursor();