Server-side processing sluggish?

Randy Cash

I have a MS Sql Server (Express 2012) database on a Windows 2008 R2 server. I'm running PHP 5.4 with IIS 7 on that server. The table I'm working on has over 40 columns and about 8,000 rows. Even with server-side processing, it still takes about 22 seconds to render. I'm using server-side processing because some of the database tables have over a million rows. I was hoping someone could take a look at my code and provide some insight / suggestions for speeding things up.

*Note - In the interest of saving space, I won't include all of the column names.

HTML:

<table id="table_id" class="display">
<thead>
    <tr>
        <th>OKIID</th>
        <th>Hwy_Dir</th>
        <th>Main_Street</th>
        <th>sDir</th>
        <th>Cross_Street</th>
        <th>Source</th>
        <th>CountYear</th>
        <th>AADT</th>
    </tr>
</thead>

Javascript:

$(document).ready(function(){
$('#table_id').DataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxDataProp": "",
"sAjaxSource": "Database.php",
"sServerMethod": "POST",
"fixedHeader": true,
"scrollY": "75vh",
"scrollX": "900px",
"scrollCollapse": true,
"paging": false,
"aoColumns":[
{"mData": "OKIID"},
{"mData": "Hwy_Dir"},
{"mData": "Main_Street"},
{"mData": "sDir"},
{"mData": "Cross_Street"},
{"mData": "Source"},
{"mData": "CountYear"},
{"mData": "AADT"}
]
});
});

PHP:

<?php header('Content-Type: application/json; charset=UTF-8'); $serverName = "TRAFFICDB\SQLEXPRESS"; $connectionInfo = array( "Database"=>"Traffic Count Database", "UID"=>"okiuser", "PWD"=>"counts"); $conn = sqlsrv_connect( $serverName, $connectionInfo); $SQL = "SELECT * FROM AADT"; $result = sqlsrv_query($conn, $SQL); while( $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){ $rows[] = $row; } echo json_encode($rows); sqlsrv_free_stmt($result); $close = sqlsrv_close($conn); ?>

allan

"paging": false,

That means that your script is always loaded every single row. So you get absolutely no benefit from server-side processing, and in fact all that is happening is that more data needs to be downloaded for every draw. That's probably the worst possible option for performance :-).

So either you can use paging, or if you really don't want the paging buttons, use Scroller (which can utilise paging without the paging buttons being shown).

Allan

Randy Cash

Haha, I would use the worst possible option for performance. I didn't want to use paging. I will try Scroller and let you know how it goes. Thanks!

Randy Cash

Ok, I tried initializing scroller, and it does not improve the speed. In the past, I have tried either one (or some combination) of paging, deferLoading, deferRender, and the speed stays the same. Even after the data completes its initial load, the scroll bars are sluggish. Maybe it's an issue with my PHP?

allan

To check I would suggest you use:

• paging: true
• deferLoading - do not use
• deferRender - do not use (makes no different when you have server-side processing)
• scroller - do not use

i.e. just have simple paging to start with. Does the server return only 10 rows of data at a time?

If it does, then your PHP is okay. If it returns all rows, the PHP is duff :-)

Allan

Randy Cash

Yeah, each page returns all 8,000 rows. Must be the PHP. My existing code is fetching an associative array from the table in SQL Server.

  $SQL = "SELECT *
          FROM AADT";
  $result = sqlsrv_query($conn, $SQL);
  while( $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){

    $rows[] = $row;
  }
  echo json_encode($rows);

Is there a change that you would recommend? Thanks.

allan

I would suggest reading through the server-side manual. There are a number of parameters DataTables submits that you need to apply to the query, such as the page length, sorting and filtering.

There is a demo script that the examples include if you want to get started using that. These are the scripts my demos use.

Allan

Randy Cash

I'll read through it. Thanks!

Randy Cash

Allan,

In hindsight I realize that looping through the array was a bit foolish. It was reading through every row, every time, just to return the next row. Of course it was going to jam things up.

I took your suggestion and used the example server-side script to modify my code:

<table id="aadt" class="display">
<thead>
    <tr>
        <th>OKIID</th>
        <th>Hwy_Dir</th>
        <th>Main_Street</th>
        <th>sDir</th>
        <th>Cross_Street</th>
        <th>Source</th>
        <th>CountYear</th>
        <th>AADT</th>
    </tr>
</thead>
</table>

$(document).ready(function(){
            $('#aadt').DataTable({
              "processing": true,
              "serverSide": true,
              "ajax": "Database.php"
            });
          });

