我的代碼有一個問題，我將導(dǎo)出到超過100，000個數(shù)據(jù)的CSV，這里我已經(jīng)使用了2次，第一個是變量，其中這個變量在另一個類中采用函數(shù)，另一個是我使用時，如果我使用a加載1000個數(shù)據(jù)，則可以導(dǎo)出數(shù)據(jù)。是否可以在不更改和僅使用的情況下加載數(shù)據(jù)？如果可以，我該如何優(yōu)化我的代碼？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貢獻(xiàn)1841條經(jīng)驗 獲得超3個贊
您可以調(diào)用以從執(zhí)行的其余部分中刪除時間限制，也可以在循環(huán)的每次迭代中調(diào)用（例如）以將計時器重置 n 秒。set_time_limit(0)set_time_limit(n)
https://www.php.net/manual/en/function.set-time-limit.php
幕布斯6054654
TA貢獻(xiàn)1876條經(jīng)驗 獲得超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); });
...
}
如果包裝是問題所在，請嘗試使用 .它自 .您可以通過調(diào)用而不是 來使用它。CollectionLazyCollectionLaravel 6.0$data->cursor()$data->get()
A基本上是一個對象，您可以迭代并使用一些方法。它們允許您處理數(shù)據(jù)，而無需為X行量構(gòu)建大量行的開銷。LazyCollectionCollectionCollection
有關(guān)惰性集合的更多信息
我將重新發(fā)布您的函數(shù)，并進(jìn)行一些我認(rèn)為會對性能產(chǎn)生積極影響的更改。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();
}
您還可以使用懶惰的集合來備注和備注資產(chǎn)模型，就像這樣
$remark = Remark::select('id','label','type')->cursor();
$remarkAsset = RemarkAsset::select('asset_id','value','remark_id')->cursor();
- 4 回答
- 0 關(guān)注
- 331 瀏覽
添加回答
舉報
0/150
提交
取消