How can I get the table to load faster

estgelais

I am evaluating Editor. I've used Datatables and am very happy with it, but I can't seem to get the same table (287,000 records) to load as quickly in the Editor version. I have a simple server side script that calls only 2 fields, but it takes approximately 20 seconds to load the first 10 rows.

Editor::inst( $db, 'tp_offline' )
    ->fields(
        Field::inst( 'Business' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'City' )->validator( 'Validate::notEmpty' )
    )
    ->process( $_POST )
    ->json();

When I run the server script (http://taxprovider.com/wp-content/plugins/tp-special/editor/tp/tp_offline_simple_server.php) the json loads in under 2 seconds.

I have also implemented

"deferRender": true

in the js based on something I read in a forum post.

Any help with this would be greatly appreciated.

allan

Are you using server-side processing (serverSide) and if so, are you POSTing the DataTables request? Example available here.

Allan

estgelais

Allan,

The original Editor page I used which does load the table (after 20 seconds or so) iis here (http://taxprovider.com/wp-content/plugins/tp-special/editor/tp/tp_offline_simple.php).

It did not have the items you seemed to suggest, so I added those here (http://taxprovider.com/wp-content/plugins/tp-special/editor/tp/tp_offline_simple_new.php).

This new page throws an error. Maybe I didn't implement it correctly.

allan

For >50'000 rows you almost certainly want to use server-side processing. The data load is 21.6MB on the original page for example!

Could you show me the PHP in tp_offline_simple_server.php?

Thanks,
Allan

estgelais

Allan,

I believe I am trying to use serverside processing, just apparently not being very successful at it. :-)

Here is the code in my serverside script:

// DataTables PHP library
include( "../php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'tp_offline' )
    ->fields(
        Field::inst( 'Business' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'City' )->validator( 'Validate::notEmpty' )
    )
    ->process( $_POST )
    ->json();

It is fairly simple and attempts to do what this example does - http://editor.datatables.net/examples/simple/server-side-processing.html

allan

Super - thanks. The issue is that DataTables is trying to do a sort on the first column, which isn't going to work when server-side processing since there is no checkbox column at the server.

That default sort is coming from order. Simply adding order: [[1, 'asc']] to your table's initialisation I believe will address this.

Page load time is down to 400mS for me like that :-)

Allan

estgelais

That worked fantastic! Thanks so much! Have a great weekend!

estgelais

Oops, was a little fast with the praise. Turns out the search box throws the original error.

estgelais

Well, I think I figured it out. Based upon your suggestion for ordering, I made the first column not searchable.

columnDefs: [ {'targets': 0, 'searchable': false} ]

Seems to have done the trick!

allan

Yup - that's the way to do it :-). Thanks for posting back with the updates!

Regards,
Allan