```php
<?php
$table = 'AADT';
$primaryKey = 'OKIID';
$columns = array(
array( 'db' => 'OKIID', 'dt' => 0 ),
array( 'db' => 'Hwy_Dir', 'dt' => 1 ),
array( 'db' => 'Main_Street', 'dt' => 2 ),
array( 'db' => 'sDir', 'dt' => 3 ),
array( 'db' => 'Cross_Street', 'dt' => 4),
array( 'db' => 'Source', 'dt' => 5),
array( 'db' => 'CountYear', 'dt' => 6),
array( 'db' => 'AADT', 'dt' => 7)
);
$sql_details = array(
'user' => 'okiuser',
'pass' => 'counts',
'db' => 'Traffic Count Database',
'host' => 'TRAFFICDB\SQLEXPRESS'
);
require('ssp.class.php');
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

<?php > ``` Looking at the response window in Chrome's developer tools, it appears that the query string being sent to the server is too long. Should I just change the IIS settings in Windows to allow more than the max 2,048 character length? Or, do I need to modify the code to prevent datatables from sending too many parameters to the server? ?>

I saw a couple of older threads on the topic, but didn't really see a good solution. Maybe I just overlooked the answer; in which case, I apologize for wasting your time.

Thanks.

Randy Cash

Ok, I solved the aforementioned issue by using:

"sServerMethod": "POST"

It then said that the 'ssp.class.php' file could not be found. I went to github and downloaded the file, and saved it in my root folder, with the other PHP files.

I removed the following code block from the ssp.class.php file:

// REMOVE THIS BLOCK - used for DataTables test environment only!
$file = $_SERVER['DOCUMENT_ROOT'].'/datatables/mysql.php';
if ( is_file( $file ) ) {
    include( $file );
}

It now says that, 'an error occured while connecting to the database; could not find driver'. This is a head-scratcher. My server is configured correctly for PHP 5.4, and the
php_sqlsrv_54_nts.dll extension is enabled in my php.ini file. Thoughts???

Randy Cash

It would probably help if I actually went through the ssp.class.php file and added the appropriate database connection.

allan

Thanks for the updates. Yes - the SSP class that is in the examples is currently suitable only for MySQL. Hopefully it shouldn't be too difficult to modify for SQL Server. I think the main change will be the connection and the SQL used to count the number of rows (since the SSP demo uses a MySQL specific shortcut to help improve performance a little there).

Allan

Randy Cash

If I may ask, what is the difference between the following: columns and aoColumns, ajax and sAjaxSource, etc.? I've seen examples that contain both, and they seem to do similar things. Is it version-specific? Thanks.

allan

The Hungarian notation versions are all legacy. If you can not use them, I would strongly suggest you do so. They will still work for backwards compatibility, but only the camelCase versions are documented on this site now.

See the upgrade naming documentation for details. It also links to a guide which allows easy converting between them.

Allan

Randy Cash

Allan,

I found an old server-side processing PHP script for Sql Server in the legacy documentation page:

https://www.datatables.net/development/server-side/php_mssql_odbc

The script successfully connects to my DB, sets the server type to 'Post', identifies the column headers, and sets searchable and orderable to true (according to the PHP response in Chrome's XHR development tab). But, it runs into a syntax issue in the 'Paging' query syntax:

    /* Paging */
    $top = (isset($_GET['iDisplayStart']))?((int)$_GET['iDisplayStart']):0;
    echo($top);
    echo("\n");
    $limit = (isset($_GET['iDisplayLength']))?((int)$_GET['iDisplayLength'] ):10;
    echo($limit);
    echo("\n");
    $sQuery = "SELECT TOP $limit ".implode(",",array_values($aColumns))."


        FROM $sTable
        $sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN
        (
            SELECT $sIndexColumn FROM
            (
                SELECT TOP $top ".implode(",",array_values($aColumns))."
                FROM $sTable
                $sWhere
                $sOrder
            )
            as [virtTable]
        )
        $sOrder";

     $rResult = sqlsrv_query($gaSql['link'],$sQuery);

I get an SQLSTATE 42000, and code 156. There is a Syntax issue near 'FROM' in both SELECT statements that contain the 'implode' function. Tried echoing and var_dumping, but having trouble debugging. Any syntax issues jump out at you?

Thanks.

allan

You'd need to either update it for the new parameters, or send the legacy parameters.

The legacy type will be removed in the next major version though, so I would suggest the former.

Allan

Randy Cash

Maybe I will just use the ssp.class.php file and modify it for SQL Server. It's probably easier to just update the DB connection for SQL Server, rather than go through and update all of the parameters in this script. I just thought since this script was available, I'd give it a go. Maybe I'll try to get both working and post the scripts on here. I'll keep you posted on the ssp.class.php progress.

Thanks!

Randy Cash

Allan,

I did end up using that SQL Server PHP script in the legacy documentation. I've been going through and updating the parameters to the camelCase notation used in 1.10. Still having some issues, and I was hoping you could help.

$output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "data" => array()
    );

Chrome throws a PHP notice that sEcho is an undefined index. Does sEcho need to be changed to either paging or draw? Also, my ajax type is set to "POST". Does $_GET need to be changed to $_POST in my script?

Thanks.

Randy Cash

I should mention that I did look through the documentation on converting parameter names. I did not see sEcho in there.

Randy Cash

I wrapped that errant statement in an inline isset function

"paging" => intval(isset($_GET['paging']) ? ($_GET['paging']): "BAD")

