search

yajra / laravel-datatables

jQuery DataTables API for Laravel
https://yajrabox.com/docs/laravel-datatables
MIT License
4.75k stars 859 forks source link

Speed when using a Collection #1437

Open clnt opened 7 years ago

clnt commented 7 years ago

Hi Yajra,

Going to start off with saying beautiful package, cheers for all your hard work - I use it in pretty much every project.

Summary of problem or feature request

After upgrading from 6.0 -> 8.0 I noticed some huge speed differences, mainly when passing an eloquent collection to the DataTable::make() function or to the new functions added in 8.0.

Before I upgraded my project to Laravel 5.5 and laravel-datatables 8.0 it was using Laravel 5.2 & laravel-datatables 6.x - all of my tables at this point were using collections and everything loaded within decent time. Nothing noticeable anyway, but once I upgraded my Laravel and datatables I started hitting max execution time, no data showing in table just getting stuck on showing 'processing' and generally not working properly.

I put this exact project on a better server, and it managed to handle it better to the point it didn't fully crash out (this wasnt a huge upgrade in hardware at all either and both have ssd's) but macOS > CentOS.

However, these speed issues are not apparent at all and it is blazing fast when using $dataTable->queryBuilder()

I've managed to convert all of my tables to use queryBuilder() but I have one table which is much easier to generate via a collection. It only has 15k rows but still takes forever when passing a collection (about 1 minute 34 seconds), I have another table with 55k+ rows and it is rendered in about 7 seconds when using queryBuilder()

If I run the same test as above on the 'better server' using a collection only takes 15 seconds, because of how small the hardware gap is between these machines I believe it possibly could be down to operating system?

Code snippet of problem

return $dataTables->collection($collection)
         ->editColumn('forename', '{{ $forename }}') 
         ->editColumn('surname', '{{ $surname }}') 
         ->make(true);

OR

return DataTables::make($collection)
         ->editColumn('forename', '{{ $forename }}') 
         ->editColumn('surname', '{{ $surname }}') 
         ->make(true);

System details

I shall include details of both systems, including hardware specs:

Mac (machine problem is most apparent on)

Hardware:

Server (just a plain desktop PC running linux)

Hardware:

Let me know if you require anymore information.

Thanks!

yajra commented 7 years ago

I think the biggest change done on collection was the fix for support on multiple column ordering. Will try to dig on this when I got the chance. Thanks for the heads-up!

yajra commented 7 years ago

Just recalled, this might be causing the performance issue:

$this->collection = $this->collection
                ->map(function ($data) {
                    return array_dot($data);
                })
                ->sort($sorter)
                ->map(function ($data) {
                    foreach ($data as $key => $value) {
                        unset($data[$key]);
                        array_set($data, $key, $value);
                    }

                    return $data;
                });

Given that you have 15k rows, on the latest version, all of these will be converted to array dot, sorted accordingly then another loop to revert the data. I think these 3 iterations causes the performance issue due to bulk rows.

However, I think this operation is needed in order for multiple column sorting to work. If you have any idea on how to refactor this to improve the performance. Please do not hesitate to submit a PR. Thanks!

yajra commented 7 years ago

I am also following some PR on the framework that tried to do the multiple sorting using sortBy but it has been on & off and was I think reverted several times. If that would be implemented, then that might be a good way to solve this issue.

clnt commented 7 years ago

Thanks for the great response, I can work around it for now but it would certainly be a great improvement if this could somehow be made faster.

When I got some spare time on my hands I will have a play around with it to see if I can speed it up in any way

HadrienKulik commented 6 years ago

From our tests, it seems like using blade syntax {{ }} inside editColumn affects performances significantly. Cf. https://github.com/yajra/laravel-datatables/issues/1534. @CLINT0N Maybe a way to quickly improve perf in your code as well?

yajra commented 6 years ago

@HadrienKulik yes I think it might be a possible cause too since using blade syntax calls a toArray method which will definitely be a hit in performance given a large data.