How to avoid Maximum execution time of 60 seconds exceeded Laravel without change php.ini max_execution_time

Question

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

Solution 1

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.

• More information on Lazy Collections

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();
}

Without all the comments

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();

Solution 2

You can call set_time_limit(0) to remove the time limit from the rest of the execution, or you can call set_time_limit(n) in each iteration of a loop (for example) to reset the timer for n more seconds.

https://www.php.net/manual/en/function.set-time-limit.php

Solution 3

It's better to do these long running tasks asynchronously. In Laravel you can use queues for that. As queues run on CLI you could configure a different max_execution_time for that. If you would prefer to keep the execution time the same, then you should try splitting up the task you're performing into multiple parts. If each of those parts does not exceed 1 minute, then you're good to go.

Comments

[user1669496]

Have you investigated the query further to determine how long that takes to run using explain? That appears to me to be the most likely culprit of your long running script.

[Gio Isa]

Maybe I will fix the query for export, and see how much time is spent executing this query

[IGP]

I don't think your code works as you think it does in its current condition. You're not using chunk() properly. In fact, it's not doing anything.