Slow DataTables - KoolReport

David Vanacore commented on Jul 16, 2020

I have a report using DataTables with server side processing on, that searches through 3 million plus records. The query pulls data from 2 different tables and I use Left Join and Union All in the query. There are 7 inputs and my report has 7 columns that it displays. I have Paginate and Search options on.

A typical search takes around 1 minute 15 seconds to complete. When I click on different page or try to use the search function, it takes around 1 minute for it to change or to show the search results, respectively.

Is there anyway to speed this up?

Here's a snippet from my view.php. I omitted the other inputs and columns, but they're basically the same.

<?php

use \koolreport\datagrid\DataTables;
use \koolreport\inputs\Select2;

?> <html>

<div style="margin:30px;">
<head>
<title>Search All Tables</title>
</head>
<body>
    <h1>Search All Tables</h1>
    <form method="get">
    <div class="row">
        <div class="col-sm-1">
        From Quarter:
            <?php
            Select2::create(array(
                "name"=>"startQtrPicker",
                "dataSource"=>$this->src("mysql2")->query("
                    SELECT YearQtr
                    FROM YearQtr
                    ORDER BY YearQtr DESC
                "),
                "attributes"=>array(
                    "class"=>"form-control"),
                ));
            ?>
        </div>
        <div class="col-sm-1">
        To Quarter:
            <?php
            Select2::create(array(
                "name"=>"endQtrPicker",
                "dataSource"=>$this->src("mysql2")->query("
                    SELECT YearQtr
                    FROM YearQtr
                    ORDER BY YearQtr DESC
                "),

                "attributes"=>array(
                    "class"=>"form-control"),
                ));
            ?>
        </div>

        </div>
        <div class="form-group" style="margin-top:22px;">
        <button class="btn btn-md btn-primary">Search</button>
        </div>
    </form>
    </div>
    </div>
    <?php
    DataTables::create(array(
        'name' => 'AllTabless',
        "cssClass"=>array(
            "table"=>"table table-striped table-bordered"),
        'dataSource' => function() {
            return $this->src('mysql2')
            ->query('
            #TABLE1
            SELECT
            YearQuarter AS "YrQtr",
            Group1.Title AS "Name",
            Dollars AS "Revenue"

            FROM (TABLE1
            LEFT JOIN Group1 ON Group1.__kp_Name_ID=TABLE1._kf_Name_ID
            )

            WHERE 1=1
            '.(($this->params["startQtrPicker"]!=array())?"and YearQuarter BETWEEN :startQtrPicker AND :endQtrPicker":"").'

            UNION ALL

            #TABLE2
            SELECT
            YearQuarter AS "YrQtr",
            Group1.Title AS "Name",
            Amount AS "Revenue"

            FROM (TABLE2
            LEFT JOIN Group1 ON Group1.__kp_Name_ID=TABLE2._kf_Name_ID
            )

            WHERE 1=1
            '.(($this->params["startQtrPicker2"]!=array())?"and YearQuarter BETWEEN :startQtrPicker2 AND :endQtrPicker2":"").'

            ')

            ->params(array(
                ":startQtrPicker"=>$this->params["startQtrPicker"],
                ":endQtrPicker"=>$this->params["endQtrPicker"],

                ":startQtrPicker2"=>$this->params["startQtrPicker2"],
                ":endQtrPicker2"=>$this->params["endQtrPicker2"],
            ));
        },
        "options" => array(
            "searching" => true,
            "paging" => true,
            "colReorder" => true,
            "pageLength" => 25,
            ),
        "serverSide"=> true,
    ));
    ?>
</body>

</html>

David Winterburn commented on Jul 16, 2020

Please perform the exact select query with join, where and union all in phpmyadmin interface and let us know how much time it takes. Thanks!

David Vanacore commented on Jul 20, 2020

When I run the same query in HeidiSQL it takes 26.5 second. In PHPMyAdmin, it takes 17.3 seconds from when I click "Go" to when the data shows on the screen. Although PHPMyAdmin shows that the query took 6.6 seconds.

David Winterburn commented on Jul 20, 2020

Hi,

Please turn on the following property of DataTables to disable search on input change:

DataTables::create(array(
    ...
    "searchOnEnter" => true,
    ...
);

To investigate further the bottleneck, please open the dev tool on your page (F12), choose tab Network, click label XHR, then change page of your DataTables. You could see each ajax request for each page change. Click on each request and its Timing tab. If the response takes too much time, it's a server side issue. Let us know the result. Thanks!

David Vanacore commented on Jul 21, 2020

When I select Network, XHR, there's only one item and it shows Status=200, Initator=app.js.16261, Size=153kB, Time=53.10s.

If I remove the Union All and the second table the time drops down to 11.14s

David Winterburn commented on Jul 21, 2020

Please try the following select query in phpmyadmin:

select * from (
            #TABLE1
            SELECT... 

            UNION ALL

            #TABLE2
            SELECT...
)
limit 0, 100

where the subquery is your query with replaced parameter values filled in. Let us know the result. Thanks!

David Winterburn commented on Jul 21, 2020

For your information, when DataTables uses server side processing it uses your select query as a subquery in this format to retrieve only the current page's rows:

select * from ($selectQuery) limit $offset, $length #for mysql datasource, other datasources have their own grammar for offset, limit

On some further research and tests I see that using Union or Union all in the select query greatly increase processing time as the database has to prepare all of union results (which could be millions of rows) before applying offset, limit.

We will do more research to see if there's a way to optimize union queries with offset, limit.

David Vanacore commented on Jul 25, 2020

Thank you for looking into it. Yes, when I take out the Union All, it works much faster. I would appreciate any info you have, if you figure out a way to optimize the union queries.

KoolReport's Forum

Slow DataTables #1533

Build Your Excellent Data Report