Using a Dataset of 110 000 records, how should I proceed?

timcadieux

First i'd like to compliment you on your great work, it's nice to fidn something that actually works as described!

My question is multi-part.

I have a database of 110 000 records that I have loaded into a JSON (14meg) file. It works, but I'd like to show a more prominent loader while the data loads becuase it looks like it isn't doing anything. Now, looking through previous posts, I used the below, but I'd like to hide the table altogether until the loading is done, I can't seem to get that to work just yet.
[code]#processingDiv{
position:absolute;
top:45%;
left:45%;
width:200px;
height:40px;
z-index:101;
text-align:center;
font-size:150%;
padding:10px;
background-image:'../images/ajax-loader white on black.gif';



}

$(document).ready(function() {

$('#processingDiv').show();



oTable = $('#example').dataTable( {
"sAjaxSource": '../examples_support/json.txt',
"bSortClasses": false,
"sPaginationType": "full_numbers",
"aoColumns": [
/* Engine */ { "bSearchable": true,
"bVisible": false }
/*null*/,
/* Browser */ null,
/* Platform */ null,
/* Grade */ null,
/* Grade */ null,
/* Grade */ null,
/* Grade */ null,
/* Grade */ null,
/* Grade */ null
],
"fnInitComplete": function() { $('#processingDiv').hide(); } // Hide processingDiv when loading table is complete
} );

$("thead input").keyup( function () {
/* Filter on the column (the index) of this element */
oTable.fnFilter( this.value, $("thead input").index(this) );
} );



/*
* Support functions to provide a little bit of 'user friendlyness' to the textboxes in
* the footer
*/
$("thead input").each( function (i) {
asInitVals[i] = this.value;
} );

$("thead input").focus( function () {
if ( this.className == "search_init" )
{
this.className = "";
this.value = "";
}
} );

$("thead input").blur( function (i) {
if ( this.value == "" )
{
this.className = "search_init";
this.value = asInitVals[$("thead input").index(this)];
}
} );
} );





Please wait while the content is being loaded [/code]
Secondly, I'm wondering if I should be looking at the server side code becuase when I try to type something into any one of the filters, it takes at least another 20-30 seconds before the browsers responds and displays the letter I typed!

Any help is appreciated, thx!

allan

Hi timcadieux,

I'd say no two ways about it - server-side processing is the way to go! 110'000 records is a heck of a lot of data for the client side to deal with - it would be much better to have a database engine which is optimised for this kind of thing do all the heavy lifting.

If you do want to proceed with what you are doing you might be able to wrap the whole lot in a DIV and set that to display:none. Then when the processing is done (fnInitComplete()) you would then show the div. But I would seriously consider using server-side processing for 14MB of data! :-)

Allan

timcadieux

Thx for the reply. I've made the server-side page, but there's an awful lot of queries in there (this is my first foray into PhP !!) and so I'm not sure if it's my fault that this is not doing what I want.

I'm just trying to use ORDER by to get the initial load to appear in Alpha but it doesn't take. I've added it to the 2nd query. It seems to be the correct one as if I LIMIT it to 50 records, this applies, just not the ORDER BY ?

Also, how can I remove the single Search box at the top right, I'm going to use the individual filters instead ( if possible, haven't seen an Example page for this anywhere?).

Thx for your help.

allan

HI timcadieux,

I think you'll need about three different queries to get all of the information needed for each reply to DataTables. The second two should be lightening fast as they are really just counters, so I think this should be okay, as long as your database has the right indexes etc.

Are you using PHP similar to my example: http://datatables.net/1.5-beta/examples/data_sources/server_side.html ? The order by etc shown there might be quite useful to you.

Regarding removing the filtering box, but keeping filtering featured on, you can use the sDom option ( http://datatables.net/usage#sDom ) and simply drop the 'f' option for the filter input, and it won't be drawn. sDom in action can be seen here: http://datatables.net/1.5-beta/examples/basic_init/dom.html

Regards,
Allan

timcadieux

Allan, I'm trying something a little different, I love how fast the AJAX runs so what I was going to do is build a JSON file for each letter of the Alphabet. That's not my question.

I've created a JSON file, in alpha order, I can look in the file and it's correct, but when the Grid loads it, the names are no longer in alpha order?

Ideas?

Thx

allan

Hi timcadieux,

So if I understand correctly you have 26 files [a-z].json (or whatever) and you load each one as needed using something like fnReloadAjax() right? Sounds reasonable. Ordering in DataTables is done entirely by sorting, so once your json file has loaded, DataTables will sort it. You can override this by setting aaSorting to be an empty array.

Allan

timcadieux

Right, I cleared out the Search box. I'm not clear on how I would set aaSorting to an empty array, the few things I've tried haven't worked.

allan

Hi timcadieux,

Perhaps something like:

[code]
var oSettings = oTable.fnSettings();
oSettings.aaSorting = [];
[/code]
This should cause DataTables to not sort - if this is what you want.

Allan