Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

eggplantSword's avatar

Improve loading time

Hi, I have this page that is pretty information heavy it contains 3 elements: some info cards, some filters and a map component. Depending on the filters and user role / permissions all the information changes especially the map component info which has two states: current locations where you see the active users last location or the user route where you follow one users movement through the day. The loading time is cra-zy like people are complaining the site doesn't work slow.

Here are some photos

https://ibb.co/Tcyzbd8 current locations -> this is the default information on the page https://ibb.co/X7y9LtC following specific user's movement

I'm using vue2 + inertia.js :)

Here is my method. I know it's huge but I'm not sure how to make it better. I'm thinking to move the different parts to different methods and use axios to get it async but I'm not sure if there is a better way.

    public function index(Request $request)
    {
        $remove_these_roles = ['Super Administrador', 'Back Office', 'Cliente'];

        // Users
        $users = User::query();
        $users->select(['id as value', 'name as label', 'is_active']);
        $users->whereHas('roles', fn ($q) => $q->whereNotIn('name', $remove_these_roles));

        $locations = Location::query();
        $locations->select('user_id')->selectRaw('MAX(datetime) AS "datetime"');
        $locations->groupBy('user_id');
        $locations->whereHas('user', function ($query) use ($remove_these_roles) {
            $query->isActive();
            $query->whereHas('roles', fn ($q) => $q->whereNotIn('name', $remove_these_roles));
        });

        // Sale Points Stats
        $countSalePointCheckOuts = SalePointCheckOut::query()
            ->whereHas('user.roles', fn ($q) => $q->whereNotIn('name', $remove_these_roles));
        $countSalePointCheckOuts->selectRaw(
            "SUM(CASE WHEN sale_point_entry = ? AND sale_point_exit = ? THEN 1 END) AS visited",
            [true, true]
        );
        $countSalePointCheckOuts->selectRaw(
            "SUM(CASE WHEN sale_point_entry = ? AND sale_point_exit = ? THEN 1 END) AS in_progress",
            [true, false]
        );
        $countSalePointCheckOuts->selectRaw(
            "SUM(CASE WHEN sale_point_entry = ? AND sale_point_exit = ? THEN 1 END) AS not_visited",
            [false, false]
        );
        $countSalePointCheckOuts->selectRaw(
            "AVG(CASE WHEN sale_point_entry = ? AND sale_point_exit = ? THEN TIMESTAMPDIFF(MINUTE, date_entry, date_exit) END) as avg_time",
            [true, true]
        );
        $countSalePointCheckOuts->selectRaw("COUNT(sale_point_check_outs.id) AS total_stops");

        // User Movement
        $userMovement =  Location::select('*')->selectRaw('ROW_NUMBER() OVER (PARTITION BY user_id, DATE(datetime), CAST(latitude as decimal(13,3)), CAST(longitude as decimal(13,3)) ORDER BY datetime DESC) AS "row_number"');
        $userMovement->where('precision', '<', 100);

        // User Sale Point Check Outs
        $userRouteCheckOuts = SalePointCheckOut::with('salePoint.salePoint', 'route', 'salePointRoute')
            ->whereHas('user.roles', fn ($q) => $q->whereNotIn('name', $remove_these_roles));
        $userRouteCheckOuts->select('sale_point_check_outs.*');
        $userRouteCheckOuts->leftJoin('sale_point_route', function ($join) {
            $join->on('sale_point_route.sale_point_id', '=', 'sale_point_check_outs.sale_point_id');
            $join->on('sale_point_route.route_id', '=', 'sale_point_check_outs.route_id');
        });
        $userRouteCheckOuts->orderBy('sale_point_route.priority');

        // Filters
        if (!$request->user()->can('gps-read') && $request->user()->can('gps-read-team')) {
            $users->whereRelation('team', 'user_id', '=', $request->user()->id)
                ->orWhereHas('teams', function ($query) use ($request) {
                    $query->whereIn('teams.id', Team::query()->where('user_id', $request->user()->id)->select('id'));
                });
            $locations->whereRelation('locationSalePointCheckOut.user.team', 'id', '=', $request->user()->id)
                ->orWhereHas('locationSalePointCheckOut.user.teams', function ($query) use ($request) {
                    $query->whereIn('teams.id', Team::query()->where('user_id', $request->user()->id)->select('id'));
                });
            $userMovement->whereRelation('locationSalePointCheckOut.user.team', 'id', '=', $request->user()->id)
                ->orWhereHas('locationSalePointCheckOut.user.teams', function ($query) use ($request) {
                    $query->whereIn('teams.id', Team::query()->where('user_id', $request->user()->id)->select('id'));
                });
            $userRouteCheckOuts->whereRelation('user.team', 'user_id', '=', $request->user()->id)
                ->orWhereHas('user.teams', function ($query) use ($request) {
                    $query->whereIn('teams.id', Team::query()->where('user_id', $request->user()->id)->select('id'));
                });
            $countSalePointCheckOuts->whereRelation('user.team', 'user_id', '=', $request->user()->id)
                ->orWhereHas('user.teams', function ($query) use ($request) {
                    $query->whereIn('teams.id', Team::query()->where('user_id', $request->user()->id)->select('id'));
                });
        }

        if ($request->filled('user_id')) {
            $locations->where('user_id', $request->input('user_id'));
            $userMovement->where('user_id', $request->input('user_id'));
            $userRouteCheckOuts->where('user_id', $request->input('user_id'));
            $countSalePointCheckOuts->where('user_id', $request->input('user_id'));
        }

        if ($request->filled('user_roles')) {
            $users->whereHas('roles', fn ($query) => $query->whereIn('id', $request->input('user_roles')));
            $locations->whereHas('user.roles', fn ($query) => $query->whereIn('id', $request->input('user_roles')));
            $userMovement->whereHas('user.roles', fn ($query) => $query->whereIn('id', $request->input('user_roles')));
            $userRouteCheckOuts->whereHas('user.roles', fn ($query) => $query->whereIn('id', $request->input('user_roles')));
            $countSalePointCheckOuts->whereHas('user.roles', fn ($query) => $query->whereIn('id', $request->input('user_roles')));
        }

        $startDate = now()->setTimezone(tenant('timezone'))->startOfDay()->utc();
        $endDateStartOfDay = now()->setTimezone(tenant('timezone'))->startOfDay()->utc();
        $endDateEndOfDay = now()->setTimezone(tenant('timezone'))->endOfDay()->utc();

        if ($request->filled('date_range.start', 'date_range.end')) {
            $startDate = $request->date('date_range.start')->shiftTimezone(tenant('timezone'))->startOfDay()->utc();

            $endDateStartOfDay = $request->date('date_range.end')->shiftTimezone(tenant('timezone'))->startOfDay()->utc();
            $endDateEndOfDay = $request->date('date_range.end')->shiftTimezone(tenant('timezone'))->endOfDay()->utc();
        }

        $locations->where('datetime', '>=', $endDateStartOfDay);
        $locations->where('datetime', '<=', $endDateEndOfDay);

        $userMovement->where('datetime', '>=', $endDateStartOfDay);
        $userMovement->where('datetime', '<=', $endDateEndOfDay);

        $userRouteCheckOuts->whereDate('date', '>=', $startDate);
        $userRouteCheckOuts->whereDate('date', '<=', $endDateStartOfDay);

        $countSalePointCheckOuts->whereDate('date', '>=', $startDate);
        $countSalePointCheckOuts->whereDate('date', '<=', $endDateStartOfDay);

        // Current Locations
        $currentLocations = Location::with('user.roles');
        $currentLocations->whereHas('user.roles', fn ($q) => $q->whereNotIn('name', $remove_these_roles));
        $currentLocations->joinSub($locations, 'last_locations', function (JoinClause $query) {
            $query->on('last_locations.user_id', '=', 'locations.user_id');
            $query->on('last_locations.datetime', '=', 'locations.datetime');
        });

        return Inertia::render(
            'Tenant/GPS/GpsIndex',
            [
                'user_id' => $request->input('user_id'),

                'roles' => Role::query()->has('users.salePointCheckOuts')->whereNotIn('name', $remove_these_roles)->get(['id', 'name']),

                'users' => $users->orderByDesc('is_active')->orderBy('label')->get(),

                'chart_info' => Inertia::lazy(function () use (&$countSalePointCheckOuts, $endDateEndOfDay) {
                    $countSalePointCheckOuts = $countSalePointCheckOuts->first();

                    return [

                        'visited' => $countSalePointCheckOuts?->visited ?: 0,

                        'last_visited' => $endDateEndOfDay,

                        'in_progress' => $countSalePointCheckOuts?->in_progress ?: 0,

                        'last_in_progress' => $endDateEndOfDay,

                        'not_visited' => $countSalePointCheckOuts?->not_visited ?: 0,

                        'last_not_visited' => $endDateEndOfDay,

                        'avg_time' => $countSalePointCheckOuts?->avg_time ?: 0,

                        'last_avg_time' => $endDateEndOfDay,

                        'total' => $countSalePointCheckOuts?->total_stops ?: 0
                    ];
                }),

                'current_locations' => function () use ($currentLocations) {
                    $locations = $currentLocations->get();

                    return [

                        'locations' => $locations,

                        'counts' => $locations->countBy(fn ($location) => $location->user->roles[0]->name),

                        'totals' => Role::query()->withWhereHas('users', fn ($query) => $query->isActive())->withCount(['users' => fn ($query) => $query->isActive()])->get()
                    ];
                },

                'user_route' => Inertia::lazy(fn () => Location::query()->fromSub($userMovement, 'locations')->where('row_number', 1)->orderByDesc('datetime')->get()),

                'user_route_check_outs' => Inertia::lazy(fn () => $userRouteCheckOuts->with('salePoint.salePoint')->get()),

                'chart_visited' => Inertia::lazy(fn () => $userRouteCheckOuts->with('user', 'justification', 'salePoint')->where('sale_point_entry', true)->where('sale_point_exit', true)->get()),

                'chart_progress' => Inertia::lazy(fn () => $userRouteCheckOuts->with('user', 'justification', 'salePoint')->where('sale_point_entry', true)->where('sale_point_exit', false)->get()),

                'chart_not_visited' => Inertia::lazy(fn () => $userRouteCheckOuts->with('user', 'justification', 'salePoint')->where('sale_point_entry', false)->where('sale_point_exit', false)->get()),
            ]
        );
    }

