Search code examples
laraveleloquentmulti-database

Laravel pass big data through a view, load time slow

I need to pass quite a big amount of data through the view in Laravel and it seems to take quite a look, can someone advice about the correct way to improve this code/ pass this amount of data through a view?

from my PC the loading of this page is very slow, in the live system once is on the server seems to take less time but still, I think is not fast enough.

I saw there is this dependency that should help with a live system composer install --optimize-autoloader --no-dev but I haven't installed it yet, will help my speed as I have a lot of classes to initialize?

The first piece of code it gets the connection for each city, we are using a multi-schema PostgreSQL DB, so for each URL/city1 URL/city2 etc it needs to find from which DB take the data.

/**
 * Create objects that we will need to query
 */

$this->Response = new Response;
$this->Response->initialize($connection);
$this->Responder = new Responder;
$this->Responder->initialize($connection);
$this->Interests = new lutInterest;
$this->Interests->initialize($connection);
$this->Issue = new lutIssue;
$this->Issue->initialize($connection);
$this->Time = new lutTime;
$this->Time->initialize($connection);

then I need to check if the schemeid parameter is present and its value and then run the related queries from the DB

/**
 * check the schemeid paramenter
 * Do the queries
 */
if ($_GET['schemeid'] == 0)
{
    $totalIssues = $this->Response->orderby('issueid')->groupby('issueid')->select('issueid', DB::raw('count(*) as total'))->get();
    $totalTimes = $this->Response->orderby('timeid')->groupby('timeid')->select('timeid', DB::raw('count(*) as total'))->get();
    $totalParticipants = DB::connection($connection)->table('responder_interests')->groupby('interestid')->orderby('interestid')->select('interestid', DB::raw('count(*) as total'))->get();
    $totalResponses = $this->Response->select(DB::raw('count(responseid) as total'),DB::raw("EXTRACT('year' FROM created_at) as year,EXTRACT('month' FROM created_at) as month"))->groupby('year','month')->orderby('year','asc')->orderBy('month','asc')->get();
    $totalRespArray = $this->Response->select(DB::raw('count(*) as total'))->get();
    $totalresponders = $this->$responders->count();
}
else
{
    $responders = $this->Response->where('scheme_id',$_GET['schemeid'])->groupby('responderid')->orderby('responderid')->select('responderid')->get();
    $respondersIDs = $responders->map(function ($data) { return $data->responderid; });

    $totalIssues = $this->Response->orderby('issueid')->groupby('issueid')->where('scheme_id', $_GET['schemeid'])->select('issueid', DB::raw('count(*) as total'))->get();
    $totalTimes = $this->Response->orderby('timeid')->groupby('timeid')->where('scheme_id', $_GET['schemeid'])->select('timeid', DB::raw('count(*) as total'))->get();
    $totalParticipants = DB::connection($connection)->table('responder_interests')->groupby('interestid')->orderby('interestid')->wherein('responderid',$respondersIDs)->select('interestid', DB::raw('count(*) as total'))->get();
    $totalResponses = $this->Response->where('scheme_id',$_GET['schemeid'])->select(DB::raw('count(responseid) as total'),DB::raw("EXTRACT('year' FROM created_at) as year,EXTRACT('month' FROM created_at) as month"))->groupby('year','month')->orderby('year','asc')->orderBy('month','asc')->get();
    $totalRespArray = $this->Response->where('scheme_id',$_GET['schemeid'])->select(DB::raw('count(*) as total'))->get();
    $totalresponders = $responders->count();
}

and final run a couple of function to get the data properly formatted and be able to displayed in the charts correctly.

/**
 * Convert issue data
 */
$categoryData = $totalIssues->map(function ($data) { return $data->total; });
$categoryLabels = $totalIssues->map(function ($data) { return $this->Issue->where('issueid',$data->issueid)->first()->shortdesc; });
$categoryBorder = $totalIssues->map(function ($data) { return $this->Issue->where('issueid',$data->issueid)->first()->color; });
$categoryColors = $categoryBorder->map(function ($data) { return implode('', [str_replace('rgb', 'rgba' ,explode(" ", $data)[0]),explode(" ", $data)[1], str_replace(')', ',0.2)' ,explode(" ", $data)[2])] );});
$categoryColorsHover = $categoryBorder->map(function ($data) { return implode('', [str_replace('rgb', 'rgba' ,explode(" ", $data)[0]),explode(" ", $data)[1], str_replace(')', ',0.5)' ,explode(" ", $data)[2])] );});

