Query execution time

neburton

I want to display SQL execution time somewhere on my datatable.

I've added a duration value to the JSON output:

"{"duration":"6.1875","data":[{.....

I've had a look through the API if there's a way to overide the info display or append it, but cannot see.

colin

You can use infoCallback for that, using ajax.json() to get the last Ajax response.

Colin

neburton

brilliant, got it working. Thanks very much

tefdat

@neburton,
I got working evaluating the json object and adding it into the infocallback as @colin mentioned.

                var table = $('#example').DataTable({
                ajax: "data.json",
                "infoCallback": function( settings, start, end, max, total, pre ) {
                    var api = this.api();
                    var pageInfo = api.page.info();
                    var params = table.ajax.params();
                    var json = table.ajax.json();
                    console.log(params);
                    console.log(json);
                    //https://stackoverflow.com/a/17663871/14226613
                    //https://datatables.net/forums/discussion/60823/query-execution-time
                    var info = "Showing " + start + " to " + end + " of " + total + " entries <em>(Total " + max.toLocaleString('en-US') + ")</em>";
                    return info
                },

May I ask how you added the duration value to the JSON output?
I guess, you are counting the time as decribed here somehow, but I have no clue, how to this.

Thank you

allan

@tefdat - It will depend on what you are using at the server-side to build the JSON. Your code above makes it look like data.json is a static file, but it might be getting parsed dynamically by something?

Allan

tefdat

Hey @allan
meanwhile I got it already almost running (without integrating into the json) - thx to the great datatables API docs

                    //https://stackoverflow.com/a/1975103/14226613
                    var t0 = 0;
                    var t1 = 0;
                    var duration = 0;

                    var table = $('#example')
                    .on('preXhr.dt', function ( e, settings, data ) {
                        console.log("fire");
                        t0 = performance.now();
                    })

                    .on('xhr.dt', function ( e, settings, json, xhr, data ) {
                        t1 = performance.now();
                        duration = ((t1 - t0) / 1000.0).toFixed(2);
                        console.log( 'Search took: ' + duration + " seconds." );
                    })              

                    .DataTable({

                        ajax: "data.json",
                        "infoCallback": function( settings, start, end, max, total, pre ) {
                            var api = this.api();
                            //https://stackoverflow.com/a/17663871/14226613
                            //https://datatables.net/forums/discussion/60823/query-execution-time
                            var info = "Showing " + start + " to " + end + " of " + total + " entries <em>(Total " + max.toLocaleString('en-US') + ")</em><br>Search took: " + duration + " seconds.";
                            return info
                        },


                        //responsive design on/off
                        responsive: true,
                    //deferRender true/false
                    deferRender: true,

                    //server side processing
                    processing: false,
                    serverSide: true,
                    ajax: {
                        type: "POST",
                        url: "./sql.php",
                        dataType: "json",
                    },

Only Issue - preXhr.dt is fired only once at page refresh, not again - allthough everything else running well.

tefdat

I used the workaround the preDraw event - pretty same result. Its perfect now.
Thank you all.
This is an important information for the user, hence pushing F12 and looking into the console is too much for the most of em

            var table = $('#example')
            /*
            .on('preXhr.dt', function ( e, settings, data ) {
                console.log("fire");
                t0 = performance.now();
            })*/
            .on( 'preDraw.dt', function () {
                t0 = performance.now();
            })
            .on('xhr.dt', function ( e, settings, json, xhr, data ) {
                t1 = performance.now();
                duration = ((t1 - t0) / 1000.0).toFixed(2);
                //console.log( 'Search took: ' + duration + " seconds." );
            })

            .DataTable({
                ajax: "data.json",
                "infoCallback": function( settings, start, end, max, total, pre ) {
                    var api = this.api();
                    //https://stackoverflow.com/a/17663871/14226613 / https://datatables.net/forums/discussion/60823/query-execution-time
                    var info = "Showing " + start + " to " + end + " of " + total + " entries <em>(Total " + max.toLocaleString('en-US') + ")</em><br>Search took: " + duration + " seconds.";
                    return info
                },

allan

Nice one - thanks for posting back!

Allan