Client side - 110 000 records from json takes about 15seconds to load

timcadieux

Building a datatable using an api I built to load 110 000 records but it takes forever for the page to load

fiddler here - http://jsfiddle.net/f5tz643r/1/

Looking for ways to speed this up. I am willing to do a server side implementation if necessary but I'm not a php coder and not a single of the php examples i've attempted to use would work for me.

tangerine

I am willing to do a server side implementation if necessary

It's probably necessary.

...the php examples i've attempted to use

Which examples, and in what way wouldn't they work?

gyrocode

Data set is 83MB, Ajax request itself on my 50Mbps connection takes 16 secs to complete (5 secs waiting for response and then 11 sec receiving response). That is besides jQuery DataTables processing time.

Server-side processing is your best option.

However since there is third-party service involved, you would need to import the full dataset into your own database and then keep it current by updating periodically. That would probably be the most complex part.

jQuery DataTables distribution comes with sample PHP script and server-side processing library that is easy to adjust to your own needs.

---

See more articles about jQuery DataTables on gyrocode.com.

timcadieux

Thank you, I had no idea these pages existed.

I've used the examples pages and edited them to the simplest possible config and unfortunately I'm still having an issue.

Am I supposed to be able to browse to http://www.mapleleaflegacy.ca/v1/api/server_side/server_processing.php and return a json because that page always fails.

$table = 'tblsearchtable';
// Table's primary key
$primaryKey = 'id';

$columns = array(
    array( 'db' => 'id', 'dt' => 0 )
);
// SQL server connection information
$sql_details = array(
    'user' => 'username',
    'pass' => 'password',
    'db'   => 'database',
    'host' => 'host'
);

require( 'ssp.class.php' );
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);


            <table id="datatable" cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered dt-responsive nowrap" width="100%">
                <thead>
                    <tr>
                        <th>id</th>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>id</th>
                    </tr>
                </tfoot>
            </table>

$('#datatable')
    .on('xhr.dt', function (e, settings, json, xhr) {
        //Clean the Data
        if (json != null) {
        }
    })
        .DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "http://www.mapleleaflegacy.ca/v1/api/server_side/server_processing.php"
    } );

allan

Check what your server's error logs say about that error. A 500 server error is impossible to diagnose on the client-side.

Allan

tangerine

Is your SQL server connection information correct?
Do you have ssp.class.php available?

timcadieux

Checked with the host and this was the response i got
'That script was exceeding the limitation of memory usage set by PHP config.'

Upped the limit and its all good now. I never would have found that! Thx!

So this page is now returning data
http://www.mapleleaflegacy.ca/v1/api/server_side/server_processing.php

but when i run the above js, the code below always sees the json as null

    .on('xhr.dt', function (e, settings, json, xhr) {
        //Clean the Data
        if (json != null) {
        }
    })

allan

Do you have a link to the page where you are using that - not just the JSON response but the actual page?

Allan

timcadieux

http://www.mapleleaflegacy.ca/newsearch/search.html

timcadieux

Strange, the code is now working-ish...

It now displays No matching records found, but also page 1 of 57 pages and the paging is returning data on all pages, just not on page 1 of the initial load.

timcadieux

Doh, its working now! W00t..i have no idea why! lol...

Thx for your help folkz!!