Performance Issues on Larger MSSQL Table - Am I doing Where Clause Properly?

pastorjustin · August 2016

Okay, so I have a MSSQL database with about 2 Million Rows in it. It is on a very high end DB system that performs exceedingly quickly. I have all the columns that I am doing my where clauses on properly indexed.

I have IIS/PHP installed on one server and the MSSQL server is on another box in the same environment. Basically, this is a table of all the orders in an accounting system. I have started out with the Simple Server Side Processing example and have slowly adopted it for MSSQL.

If I run a third party tool like RazorSQL on the IIS/PHP server (or anywhere else on the network for that matter), I can get the results by customer in less than 1 second (about .5 seconds). When I run the same query through the DataTables system, it takes about 25 seconds to process the same data (about 300 rows or so).

This (30 seconds or so) is about how long it takes to query this type of information in the SQL system if you are trying to query a column that is not indexed. It is odd to me that DataTables returns the statement "Showing 1 to 10 of 349 entries (filtered from 2,173,873 total entries)" Why is it even taking the time to gather the information about the total number of entries. That is not a normal response from a typical SQL query. It makes me wonder if the filter is happening post filter or something.

Am I doing something wrong in how I am executing the query? What can I do to get faster results?

Okay, well here are the nitty gritty details. This is what the SQL Query looks like (takes 1/2 second):

SELECT [CUST_DIV]
      ,[CUST_CODE]
      ,[ITEM_CODE]
      ,[ORDER_NUM]
      ,[ORDER_DATE]
      ,[PRIC_EXT_INV_TO_DATE]
  FROM [DATABASENAME.[dbo].[TABLENAME]
WHERE CUST_DIV = '05'
AND CUST_CODE = '123456789'

This is how I am doing the where clause in DataTables (takes 25 seconds):

SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "CUST_DIV = '05' AND CUST_CODE = '123456789'" )

Here are all the config files if you are curious.

HTML:

<script type="text/javascript" language="javascript" src="//code.jquery.com/jquery-1.12.3.min.js">
</script>
<script type="text/javascript" language="javascript" src="../../media/js/jquery.dataTables.js">
</script>
<script type="text/javascript" language="javascript" src="../resources/syntax/shCore.js">
</script>
<script type="text/javascript" language="javascript" src="../resources/demo.js">
</script>

$(document).ready(function() { $('#example').DataTable( { "processing": true, "serverSide": true, "ajax": "scripts/server_processing2.php" } ); } );

Division	Customer Code	Item #	Order #	Order Date	Order Amount
Division	Customer Code	Item #	Order #	Order Date	Order Amount

Server Processing:

<?php

$table = 'dbo.TABLENAME';
$primaryKey = 'ITEM_CODE';
$columns = array(
array( 'db' => 'CUST_DIV', 'dt' => 0 ),
array( 'db' => 'CUST_CODE', 'dt' => 1 ),
array( 'db' => 'ITEM_CODE', 'dt' => 2 ),
array( 'db' => 'ORDER_NUM', 'dt' => 3 ),
array(
'db' => 'ORDER_DATE',
'dt' => 4,
'formatter' => function( $d, $row ) {
return date( 'jS M y', strtotime($d));
}
),
array(
'db' => 'PRIC_EXT_INV_TO_DATE',
'dt' => 5,
'formatter' => function( $d, $row ) {
return '$'.number_format($d);
}
)
);

$sql_details = array(
'db' => 'DatabaseName',
'host' => 'HOSTNAME,1433',
'user' => 'user',
'pass' => '*******'
);

require( 'ssp2.class.php' );

echo json_encode(
SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "CUST_DIV = '05' AND CUST_CODE = '123456789'" )

);
```

pastorjustin · August 2016

Attached, is the SSP class. Which if anyone wants to use MSSQL with DataTables, that is some mighty helpful code to have around. It took me a bit to tweak all the pieces to ensure that search and other things would work properly.

allan · August 2016

Why is it even taking the time to gather the information about the total number of entries

Because that's how the server-side processing works. It returns:

The data for the current page
The number of records in the result set without filtering (so it can show the end user how many rows have been filtered out)
The number of records in the result with with filtering.

If you want to reduce the total data set you need to apply a suitable WHERE filter.

I would suggest echoing out the three queries that are being executed and check each for performance directly against the database. Then see if you can improve it.

Allan

pastorjustin · August 2016

I think this had something to do with the PHP SQL driver. I found others having similar performance issues. The issues seems to have gotten better after I updated all the SQLPHP drivers to the latest version.

pastorjustin · August 2016

This is kind of a new question, but I wanted to post it on here since all the code is already here.

I'd like to pass a variable from the HTML file to the PHP file that will populate the CUST_CODE.

SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "CUST_DIV = '05' AND CUST_CODE = '123456789'" )

I have this code on the HTML which provides me a box to pass the variable, but I don't know the next piece. I realize that this is NOT a datatables issue, but I figured other folks have done this already and could point me in the right direction.

ID: <input type="text" id="test" ><input type="button" value="Pass Customer ID" onclick="test($('#ID').val());" >

allan · August 2016

Use the ajax.data option if you want to pass parameters to the server-side as part of an Ajax request. There is an example here.

Allan

pastorjustin · September 2016

Can you give me some pointers on how I can have this auto sort on the date column???

allan · September 2016

order.

Allan

pastorjustin · September 2016

This doesn't apply to server side processing does it? I apologize for being a bit of a newbie on this, but I am using the server side processing script as my starting point.

I am assuming that I need to change something in the code below in the ssp.class.php file, but I am unsure where to start. In my example above I want to order by the ORDER_DATE column...

    /**
     * Ordering
     *
     * Construct the ORDER BY clause for server-side processing SQL query
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $columns Column information array
     *  @return string SQL order by clause
     */
    static function order ( $request, $columns ) {
  $order = '';
  if ( isset($request['order']) && count($request['order']) ) {
    $orderBy = array();
    $dtColumns = self::pluck( $columns, 'dt' );
    for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
      // Convert the column index into the column data property
      $columnIdx = intval($request['order'][$i]['column']);
      $requestColumn = $request['columns'][$columnIdx];
      $columnIdx = array_search( $requestColumn['data'], $dtColumns );
      $column = $columns[ $columnIdx ];
      if ( $requestColumn['orderable'] == 'true' ) {
        $dir = $request['order'][$i]['dir'] === 'asc' ?
         'ASC' :
         'DESC';
         $orderBy[] = '['.$column['db'].'] '.$dir;   // revised for SQL Server
      }
    }
  // see "static function limit" above to explain the next line.
  $order =  "ORDER BY ".implode(', ', $orderBy)." OFFSET ".intval($request['start'])." ROWS FETCH NEXT ".intval($request['length'])." ROWS ONLY";
  }
  return $order;
}

pastorjustin · September 2016

Okay, never mind, you are right that works fine for server side as well. I thought I read somewhere that it didn't. Your documentation is amazing.

allan · September 2016

Good to hear that helps :-)

Performance Issues on Larger MSSQL Table - Am I doing Where Clause Properly?

Performance Issues on Larger MSSQL Table - Am I doing Where Clause Properly?

Answers

Howdy, Stranger!

Categories

DataTables

Performance Issues on Larger MSSQL Table - Am I doing Where Clause Properly?

Performance Issues on Larger MSSQL Table - Am I doing Where Clause Properly?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables