我的代码有一个问题，我将导出到超过100，000个数据的CSV，这里我已经使用了2次，第一个是变量，其中这个变量在另一个类中采用函数，另一个是我使用时，如果我使用a加载1000个数据，则可以导出数据。是否可以在不更改和仅使用的情况下加载数据？如果可以，我该如何优化我的代码？chunkgetDataAssetRepositoryforeachlimitmax_execute_timephp.inichunk在这种情况下，我使用的是PostgreSQL。这是代码AssetRepository.phpclass 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"), )
4 回答
偶然的你
TA贡献1841条经验 获得超3个赞
您可以调用以从执行的其余部分中删除时间限制，也可以在循环的每次迭代中调用（例如）以将计时器重置 n 秒。set_time_limit(0)set_time_limit(n)
https://www.php.net/manual/en/function.set-time-limit.php
幕布斯6054654
TA贡献1876条经验 获得超7个赞
也许这个查询返回了这么多元素，PHP大部分时间都花在了将对象包裹在它们周围。如果要查看在查询本身上花费了多少时间，可以直接在 控制台 （） 上运行它，或者在代码中使用CollectionPostgreSQL Serverphp artisan tinkerDB::listen
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); });
...
}
如果包装是问题所在，请尝试使用 .它自 .您可以通过调用而不是 来使用它。CollectionLazyCollectionLaravel 6.0$data->cursor()$data->get()
A基本上是一个对象，您可以迭代并使用一些方法。它们允许您处理数据，而无需为X行量构建大量行的开销。LazyCollectionCollectionCollection
有关惰性集合的更多信息
我将重新发布您的函数，并进行一些我认为会对性能产生积极影响的更改。exportAll
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();
}
您还可以使用懒惰的集合来备注和备注资产模型，就像这样
$remark = Remark::select('id','label','type')->cursor();
$remarkAsset = RemarkAsset::select('asset_id','value','remark_id')->cursor();
- 4 回答
- 0 关注
- 326 浏览
添加回答
举报
0/150
提交
取消