Am i doing this right.. AJAX, PHP, Datatables ( Slow )

WebCodex

HI,

Can someone take a look at my code and tell me if im doing this right please.

I have 1300+ records, im using AJAX to get a json array from a PHP function.

I tried to add a loading animation with no luck..

        oLanguage: {
            sProcessing: "<img class='multi_load' src='images/loadingspinner.png'>"
        },

View the table working (slowly) here: http://webcodex.co/woh/?page=cardlist

The JS code:

<script type="text/javascript">
$(document).ready(function(){

    var token = <?php echo json_encode($token); ?>;
    $.ajax({
        type: 'POST',
        url: 'controllers/getcardlist.php',
        dataType: 'json',
        data: {  token: token },
        success: function(data){
            $('#data').DataTable( {
                processing : true,
                responsive: true,
                "deferRender": true,
                data: data,
                columns: [
                { 
                    'data': 'cardname', 
                        "render": function ( data, type, full ) {
                            return '<a href="?page=card&amp;cardname='+ full.cardname +'">'+ full.cardname +'</a>'; 
                        }
                },
                { 'data': 'rarity1' },
                { 'data': 'alignment' },
                    {
                    'data': 'image', 
                        "render": function ( data, type, full ) {
                            return '<a class="fancybox" rel="" href="cardimages/'+ full.alignment +'/'+ full.image1 +'" data-fancybox-group="'+ full.cardname +'">Card Image</a><div class="hidden"><a class="fancybox" rel="'+ full.image2 +'" href="cardimages/'+ full.alignment +'/'+ full.image2 +'" data-fancybox-group="'+ full.cardname +'">Card Image</a></div>';
                        }
                    },
                    { 
                    'data': 'overboost', 
                        "render": function ( data, type, full ) {
                            return '<a href="?page=calcoverboost&amp;cardname='+ full.cardname +'">Overboost</a>';
                        }
                    },
                ],
                initComplete: function () {
                            this.api().columns('.select-filter').every( function () {
                                var column = this;
                                var select = $('<select><option value=""></option></select>')
                                    .appendTo( $(column.footer()).empty() )
                                    .on( 'change', function () {
                                        var val = $.fn.dataTable.util.escapeRegex(
                                            $(this).val()
                                        );
                 
                                        column
                                            .search( val ? '^'+val+'$' : '', true, false )
                                            .draw();
                                    } );
                 
                                column.data().unique().sort().each( function ( d, j ) {
                                    select.append( '<option value="'+d+'">'+d+'</option>' )
                                } );
                            } );
                        }
            });
        }
    });
});
</script>

The PHP Code:

if($_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest' && isset($_POST['token']) && $_POST['token'] === $_SESSION['token']){
    $alignment = 'all';
    echo json_encode($cards->getCardDetails($alignment));
}

ThomD

First, thanks for posting a link. The server seems slow. Loading swipper.css took 4 seconds the first time. datatables.min.js took 3.5 seconds. the PHP took almost 9 seconds. You need to work on that server delivery speed before worrying about js performance, The third time I loaded everything was much faster (mostly cached I'm sure), even the php only took 2 seconds, but then took 4 seconds the 4th time.

WebCodex

Thanks for the reply,
If I run the PHP function by itself and print_r() the returned array it takes milliseconds so it must be the JS that is somehow slowing it down, is the way I'm using AJAX ok? Is there a more efficient way? Thanks in advance

allan

Your DataTales initialisation looks fine. You could reduce the amount of code a little by using ajax rather than making your own Ajax call, but that wouldn't benefit the performance.

When looking at the page, the Ajax request (for me) took 260mS before any response was given and then 1.7S to download the results. It is almost 1MB of data. That appears to be where most of the apparent performance hit is.

I don't think your server is using gzip - that would definitely help and I would suggest enabling that.

You might also want to see if you can reduce the amount of data in the JSON payload. There are a lot of fields that don't appear to be getting used.

Allan

WebCodex

Thanks for your reply,
Ill try out the AJAX Option maybe that will help

allan

I don't think it will as I stated above. The other options I suggested should.

WebCodex

Gzip is on by default:

Accept-Encoding gzip, deflate

WebCodex

Im using PHP MySQL PDO, in other applications when i use AJAX the requests are super quick and i can return a similar amount of data in milliseconds.

The point of using datatables is the way it formats the tables, the responsive part mostly, I'm really struggling to get it to load as quickly as id like, i cannot even get a loading message to show up, which would make things a little bit better.

ThomD

From home this morning, it was quick. The php call took 118ms to respond and 350ms to download. The response header says "chunked", so I think gzip didn't get applied.

WebCodex

Hey, thanks for all your help on this issue,

I have started coding a server side approach as found here https://www.datatables.net/examples/data_sources/server_side.html but I have a question:

How can i return data from the DB to use in the formatter?

How can i access other values in the DB?

$columns = array(
    array( 
        'db' => 'cardname', 
        'dt' => 0,
        'formatter' => function( $d, $row ) {
            return '<a href="?page=card&amp;cardname='.$d.'">'.$d.'</a>';
        }
        ),
           
    array( 'db' => 'rarity1',  'dt' => 1 ),
    array( 'db' => 'alignment',   'dt' => 2 ),
    array(
        'db'        => 'image1',
        'dt'        => 3,
        'formatter' => function( $d, $row ) {
            return '<a class="fancybox" rel="" href="cardimages/'.$row['2'].'/'.$row[3].'" data-fancybox-group="'.$row[0].'">Card Image</a><div class="hidden"><a class="fancybox" rel="'.$row[0].'" href="cardimages/'.$row['2'].'/'.$row[0].'" data-fancybox-group="'.$row[0].'">Card Image</a></div>';
        }
    ),
);

allan

If you want to do anything beyond the very simple examples that use the SSP class you will need to create your own server-side processing script. The SSP class is designed to be useful only for the most simple of cases.

Allan

WebCodex

Yeah, i see now, ill give that a go and hopefully it will work out.

Keep up the great work here at Datatables.

Thankyou for all your help

tangerine

I can recommend Emran's SSP class.

https://emranulhadi.wordpress.com/2014/06/05/join-and-extra-condition-support-at-datatables-library-ssp-class/