Datatable serverside rendering toJson() very slow.

dyle71

Description of problem:

!/bin/hi *

I have Laravel 10 and DataTables v10.11.4 installed. And my server-side rendering is very, very slow.

The code snippet in question is:

$ts_start = hrtime(true);
$guestRegistration = GuestRegistration::where('show_day_id', $showDay->id)
    ->with('showDay')
    ->with('show')
    ->with('customer')
    ->with('customer.gender')
    ->with('registrationState')
    ->with('customer.organization')
    ->with('customer.labels')
    ->get();
$ts_end = hrtime(true);
$ts_duration_db_lookup = ($ts_end - $ts_start) / 1000000;

$ts_start = hrtime(true);
$dt = datatables()
    ->of($guestRegistration)
    ->addColumn('name', function ($item) {
        return view('control.customers.name')
            ->with('customer', $item->customer);
    })
    ->addColumn('registration', function ($item) {
        return view('control.registration.entry-url')
            ->with('registration', $item);
    })
    ->addColumn('status', function ($item) {
        return view('control.registration.status-extended')
            ->with('registration', $item);
    })
    ->addColumn('actions', function ($item) {
        return view('control.registration.guest-registration-actions')
            ->with('registration', $item);
    });

$ts_end = hrtime(true);
$ts_duration_datatables = ($ts_end - $ts_start) / 1000000;

$ts_start = hrtime(true);
$json = $dt->toJson();
$ts_end = hrtime(true);
$ts_duration_json = ($ts_end - $ts_start) / 1000000;

I debugged in deep and the $ts_* vars show me my timings. A typical case is:

• $ts_duration_db_lookup: 647 ms - So, I assume my Eloquent lookup is pretty ok. I also see that in total I only come up with 13 SQL queries in the PHP Debug bar.
• $ts_duration_datatables: 33 ms. Setting up the Datatable does not do anything.
• $ts_duration_json: 12534 (!) ms. Converting the result from the database to JSON via Datatables takes up 12 seconds!

This is done for a set of 10 out 500 items. As I see, every data is parsed and processed in Yajra\DataTables\Processors\DataProcessor::process Method, i.e. all of the 500 items are heavily worked on, even if I only requested 10 from these.

Meaning: when I do have 100000 items, this call will last... how long?

What can I do to speed up the processing? I sense my SQL is ok.

Thx!

dyle71

BTW, if I change the added columns to constant strings instead of blades, like this:

$dt = datatables()
    ->of($guestRegistration)
    ->addColumn('name', 'This is the name')
    ->addColumn('registration', 'This is the registration')
    ->addColumn('status', 'This is the status')
    ->addColumn('actions', 'This is the action');

The conversion toJson() even gets worse!

$ts_duration_json now reports 23916 (!) ms!

What is wrong?

kthorngren

As I see, every data is parsed and processed in Yajra\DataTables\Processors\DataProcessor::process Method, i.e. all of the 500 items are heavily worked on, even if I only requested 10 from these.

Sounds like either you haven't enabled server side processing in the Datatables initialization code or the server script doesn't support fetching only the rows requested by server side processing and fetches all the rows.

Yarja Datatables is a third party library that incorporates the use of Datatables. I don't believe there is anyone, who answers questions on this forum, who is familiar with Yarja Datatables. You probably will want to use their support resources or maybe something like Stack Overflow for help with Yarja Datatables.

Kevin

dyle71

Thx for the response. I'll check there.

dyle71

Ha! Found it. Passing on the Query instead of the collection (i.e. omit ->get()) solved the issue.

Fixed.