Undefined index problem is gone. Now I'm just returning bad json. Chrome says the json is invalid. A json validator says it is good. I'll look into that more. Can you just confirm if I should use paging in place of sEcho?

Thanks.

allan

sEcho is only sent if you use use the legacy Ajax mode.

If you link to the page I'll take a look at its current configurations.

Allan

Randy Cash

Unfortunately, it's an intranet site at the moment. If you have an alternate method to send it to you, I can do that.

That being said, I'm no longer getting an invalid JSON error. The headers display correctly, as does the 'display 0 to 10' of 7997 records. The XHR response is returning the correct data. However, the datatable itself says no matching records.

I have checked that the columns structure is identical between HTML, JS, and PHP. Although I've validated the JSON output with JSONLint, I did have a question about the ouput.

The output array in the PHP script looks like this:

$output = array(
        "paging" => intval(isset($_GET['paging']) ? ($_GET['paging']): "BAD"),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "data" => array()
    );

    while ( $aRow = sqlsrv_fetch_array( $rResult ) ) {
        $row = array();
        for ( $i=0 ; $i<count($columns) ; $i++ ) {
            if ( $columns[$i] != ' ' ) {
                $v = $aRow[ $columns[$i] ];
                $v = mb_check_encoding($v, 'UTF-8') ? $v : utf8_encode($v);
                $row[]=$v;
            }
        }
        If (!empty($row)) { $output['data'][] = $row; }
    }
    echo json_encode( $output, JSON_PRETTY_PRINT );

So it is returning an array in JSON like this:

{
"paging": 0,
"iTotalRecords": 7997,
"iTotalDisplayRecords": 7997,
"data": [
[
"BOO0003",
"Combined",
"Industrial Rd (KY 1829)",
"E of",
"Dixie Hwy (US 25)",
]
]
}

Is the inclusion of "paging", "iTotalRecords", and "iTotalDisplayRecords" in the output causing the JSON to be read incorrectly by the table?

Thanks.

Randy Cash

Ok, disregard the last post. I'm now returning data. The issue was not with the JSON. My dataSrc was set to "", when it should have pointed to the actual "data" array that was being returned in the PHP script.

Interestingly, I got an error for an unknown parameter 'OKIID' for row 0, column 0. 'OKIID' is a column header and not in the array. In my javascript, I had:

"columns": [
{"data" : "OKIID"}

and so on, for the rest of the column headers. As a result, DataTables was looking for the string "OKIID" in the JSON array. Of course it doesn't exist in there, because it's just a header. I changed my JS to read:

"columns": []

Now it successfully returns data; and without the sluggishness that prompted me to start this thread in the first place.

I still have to work out some issues with the pagination. When that's fixed, I'll post the script here for other MSSQL users who are having issues. Then we can consider this thread closed.

Thanks.

Randy Cash

Allan,

Do you see any syntax issues with the following PHP script for pagination:

    $top = (isset($_GET['displayStart']))?((int)$_GET['displayStart']):0;
    $limit = (isset($_GET['pageLength']))?((int)$_GET['pageLength'] ):50;

    $sQuery = "SELECT TOP $limit ".implode(",",$columns)."


        FROM $sTable
        $sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN
        (
            SELECT $sIndexColumn FROM
            (
                SELECT TOP $top ".implode(",",$columns)."
                FROM $sTable
                $sWhere
                $sOrder
            )
            as [virtTable]

        )
        $sOrder";

I can set the top and limit to be whatever, and DataTables will display those records without issue. However, when I click on the next page, it doesn't make a new call to the server.

Thanks.

allan

I would need a link to the page or a debug trace to understand what the issue is.

Allan

Randy Cash

It's not a published site yet. I'll try the debugger. Thanks.

Randy Cash

Debug code is iqikab. Does this allow you to view my page, and the issue?

allan

Not the page, just the data from it. It shows that draw is being returned as 0 in the JSON data which is not valid. It should always be 1 or higher.

Allan

Randy Cash

Hmmm. I was skeptical about the updates to this section of code. From the legacy MSSQL ODBC PHP script:

    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );

I updated it to read:

    $output = array(
        "draw" => intval(isset($_GET['draw']) ? ($_GET['draw']): "BAD"),
        "recordsTotal" => $iTotal,
        "recordsFiltered" => $iFilteredTotal,
        "data" => array()
    );

Did I replace 'sEcho' incorrectly? I never saw the conversion for it in the legacy documentation.

allan

That looks like it should work to me. Although I would suggest you use:

"draw" => intval(isset($_GET['draw']))

That the moment you are intval()ing the result of the isset(). I'm not sure what would happen with that!

Allan

Randy Cash

I had another thought. I came across this example for draw:

var table = $('#example').DataTable();
 
$('#myFilter').on( 'keyup', function () {
    table
        .search( this.value )
        .draw();
} );

I never included this in the javascript, because it's not in the server-side processing examples. I have paging set to true. But, do I also need this draw function?