DataTables logo DataTables

[SOLVED] How to handle large datasets?
  • I am using DataTables as the front end for an inventory listing site.
    Some users have rather large datasets, in excess of 100,000 records.

    How can I load the full amount of data without getting unresponsive script errors?

    I currently use a paging mechanism and only load 2000 records at a time but it is confusing to some users because
    when they search, it only searches the current 2000 .vs the entire dataset.

    Or, is it possible to determine which of the "pages" of 2000 the record may be in and then reload the DataTable with that set of records and find it then?
    Any ideas?
  • With that large of a dataset, the only reasonable option in my opinion is to use Server-Side Processing.

    http://datatables.net/usage/server-side

    It will allow you to send your server application pagination information, meaning you could retrieve only as many records as you need to keep visible. The search field will send search terms to the server, allowing the filtering to happen in the server, not just the records stored on the client side.

    Using server-side processing, your UI will be responsive and searching/filtering will cover your hundreds of thousands of records.

    Keep in mind, the sample code reflects how you might implement it, but you're not limited in any way. As long as the server can read in the variables from the POST/GET, and return JSON (or other valid data), you're golden.
  • I AM using server-side processing and returning only 2000 rows per page. I am just not sure how to do the searching if the term is not in the currently loaded 2000 rows.
  • I'm clearly missing something then. With server-side processing enabled, you are handing off the filtering to the server side. There should be no reason you'd be doing the search on the subset of 2000 records on the client side, and I'm not sure you even COULD, which leads me to believe there's an issue with the server-side code.

    My best guess (and it's ONLY a guess) is that you're 'paginating' on the SERVER side before doing the search. The server-side code is first saying, "Here are my 2000 records, now let's filter by the parameter I found in the sSearch variable." Whereas the logic should be "Let's filter by the sSearch variable; there are more than 2000 records, so let's paginate them."
  • There is actually two paginations going on. First, in my page code, I'm returning only the first 2000 records, then the DataTable(DT) is paginating based on the user's selection of how many records to display. I am loading the HTML table from the PHP using Smarty like this:
    <table border=0 cellpadding=5 cellspacing=0 width="100%" id="userInvtable">
      <thead>
    	<tr> 
        <th valign="top" width="16" align="center" class="no_sort"><img src="images/cross.png" /><br /><a href="javascript:void(0);" onClick="javascript: checkAll();" id="chk_txt" style="font-size: 8pt;">(check all)</a></td>
        <th valign="top">Part Number</th>
        <th valign="top">Alt P/N#<br>or<br>NSN P/N#</th>
        <th valign="top">Condition<br>Code</th>
        <th valign="top">Quantity</td>
        <th valign="top">Description</td>
        <th valign="top">Last Updated</td>
    	</tr>
      </thead>
      {foreach from=$inventory item=inv}
      <tr bgColor="{cycle name="cycle1" values="#f7f6f1,#BEC3E3"}">
        <td align="center"><input type="checkbox" name="delete[]" value="{$inv.inventory_id}"></td>
        <td><a href="account.php?mode=edit&id={$inv.inventory_id}">{$inv.inventory_part_number|stripslashes}</a></td>
        <td>{$inv.inventory_alt_part_number|stripslashes}</td>
        <td>{$inv.inventory_condition_code|stripslashes}</td>
        <td>{$inv.inventory_quantity|stripslashes}</td>
        <td>{$inv.inventory_description|stripslashes}</td>
        <td>{$inv.last_update|date_format}</td>
      </tr>
      {/foreach}
    </table>
    

    The search only works on the first 2000 rows because that is essentially all I'm loading.

    I took the example, modified it to obtain our data and while it works somewhat, the display gets mucked up and the search returns invalid JSON.
    New code is posted in next comment.

    Now, I am also taking out the Smarty processing of the HTML table in the template. Do I need to do that?
    and why would the display get mucked up?
  • Here's the new backend code:
    <?php
      /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
       * Easy set variables
      */
      
      /* Array of database columns which should be read and sent back to DataTables. Use a space where
       * you want to insert a non-database field (for example a counter or static image)
      */
    
    require_once "libs/Core.php";
    $db = new Database;
    $id = $_SESSION['user_id'];
    
    /*
        <td align="center"><input type="checkbox" name="delete[]" value="{$inv.inventory_id}"></td>
        <td><a href="account.php?mode=edit&id={$inv.inventory_id}">{$inv.inventory_part_number|stripslashes}</a></td>
        <td>{$inv.inventory_alt_part_number|stripslashes}</td>
        <td>{$inv.inventory_condition_code|stripslashes}</td>
        <td>{$inv.inventory_quantity|stripslashes}</td>
        <td>{$inv.inventory_description|stripslashes}</td>
        <td>{$inv.last_update|date_format}</td>
    */
      $aColumns = array('i.inventory_id', 'i.inventory_part_number', 'i.inventory_alt_part_number', 'i.inventory_condition_code', 
                        'i.inventory_quantity', 'i.inventory_description', 'i.last_update');
        /* 
       * Paging
      */
      $sLimit = "";
      if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
        $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
        mysql_real_escape_string( $_GET['iDisplayLength'] );
      }
      
      
      /*
       * Ordering
      */
      if ( isset( $_GET['iSortCol_0'] ) ) {
        $sOrder = "ORDER BY  ";
        for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
          if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) {
            $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
               ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
          }
        }
        
        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" ) {
          $sOrder = "";
        }
      }
      
      
      /* 
       * Filtering
       * NOTE this does not match the built-in DataTables filtering which does it
       * word by word on any field. It's possible to do here, but concerned about efficiency
       * on very large tables, and MySQL's regex functionality is very limited
       */
      $sWhere = "WHERE i.user_id = ".$id;
      if ( $_GET['sSearch'] != "" ) {
        $sWhere = " AND (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
          $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
      }
      
      /* Individual column filtering */
      for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
          if ( $sWhere == "" ) {
            $sWhere = "WHERE ";
          } else {
            $sWhere .= " AND ";
          }
          $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
        }
      }
      
      
      /*
       * SQL queries
       * Get data to display
       */
      $sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
                 FROM linv_inventory i 
                 $sWhere
                 $sOrder
                 $sLimit";
      $rResult = $db->query($sQuery) or die(mysql_error());
      
      /* Data set length after filtering */
      $sQuery = "SELECT FOUND_ROWS()";
      $rResultFilterTotal = $db->query($sQuery) or die(mysql_error());
      $aResultFilterTotal = $db->fetchArray($rResultFilterTotal);
      $iFilteredTotal = $aResultFilterTotal[0];
      
      /* Total data set length */
      $sQuery = "SELECT COUNT(inventory_id) FROM linv_inventory";
      $rResultTotal = $db->query($sQuery) or die(mysql_error());
      $aResultTotal = $db->fetchArray($rResultTotal);
      $iTotal = $aResultTotal[0];
      
      /* Output */
      $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
      );
    
      while ( $aRow = $db->fetchArray( $rResult ) ) {
        $row = array();
        foreach($aColumns as $name=> $value) {
          $row[] = $aRow[$name];
        }
        $output['aaData'][] = $row;
      }
      
      echo json_encode( $output );
    ?>
  • Ok, I finally figure out part of my problem the where clause wasn't getting set correctly.
    Because I set it like this first:
    $sWhere = "WHERE i.user_id = ".$id; (line 60 above)

    this needed to be like this:
    $sWhere .= " AND ("; (line 62 above, note the .= instead of just = )

    The return of the data works fine now but the display is still all mucked up plus I'm not getting any checkboxes; I need those badly.

    Here's how it should look:
    http://www.listinventory.com/Image1.png

    Here's how it looks now:
    http://www.listinventory.com/Image2.png
  • Hard to say. Looks like the table in the 'proper' screenshot is using jQuery UI, and the second one isn't. That doesn't mean the option isn't enabled, but it might mean that it's not finding the CSS related to the jQuery UI styles.

    As for the checkboxes, that's probably not a CSS issue, but I can't say for sure what it is. In my own application, to replace pure data with visual elements and controls, I'm using a combination of fnRowCallback (to implement controls on individual rows) and fnDrawCallback (to add controls to the page or table as a whole after it has been drawn).

    Can't speak intelligently to the templating engine you're using, but I do see a commented area in your code where there's a reference to checkboxes. Is it intentionally commented out? (ie. does the templating engine grab the markup from inside the comments?)
  • As you see, in the old code, where I'm building the table in the smarty template, I'm setting the first column to checkboxes. I don't understand what I need to do to get that using the server-side code.
    Also. I haven't changed anything AFA the CSS is concerned. Why would it not stay with the same style?
  • Added a bit to my previous comment about the commented-out markup. I don't know Smarty. :(

    The only reason for the styles to not work is that the stylesheet isn't found or the classes aren't present. Firebug should help you see if any of your styles have somehow gone missing or gotten mismatched. Could be as simple as missing a wrapper class that used to be there...?
  • That's funny because it is exactly the same code except for the way the datatable is loaded.
    Oh, one difference is in the new one, I'm using 1.7.5 in the old one I'm using 1.6.2
  • At a loss. Not sure if the new script in 1.7.5 appends different classes, or different wrapper classes, etc. There HAS to be a mismatch between the table and the CSS file, though, either in names, specificity, or otherwise. Sorry I've been a pathetic (zero) amount of help. ;-)
  • I can see how some items are named fg-???? and they were changed to ui-????
  • Ok, got some of it. I figure out whay it wasn't looking correctly.
    Forgot to put the bJQueryUI parameter in the init call.

    Now, if I can only get the checkboxes back. Would you use fnRowCallback or fnDrawCallback to put them there is every row required one?
  • Ok, I'm doing this but am unable to center the checkbox in the cell.

                "fnRowCallback": function( nRow, aData, iDisplayIndex ) {
             				$('td:eq(0)', nRow).html('<input type="checkbox" name="delete[]" value="'+aData[0]+'">');
                    $('td:eq(0)').css('align', 'center');
                			return nRow;
                }
    

    Is there a trick to getting it center aligned?
  • Ok, got it worked out like this:
    "aoColumnDefs": [{ "sClass": "center", "aTargets": [ 0 ] }],
    

    And added a td.center selector in my CSS.
  • Glad to hear you've got it working!
This discussion has been closed.
All Discussions