Laravel pass big data through a view, load time slow

Question

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

How much data are you passing and how long does it take? The network tab in Chrome DevTools will tell you something like *3.0 MB transferred in 2.4 sec*. — Pida, Sep 27 '19 at 08:47
50KB in 6seconds while in the live systems it takes 600ms, but seems that it takes like a couple of seconds before to load the page, is the delay between the click to go to the page and the first load that is slow — Seba, Sep 27 '19 at 09:02
You are not passing data to the view. You are not returning a view (i.e. `return view(...)`). What you are doing is redirect the user to another route, passing the data through the user session to your other route. Depending on your session store, this can of course be extremly slow. But you should invest some time into finding out where the most time is spent on this action, I expect SQL queries due to the high amount of round-trips to the database(es). — Namoshek, Sep 27 '19 at 09:09
Take a look at TTFB, if it takes a lot of time try to figure out what causing it: fetching data from DB or view rendering. Use microtime() for example. Play with data caching or even cache rendered view (if suitable) — Constantine, Sep 27 '19 at 09:12
@Namoshek I return a view, I've updated my code, that redirect is if the client doesn't belong to that city so you redirect to the correct city but if you are in the correct path I just return the `view` — Seba, Sep 27 '19 at 09:14
In that case my latter statement is what you should do next. Find out what takes the actual time - querying the database or rendering the response. And compare these numbers with the ones of your browser, it takes time to render the HTML as well. — Namoshek, Sep 27 '19 at 09:18
is there a way to don't use ajax and render all that data a second/two later? so the html will be rendered without data in the chart and then write the data into the charts — Seba, Sep 27 '19 at 09:21

score 2 · Accepted Answer · answered Sep 27 '19 at 10:04

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.

I know how to use AJAX but in this project I wanted to don't use it, also because is pretty static the dashboard there is no interaction so once I pass everything inside, the data doesn't change, not really need to use AJAX requests. I'll try to take a look where the slowness it came, and yes I know the code is quite complex and there are a lot of join query to get parts of the data I need — Seba, Sep 27 '19 at 10:30
I removed all the map functions and I decrease the TTFB, seems that map functions are quite slow — Seba, Sep 27 '19 at 17:47

Laravel pass big data through a view, load time slow

1 Answers1