If you made it this far, thanks. Any suggestions are welcome.

0 likes
5 replies
jlrdw's avatar
  • Have you tried some techniques that @tray2 suggest, like making database views of some queries.
  • Any n+1 problems
  • if you cannot cache images (different each time) naturally it's slower to load.
  • And indexes are good

Try loading a little at a time for test.

Load just the table and see load time. Next load table and one of the images. Etc.

It might be the map slowing you down, but just a guess.

Do you really need all that. How about a button to let the user see more when ready.

Just suggestions.

1 like
Tray2's avatar

@jlrdw @msslgomez here is the link to the post about using views in Laravel.

https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application

I throw in this one as a bonus, it's about using properly formed foreign keys, and that is also a great performance booster.

https://tray2.se/posts/properly-formed-foreign-keys-are-your-best-friends

I also highly recommend taking a look at this series about Eloquent Performance Patterns.

https://laracasts.com/series/eloquent-performance-patterns.

Most if not all data processing should be performed in the database, if you are looping through collections in your code other than for display purposes you should try to let the database handle it, the database is always faster than php. This is of course for fetching data, when it comes to inserting, updating data, then you can't really do much other than loop through it. Deleting data on the other hand can be handled in the database with the use of an array that is sent to it from php.

1 like
Tray2's avatar

@msslgomez It depends a little, one case would be if the query is to be used in multiple places, then a view would keep your code dryer. Another example might be where you have several tables that you join together in multiple places, ie show and edit usually uses the same query. Then the one that I think is most important length and complexity.

This example from my blog post explains it pretty well I think.

Record::query()
  ->join('artists','records.artist_id', '=', 'artists.id')
  ->join('genres', 'records.genre_id', '=', 'genres.id')
  ->join('formats', 'records.format_id', '=', 'formats.id')
  ->select('records.*',
           'artists.name AS artist',
           'genres.name AS genre',
           'formats.name AS format')
  ->orderBy('artists.name')
  ->orderBy('records.released')
  ->get();

While this query isn't that bad, this one is still way better.

RecordIndexView::query()
  ->orderBy('artist')
  ->orderBy('released')
  ->get();

Another good use case for a view is when you have something like this

  • Books
  • Records
  • Movies
  • Games

and you want to show the five latest additions from a combination of those four tables.

Please or to participate in this conversation.