Loading 2500 rows with Datatable editor taking even with "Breferender"
Loading 2500 rows with Datatable editor taking even with "Breferender"
Hi all,
I am using Datatable Editor but when it load the data (server side) it's taking too long to display the result (between 7 and 10 secs).
I tried to use "Referender" to true but even with this it's the same (still taking between 7 and 10 secs to load).
Here my Js code if someone could help me it will be nice :)
(function($){
$(document).ready(function() {
var editor = new $.fn.DataTable.Editor({
"ajax": "php/example.php",
"table": "#example",
i18n: {
create: {
button: "Nouveau",
title: "Créer nouvelle entrée",
submit: "Créer"
},
edit: {
button: "Editer dossier",
title: "Modifier entrée",
submit: "Actualiser"
},
remove: {
button: "Supprimer",
title: "Supprimer",
submit: "Supprimer",
confirm: {
_: "Etes-vous sûr de vouloir supprimer %d lignes?",
1: "Etes-vous sûr de vouloir supprimer 1 ligne?"
}
},
error: {
system: "Une erreur s’est produite, contacter l’administrateur système"
}
},
"fields": [
{
"label": "Nom",
"name": "Nom",
"type": "text"
},
{
"label": "Pr\u00e9nom",
"name": "Prenom",
"type": "text"
},
{
"label": "Ville",
"name": "Ville",
"type": "text"
},
{
"label": "Num\u00e9ro rue",
"name": "Num_rue",
"type": "text"
},
{
"label": "Nom rue",
"name": "Rue",
"type": "text"
},
{
"label": "Code postal",
"name": "Code_postal",
"type": "text"
},
{
"label": "T\u00e9l\u00e9phone",
"name": "Telephone",
"type": "text"
},
{
"label": "Email",
"name": "Email",
"type": "text"
},
{
"label": "Num\u00e9ro de s\u00e9rie",
"name": "Num_serie",
"type": "text"
},
{
"label": "Num\u00e9ro de commande",
"name": "Num_commande",
"type": "text"
},
{
"label": "Date formulaire",
"name": "Date",
"type": "text"
},
{
"label": "Date d'achat",
"name": "Date_achat",
"type": "text"
},
{
"label": "Nom produit",
"name": "Nom_produit",
"type": "text"
},
{
"label": "Cat\u00e9gorie produit",
"name": "Categorie_produit",
"type": "text"
},
{
"label": "Marque",
"name": "Marque",
"type": "text"
},
{
"label": "Couleur",
"name": "Couleur",
"type": "text"
},
{
"label": "Revendeur",
"name": "Revendeur",
"type": "text"
},
{
"label": "Diagnostic client",
"name": "Diagnostic_client",
"type": "text"
},
{
"label": "Statut",
"name": "Statut",
"type": "text"
}
]
} );
var table = $('#example').DataTable( {
//accelerer l'affichage de la table
"bDeferRender": true,
//sauvegarder les choix
//stateSave: true,
"dom": "TfrtlipS",
//bouton permettant le choix d'affichage des colonnes
dom: 'C<"clear">Tfrtlip',
"colVis": {
"buttonText": "Choix affichage"
},
"ajax": "php/example.php",
//nompbre de ligne sur le tableau à afficher
"iDisplayLength": 15,
//scrollbar
"scrollY": "450px",
"columns": [
{
"data": "id"
},
{
"data": "Num_sav"
},
{
"data": "Nom"
},
{
"data": "Prenom"
},
{
"data": "Nom_produit"
},
{
"data": "Statut"
},
{
data: null,
defaultContent: '<button style="color:#0099DD;" "href="#" class="editer">Editer</button>',
orderable: false
},
],
"order": [[1, 'asc']],
//bouton à afficher : suprimer, nouveau dossier, editer
"tableTools": {
"sRowSelect": "os",
"sSwfPath": "copy_csv_xls_pdf.swf",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit",
sButtonClass: "editor_edit",
editor: editor,
formButtons: [
{
label: "<",
fn: function (e) {
this.submit( function () {
var tt = $.fn.dataTable.TableTools.fnGetInstance('example');
var row = tt.fnGetSelected()[0];
var rows = table.rows( {filter:'applied'} ).nodes();
var index = rows.indexOf( row );
tt.fnDeselect( row );
if ( rows[index-1] ) {
tt.fnSelect( rows[index-1] );
$('a.editor_edit').click();
}
}, null, null, false );
}
},
{
label: "Sauvegarder modification",
fn: function (e) {
this.submit();
}
},
{
label: ">",
fn: function (e) {
this.submit( function () {
var tt = $.fn.dataTable.TableTools.fnGetInstance('example');
var row = tt.fnGetSelected()[0];
var rows = table.rows( {filter:'applied'} ).nodes();
var index = rows.indexOf( row );
tt.fnDeselect( row );
if ( rows[ index+1 ] ) {
tt.fnSelect( rows[index+1] );
$('a.editor_edit').click();
}
}, null, null, false );
}
}
]
},
//{ "sExtends": "editor_remove", "editor": editor }
{
sExtends: 'select_single',
sButtonClass: 'marginLeft',
sButtonText: 'Exporter',
fnClick: function () {
if (confirm("Télécharger la base de données sous format CSV ?")) {
window.open('ExportationBD/ExportationCSV.php',"_self");
}
},
},
{
sExtends: 'select_single',
sButtonClass: 'marginLeft',
sButtonText: 'Importer',
fnClick: function () {
window.open('ImportationBD/index.html',"_self");
},
},
]
},
} );
} );
}(jQuery));
This question has accepted answers - jump to:
This discussion has been closed.
Answers
Could you possibly link to the page so we can take a look please? That seems like a very long time for the data to load and I would like to take a look and see how long the server is taking to answer and send the data for the Ajax request DataTables is making.
Allan
Can I send it to you in private please? Because It's my private database that I use.
Sure - click on my name above and then "Send message".
Allan
I sent you the Link in private yesterday :)
Ran out of time to reply yesterday - will try to look at it shortly.
Allan
Hi,
Just had a look and I immediately get a 500 error from the Ajax request at the moment on the page you linked to.
Thanks,
Allan
Hi Allan,
I am sorry I tested the link this morning and I saw the "Server error". When it will be fixed I will tell you.
Thx again :)
Back,
Problem fixed :)
The error doesn't appears now.
Thanks. Looking at the developer tools in Chrome it shows that the 1.1MB of data that is loaded via Ajax is taking 4.66 seconds to download for me. It then only takes a fraction of a second to display the data in the table, so it isn't DataTables which is slow, but the data transfer.
You could consider using server-side processing so only the data needed for each page in the table is loaded.
Allan
Actually - if you are using Editor and its PHP libraries, then just enable the
serverSideoption and have DataTables make the request via POST. There is an example hereAllan
Thank you sooo much dear Allan, now my Datatable display the data in 123ms instead of 4-7-10 s!
I will just show the code if someone have the same problem :).
$('#example').DataTable( {
.
.
.
ajax: {
url: "../php/staff.php",
type: "POST"
},
serverSide: true,
.
.
.
)}
Thanks again for all of what you are doing :)
Back ^^,
I wrote the code given above but they are some errors apearing like :
" Uncaught Unable to automatically determine field from source. Please specify the field name " = > when I click on any line
" Uncaught TypeError: Cannot read property 'nodeName' of undefined " => When I click on the button "next" using the "Previous / next editing buttons"
" DataTables warning: table id= myTable - Unknown field: (index 6) " => When I want to search something in the input "Search"
Do I have to change or remove something in my code ?
Thanks in advance.
NB : I checked, when I remove the server-side processing everything is going right except of course the slowness of the loading.
Anyone know how to solve this problem ?
I am sure it's nothing but I can't find where is the bug ^^
This tech note should help.
Allan
Hi Allan,
I read the tech note but I didn't understant the 2 options :(
Best option is to use 1.4 (beta currently) and the new
editFieldoption.Allan
I downloaded the 1.4 beta, but I don't understant how to use
editFieldoption in the example given. It says that I have to change " { data: "manager.name" } " by " { data: "manager.name", editField: "manager.id" } ". But what I don't understant is the " "manager.id" , my JSON data are not structured like the example. So I tried to write :"columns": [
{
"data": "id",
"editField": "id"
},
Doesn't work :(
The
editFieldoption simply tells Editor what field to edit when inline editing on that column is activated. You give it the name of the field you want to be edited, matching the name used in the Editor construction (fields.name) (for exampleNom_produitin your case, or any of the other fields).Allan
Hi Allan,
Thanks, I understand know so what about the other error messages ?
" Uncaught TypeError: Cannot read property 'nodeName' of undefined " => When I click on the button "next" using the "Previous / next editing buttons"
" DataTables warning: table id= myTable - Unknown field: (index 6) " => When I want to search something in the input "Search"
I don't see that error on the page you sent me before.
Use
columns.searchableto disable searching on the final column.Allan
I added the
columns.searchable(false) and the error doesn't appears now thanks alot :DBut :
" Uncaught TypeError: Cannot read property 'nodeName' of undefined " => When I click on the button "next" using the "Previous / next editing buttons"
This error appears when I click on the button "Editer dossier" and then when I click on the "next/previous" button (API Previous / next editing buttons).
Thanks for clarifying that. The problem is that you are using server-side processing while the code for the buttons that you have taken from my example assumes client-side processing.
Since the rows for the next page don't exist on the client-side you would need to add a little bit of extra logic to check and see if the next row exists before progressing on it to. That check would be around the point where you have
rows[ index+1 ]. Just add a trivial check to make sure it exists before using it. Likewise with the previous button.Allan
If I understood well what you said I had to verify if the next row exist before progressing on it. The next row is defined by
rows[ index+1 ]so I wrote :...
if ( typeof rows[index-1] != 'undefined') {
Still got the error.
I would suggest adding some debug code and inspecting what
rowsandindexare and check that is the correct logic check to use.Allan
There is something strange by the way. When I click for the first time on the "next Button" it display the next row but when I click a seconde time the error appears.
It seems that the
rows[index +1]is known on the first click but undifined on the second click.Edit :
I tried :
console.log(index);, if I select the third line and then when I click on the "next button" the console shows : " 3 ". But when I click a second time on the "next button" it shows "-1" .I am lost xD
Sorry to disturb you Allan or anyone else but if you can help me please, because I don't know what to do.
I'll try to make some time next week to update my example to work with server-side processing. If you need it urgently priority support is available.
Allan
Ok Thanks Allan I will wait until next week.
Thanks for all :)
Heya Allan,
I know that you have a lot of work,I just wanted to know if you took a look about the API example
Previous / next editing buttonsto work with server-side processing ?Thanks :)