[SOLVED] How to handle large datasets?

MrBaseball34

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?

GregP

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.

MrBaseball34

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.

GregP

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."

MrBaseball34

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:
[code]




(check all)
Part Number
Alt P/N#
or
NSN P/N#
Condition
Code
Quantity
Description
Last Updated


{foreach from=$inventory item=inv}


{$inv.inventory_part_number|stripslashes}
{$inv.inventory_alt_part_number|stripslashes}
{$inv.inventory_condition_code|stripslashes}
{$inv.inventory_quantity|stripslashes}
{$inv.inventory_description|stripslashes}
{$inv.last_update|date_format}

{/foreach}

[/code]

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?

MrBaseball34

Here's the new backend code:
[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'];

/*

{$inv.inventory_part_number|stripslashes}
{$inv.inventory_alt_part_number|stripslashes}
{$inv.inventory_condition_code|stripslashes}
{$inv.inventory_quantity|stripslashes}
{$inv.inventory_description|stripslashes}
{$inv.last_update|date_format}
*/
$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 ; $ifetchArray($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 );
?>[/code]

MrBaseball34

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

GregP

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?)

MrBaseball34

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?

GregP

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...?

MrBaseball34

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

GregP

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. ;-)

MrBaseball34

I can see how some items are named fg-???? and they were changed to ui-????

MrBaseball34

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?

MrBaseball34

Ok, I'm doing this but am unable to center the checkbox in the cell.

[code]
"fnRowCallback": function( nRow, aData, iDisplayIndex ) {
$('td:eq(0)', nRow).html('');
$('td:eq(0)').css('align', 'center');
return nRow;
}
[/code]

Is there a trick to getting it center aligned?

MrBaseball34

Ok, got it worked out like this:
[code]
"aoColumnDefs": [{ "sClass": "center", "aTargets": [ 0 ] }],
[/code]

And added a td.center selector in my CSS.

GregP

Glad to hear you've got it working!