/**
 * Convert time data
 */
$timeData = $totalTimes->map(function ($data) { return $data->total; });
$timeLabels = $totalTimes->map(function ($data) { return $this->Time->where('timeid',$data->timeid)->first()->shortdesc; });
$timeBorder = $totalTimes->map(function ($data) { return $this->Time->where('timeid',$data->timeid)->first()->color; });
$timeColors = $timeBorder->map(function ($data) { return implode('', [str_replace('rgb', 'rgba' ,explode(" ", $data)[0]),explode(" ", $data)[1], str_replace(')', ',0.2)' ,explode(" ", $data)[2])] );});
$timeColorsHOver = $timeBorder->map(function ($data) { return implode('', [str_replace('rgb', 'rgba' ,explode(" ", $data)[0]),explode(" ", $data)[1], str_replace(')', ',0.5)' ,explode(" ", $data)[2])] );});

/**
 * Convert the interests data
 */
$participantData = $totalParticipants->map(function ($data) { return $data->total; });
$participantLabels = $totalParticipants->map(function ($data) { return $this->Interests->where('interestid',$data->interestid)->first()->shortdesc; });
$participantBorder = $totalParticipants->map(function ($data) { return $this->Interests->where('interestid',$data->interestid)->first()->color; });
$participantColors = $participantBorder->map(function ($data) { return implode('', [str_replace('rgb', 'rgba' ,explode(" ", $data)[0]),explode(" ", $data)[1], str_replace(')', ',0.2)' ,explode(" ", $data)[2])] );});
$participantColorsHover = $participantBorder->map(function ($data) { return implode('', [str_replace('rgb', 'rgba' ,explode(" ", $data)[0]),explode(" ", $data)[1], str_replace(')', ',0.5)' ,explode(" ", $data)[2])] );});

/**
 * Convert the responses data
 */
$responseData = $totalResponses->map(function ($data) { return $data->total; });
$responseLabels = $totalResponses->map(function ($data) { return date("M", mktime(0, 0, 0, $data->month,1)).' | '.substr($data->year,2); });

/**
 * Convert the total amount of responses data
 */
$totalResp = $totalRespArray->map(function ($data) { return $data->total; });

/**
 * send the issues and times for the popup
 */

$lutissue = $this->Issue->select('issueid', 'description')->orderby('sort')->get();
$luttime = $this->Time->select('timeid', 'description')->orderby('sort')->get();

pass everything into the view

return view('dasboard')->with(compact('title', 'schemeDocuments', 'schemes',
                'categoryData', 'categoryLabels', 'categoryBorder', 'categoryColors', 'categoryColorsHover','timeData', 'timeLabels',
                'timeBorder', 'timeColors', 'timeColorsHOver', 'participantData', 'participantLabels', 'participantBorder', 'participantColors',
                'participantColorsHover', 'responseData','responseLabels','totalResp', 'lutissue', 'luttime', 'totalresponders'));
Solution

  • 50 KB of data (as mentioned in a comment) is very little, so transferring data from server to client is not the bottleneck in your case. Your data is not as "big" as the title of your question suggests.

    I'd put this code inside the boot method in app\Providers\AppServiceProvider.php. Then the time each DB query takes will be logged in storage\logs.

    \DB::listen(function ($query) {
    \Log::info($query->time . ' milliseconds for ' . $query->sql);
});

    You can also calculate and log the execution time for arbitrary pieces of code:

    $start = microtime(true);

// This may be anything between a single line of code and your whole script

\Log::info(microtime(true) - $start); // This is in seconds

    Find out what's taking so long, then try try to optimize the query (or other piece of code).

    You also ask if there's an alternative to using AJAX to have HTML rendered before the data is loaded. I strongly suggest you learn how to do so using AJAX, but I also think that in your case there may be ways to speed up your queries so that you can simply wait for the data and load everything in a single request.

    Your code is quite complex, I'd suggest you consider splitting it up into smaller functions and using more comments, though that that won't make your code run faster.