Debugbar shows thousands gate queries

Yes you should consider caching these results. Some of the roles/permisison packages already do this for you. santigarcor/laratrust is one of them.

Or you could write your own little piece of code. If you keep an array of roles and permissions in a cache key it would be more efficient. You can retrieve all roles and all permissions of the role with 2 queries only. And you should only invalidate the cache when the user is attached/detached from a role or permissions changes.

Hello @wizjo, like click said you can try to cache it, but I think eager loading is what you need. So here is the link to the documentation of Laravel, I think if you read this it will help you alot with your queries.

https://laravel.com/docs/5.7/eloquent-relationships#eager-loading

Just for your information the problem is that you run a query inside a foreach, this is done by this part: $this->role->name.

Good luck debugging.

Debugbar shows only 6 queries, so I`m not sure if this is clue of problem:

select * from `users` where `id` = '1' limit 1 //public\index.php:54

select * from `roles` where `roles`.`id` in ('1') //public\index.php:54

select `permissions`.*, `permission_role`.`role_id` as `pivot_role_id`, `permission_role`.`permission_id` as `pivot_permission_id` from `permissions` inner join `permission_role` on `permissions`.`id` = `permission_role`.`permission_id` where `permission_role`.`role_id` in ('1') //public\index.php:54

select * from `users`//app\Http\Controllers\UserController.php:24

select * from `roles` where `roles`.`id` in ('1', '2', '3', '4') //app\Http\Controllers\UserController.php:24

select `permissions`.*, `permission_role`.`role_id` as `pivot_role_id`, `permission_role`.`permission_id` as `pivot_permission_id` from `permissions` inner join `permission_role` on `permissions`.`id` = `permission_role`.`permission_id` where `permission_role`.`role_id` in ('1', '2', '3', '4')

I also added just now:

protected $with = ['role']; //to User model

protected $with = ['permissions']; //to Role model

but it didnt affect with reducing "gate" number.

This number is connected with using @can directive in Blade views.

I think you should start looking at loops in your views and where you're actually using the gates. You might also be using the same gates in multiple places in the same request.

Think about a view looping over 50 users to list them, and you have a edit/delete button next to each one with a @can check to display the buttons. That's 100 checks right there. It's not necessarily a bad thing. It really depends on how you're using things. We don't see your views so we can't comment on whether the checks are all necessary of if there might be a more efficient way to structure it. It doesn't seem to be an issue with queries or caching.

@click : About caching: I have changed my UserController index method to:

    public function index() {
        if (!Auth::user()->can('users-general-access')) { return Redirect::to('zadania'); }

        $users = Cache::rememberForever('users', function () {
            return User::with('role')->get();
        });

        return view('osoby.index')->with([
            'users' => $users,
        ]);
    }

and I dont see any big difference beside reducing 6 queries to 3.

@Cronix : Here is my index.blade.php view:

@section('width', 'fullwidth')
@extends('layouts.app')

@section('title', 'Osoby')

@section('submenu')
    @include('osoby.partial.submenu')   
@endsection

@section('modals')
    @include('partial.modals.delete')
@endsection 

@section('content')
    @hasSection('width')
    <section class="content container-fluid">
    @else
    <section class="content container">
    @endif  
    <div class="row">
        <div class="col-xs-12">
            <h1 class="page-header">Osoby</h1>      
        </div>
    
        <div class="col-xs-12">
            @include('partial.errors')  

            @can('users-add')
            <a class="btn" href="{!! URL::to('osoby/create') !!}">Dodaj osobę</a>
            @endcan
    
            <table id="main-table" class="table table-striped table-bordered" cellspacing="0" width="100%">
                <thead>
                    <tr>
                        <th>Imię i nazwisko</th>
                        <th>E-mail</th>
                        <th>Telefon</th>
                        <th>Zawieszony</th>
                        <th>Rola</th>
                        @canany('users-edit|users-delete|users-lock')<th></th>@endcanany
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>Imię i nazwisko</th>
                        <th>E-mail</th>
                        <th>Telefon</th>
                        <th>Zawieszony</th>
                        <th>Rola</th>
                        @canany('users-edit|users-delete|users-lock')<th></th>@endcanany
                    </tr>
                </tfoot>
                <tbody>
                @foreach($users as $key => $value)
                    <tr>
                        <td class="{{ $value->statusLock() }}"><a href="{{ URL::to('osoby/' . $value->id . '/edit') }}">{{ $value->fullName }}</a></td>
                        <td class="{{ $value->statusLock() }}">{{ $value->email }}</td>
                        <td class="{{ $value->statusLock() }}">{{ $value->phone }}</td>                 
                        <td class="{{ $value->statusLock() }}">{{ ($value->active) ? 'nie' : 'tak' }}</td>
                        <td class="{{ $value->statusLock() }}">
                            @if ($value->role)
                                {{ $value->role->label }}
                            @endif
                        </td>
                        @canany('users-edit|users-delete|users-lock')
                        <td class="nowrap">
                            @can('users-edit')
                                <a class="table-ico" href="{{ URL::to('osoby/' . $value->id . '/edit') }}"><img src="{{ URL::asset('img/ico-edit.png') }}" alt="" /></a>
                            @endcan
                            
                            @can('users-delete')
                                <a class="table-ico modal-open modal-open-delete" data-modal="modal-delete-form" data-id="{{$value->id}}"><img src="{{ URL::asset('img/ico-delete.png') }}" alt="" /></a>
                            @endcan
                            
                            @can('users-lock')
                                <a class="table-ico" href="{{ URL::to('osoby/' . $value->id . '/lock') }}" title="
                                @if($value->active == 1)
                                    Zablokuj osobę (nie będzie mógł do czasu odblokowania logować się do systemu)
                                @else
                                    Odblokuj osobę
                                @endif
                                "><img src="{{ URL::asset('img/ico-lock.png') }}" alt="" /></a>
                            @endcan
                        </td>
                        @endcanany
                    </tr>               
                @endforeach             

                </tbody>
            </table>
            <script>
                $(document).ready(function() {
                    $('#main-table').dataTable({
                        "language": {
                            "processing":     "Przetwarzanie...",
                            "search":         "Szukaj:",
                            "lengthMenu":     "Pokaż _MENU_ pozycji",
                            "info":           "Pozycje od _START_ do _END_ z _TOTAL_ łącznie",
                            "infoEmpty":      "Pozycji 0 z 0 dostępnych",
                            "infoFiltered":   "(filtrowanie spośród _MAX_ dostępnych pozycji)",
                            "infoPostFix":    "",
                            "loadingRecords": "Wczytywanie...",
                            "zeroRecords":    "Nie znaleziono pasujących pozycji",
                            "emptyTable":     "Brak danych",
                            "paginate": {
                                "first":      "Pierwsza",
                                "previous":   "Poprzednia",
                                "next":       "Następna",
                                "last":       "Ostatnia"
                            },
                            "aria": {
                                "sortAscending": ": aktywuj, by posortować kolumnę rosnąco",
                                "sortDescending": ": aktywuj, by posortować kolumnę malejąco"
                            }
                        },                  
                        
                        "pageLength" : 100,
                        "order" : [[3, 'asc'], [4, 'desc']],
                    });
                    
                    $('body').on('click', 'a.modal-open-delete', function() {
                        project_id = $(this).data("id");
                        
                        action = '<?php echo URL::to('osoby'); ?>/'+project_id;
                        $('#delete-form').attr('action', action);                       
                    });                     
                });
            </script>
        </div>
    </div>
</section>
@endsection

It's what I suspected. You just have a TON of checks with @can and @canany, and some are in a loop. How many users are you looping through there?

I count at least 6 checks per loop iteration, not including checks outside of the loop. So for 5 users, that's 30 checks. For 100 users, that's 600 checks...

@canany('users-edit|users-delete|users-lock')

That's 3 checks right there, and then you also check each one of those individually within that check.

@foreach($users as $key => $value)
    @canany('users-edit|users-delete|users-lock')  // 3 checks
        @can('users-edit')  // 4th
        @endcan

        @can('users-delete')  //5th
        @endcan

        @can('users-lock')  // 6th
        @endcan
    @endcanany
@endforeach

If you remove the checks from that loop and leave the html they're generating intact, does it speed up loading of the page? I'd assume it would by a bit, but not by much. Those really aren't any different than an @if, except no one is tracking how many if statements you have in a view.

@cronix good point and if it still is low, you can try to test how much time a part of your code takes by placing a dump before and after the part of code you want to test.

Example:

dump( date( ‘H:i:s’ ) );

foreach( ... ) {
    ...
}

dump( date( ‘H:i:s’ ) );

@gertjanroke : It is only 1s delay.

@cronix : Yes, my suspicion was the same, so I think we can draw a conclusion that there's nothing to worry about and using @can directive doesnt reduce performance as well as using @if doesnt.

I do not understand why in this case such statistics exists for, since it doesn`t brings nothing important.

@wizjo oh 1s is not so bad, did you also checked your network tab? Maybe you can see something there what makes it slow.

Maybe something to keep in mind, debugbar will also consume alot of time.. it is a very handy tool but it makes your site also slower when you have it enabled.

I have some assumptions that I need to check in the subject of delay, but in order not to leave the main topic, the proposal is probably as I wrote in the previous post- the @can directive does not cause unnecessary database queries or excessive delays, so it can be used safely and there is nothing to worry about using it in loops and the number of uses is not very significant.