Slow Rendering of 10,000 Rows.

MacMan

Hi
I'm pulling data from an old server that is using ldap.
I can loop through each user row and get all 10,000 results. Using plain php4 & ldap I can put all the variables on screen in about 10 seconds.
Which isn't to bad for this server.

When I use this code with datatables it tables around 3 minutes to render it to screen..
Why such a big difference ? I had considered server side processing, but as the server is old and running ldap I can't get specific entries or do very much filtering..

Any ideas ?

[code]



@import "test_page.css";
@import "test_table.css";







$(document).ready(function() {
oTable = $('#xtest').dataTable({
"bJQueryUI": true,
"bProcessing": true,
"bSort": false,
"bSortClasses": false,
"bDeferRender": true,
"sPaginationType": "full_numbers",
"aLengthMenu": [[10, 25, 50, 100, 250, 500, 1000, 5000, -1], [10, 25, 50, 100, 250, 500, 1000, 5000, "All"]]
});
$('#xtest').dataTable().makeEditable({
sUpdateURL: "update.php",
"aoColumns": [ null,{},{},{},{},{},{},{} ]
});
});




<?php
print "Users";
echo "";
echo "
 IDnamenumberlocalephoneemaildescription
 IDnamenumberlocalephoneemaildescription";

$sr = ldap_list($ds,"cn=UK,cn=users","(&(objectclass=userclass))",array('dn'));
$info=ldap_get_entries($ds,$sr);

for($m=0; $m<$info['count'];$m++) {
$dn=$info[$m]['dn'];

$sr2 = ldap_read($ds,$dn,"(objectclass=userclass)",array('id','name','number','locale','phone','email','desc'));
$info2 = ldap_get_entries($ds,$sr2);
for($n=0; $n<$info2['count'];$n++) {
echo "{$info2[$n]['id'][0]}{$info2[$n]['name'][0]}{$info2[$n]['number'][0]}{$info2[$n]['locale'][0]}{$info2[$n]['phone'][0]}{$info2[$n]['email'][0]}{$info2[$n]['desc'][0]}";
}
}
echo "";
echo "";
?>

[/code]

The above is an example of how I'm using datatables, I also have editable included..

Any way I can speed this up ? I'm running datatables 1.9.4.

Many Thanks

MacMan

Anyone any ideas on this ?
Is it normal for it to take so long ?

Thanks

allan

4 minutes... - no that's not normal. Does removing makeEditable make any difference?

Do you have the option of Ajax loading the data? Using bDeferRender has no effect at the moment since the DOM nodes have already been created!

Allan

MacMan

Hi Allan
Thanks for taking the time to reply. Much appreciated.

The issue I have is using LDAP. I don't have much control for searching and filtering.
The LDAP server is quite old, so it's all or nothing based on a specific query.

I'm getting the 10,000 DN values and put then in an array ( less then .5 of a second) , then looping through each of those to get the 7 values from each DN. ( about 8 - 9 seconds )

Direct to screen this total process is around 10 seconds. I could write that to an array instead of screen, but it's not getting the info that seems slow.. it's putting it on screen in databtables.. I just don't know why.

I don't need any column sorting, but searching and pagination are needed.

Deleting makeEditable doesn't seem to have speeded it up.

Any other ideas ?

Thanks

allan

A javascript array will certainly help since you will be able to get the benefit of deferred rendering then. At the moment the browser is creating 10'000 rows, and then reading them all back from HTML, which is horribly slow (although it shouldn't be 4 minutes slow!). Dumping it into an array and then feeding it to DataTables using the aaData option to pass the data in would be my first suggestion.

Allan

MacMan

Thanks.

I can get the data from ldap into an array, but how do I get it from that into datatables ?
Don't understand that bit !

Thanks

allan

An example is with me whittering on for hours: http://datatables.net/release-datatables/examples/data_sources/js_array.html :-)

Allan

MacMan

This is how I've got the data coming out to $data[];

[0] => Array
(
[0] => 0
[1] => c89b0100-5926-5412-5894-008045215221
[2] => Mark
[3] =>
[4] => 6000
[5] => 1
[6] => USA
)

[1] => Array
(
[0] => 1
[1] => 2fa40800-4e26-7d93-3502-008045215221
[2] => John
[3] =>
[4] => 6001
[5] => 1
[6] => UK
)

Obviously more entries that that.. is it going to be usable in that format?

MacMan

Just seen your update.. I'll have a read of that as well.

Thanks :)

MacMan

I've sort of got this working with 3,000 entries using aaData.
I've read the values from LDAP to a php array and then used this to convert it to a JSON array.
http://www.sencha.com/forum/showthread.php?2362-Getting-the-JSON-examples-to-work-on-PHP4.x&p=12082&viewfull=1#post12082

Pagination is happening and it looks fine.
I've enabled editable using

$('#example').dataTable().makeEditable({
sUpdateURL: "update.php",
});

But it would appear only the first 10 rows can be edited. By default I'm showing 10 rows, If I view more only the first 10 can be edited.

When I click in the cell and change the values, clicking return shows a server error:
Cell can not be updated (server error).

Any ideas on this ?

MacMan

Getting much nearer..

I've got the data coming into an array and then the array is convert to JSON.
This is now displaying fine in the table.

I've added id's and now Jeditable is working.. but.
I have pagination enabled and by default it's showing the first 10 rows.

These rows are fully editable and the results are updating my database correctly.
Any cell in the first 10 rows that has no data shows NOT SET. All this is fine.

Rows 11 onwards aren't editable and any cells with no data are shown as blank.
It's almost as if it's only fully processed the first 10 rows.

This is an example of the code I'm using:

[code]

$(document).ready(function() {
$('#demo').html( '' );
$('#example').dataTable( {
"aaData": <?php echo $result ?>,
"aoColumns": [
{ "sTitle": "Order" },
{ "sTitle": "Name" },
{ "sTitle": "Rank" },
{ "sTitle": "Number", "sClass": "center" },
{ "sTitle": "Locale", "sClass": "center" },
{ "sTitle": "Email", "sClass": "center" },
{ "sTitle": "Description", "sClass": "center" }
],
"bJQueryUI": true,
"bProcessing": true,
"bSort": false,
"bSortClasses": false,
"bDeferRender": true,
"sPaginationType": "full_numbers",
"aLengthMenu": [[10, 25, 50, 100, 250, 500, -1], [10, 25, 50, 100, 250, 500, "All"]],

"fnRowCallback": function( nRow, aData, iDisplayIndex ) {
var id = "users"+":"+aData[0];
$(nRow).attr("id",id);
return nRow;
}
} );
$('#example').dataTable().makeEditable({
sUpdateURL: "update.php",
});
} );

[/code]

Any ideas why only the first 10 rows ? If I do this and pull the data direct from the LDAP all rows can be edited but it's horribly slow at loading the page!

Thanks

MacMan

If I add [code]"iDisplayLength": 50[/code]
The the page shows the first 50 records and all of these can be edited. Rows 51 onwards can't !

Help :)

allan

Urgh - I think that's a limitation of the makeEditable plugin (which has been deprecated by the author). Its probably using static events, which is why this is a problem. It would need to be updated to use delegated events for deferred rendering.

Allan

MacMan

I think your correct..
If I set "bDeferRender": false,

Then it works..

I'm a bit confused here, I'm calling
[code]


[/code]

Are both needed ? are they the same ?
Any pointers as to how to get this working ? Thanks :)

allan

Not the same - the editable file is a different script - one which added editing abilities to the table. Presumably you downloaded it from here: http://code.google.com/p/jquery-datatables-editable/ . Its a third party plug-in, which as I say is marked as deprecated, so I'm not sure the original author will be able to provide much support. But if you want to use it, either it needs to be updated to support the newer features of DataTables or you need to disable the deferred rendering.

Allan

MacMan

Hi Allan
Thanks for all the help and advice.
Hopefully last one for now :)

What is the recommended plugin for editing cells in tables ?
Is it Jeditable as per http://datatables.net/examples/api/editable.html ?

Thanks

allan

Personally I would say Editor: http://editor.datatables.net . But then it is my product :-)

Allan

MacMan

Thanks :)
I've had a quick look at Jeditable and have the same issue when bDeferRender is True.
Is there anyway around that using Jeditable ?

MacMan

Not sorted.. Thought I had it for a moment. But then I realised I'd left bDeferRender: False !

Ahh...

allan

You'd need to reapply the editable component whenever a row is created - fnCreatedRow . Or better yet, just use a delegated method. But I'm not sure how jEditable works with that.