Performance is not faster with Client side processing + defer Render

swetha_pendy

I am trying to use AJAX request using Python/Flask which is fetching 35k rows. It is taking 5 minutes to display the table even though I am exactly using the Client side processing (AJAX request + defer Render). I have no issues displaying the data but the problem is even though I use the client side with defer Render , the performance is very very slow , it is taking 5 minutes to load the data.

After reading FAQs of performance related , I went with client side processing as it is much easier than server side processing. Also suits to my use case as I read client side supports performance up to 50k rows.

Note - I have 15-20 tables which are all just 100 rows of data except for one table which is 35k rows.

Please advise if I am missing anything.

swetha_pendy

I tried alternative way of getting json through data attribute ( data : {{json_data | safe}}. "json_data" is being passed from Python to HTML template. Even performance for this solution is very slow , its taking 5 minutes to display 35k rows.

Infact whether I choose this "data" attribute or "AJAX based client side processing with defer render" - both are taking 5 minutes to display data. I commented out the data attribute.

kthorngren

I would use the browser's developer tools to determine if the delay is in fetching the data or in Datatables rendering the table once the XHR response is received. That will tell you if deferRender will help.

I would consider the numbers in the FAQ to be general guidelines. There may be more complex rendering for some tables that take longer than others with the same amount of data.

Can you post a link to your page or PM Allan with the link so the performance can be reviewed?

Kevin

swetha_pendy

So, I opened the network tab , attaching the screenshot. So the ajax request - reload_{{cat}} which is reload_kpi_fields is taking 2.5 minutes in getting the 55.1 MB data ? Can you please advise what I need to do to improve the performance?

kthorngren

Use server side processing so only the page of data is loaded. You will need server code to support the SSP protocol. This Flask library looks old but might still work.

Kevin

swetha_pendy

Ok thank you. But Out of 20 tables, It is only one table that is processing 35k rows and I am just trying to understand if I can get client side processing with defer render work for my use case.

swetha_pendy

Also does search builder work if server side is set to true ?

I understand pagination , sorting , filtering can be addressed from server side. But what happens to the search builder , column visibility & export functionality?

colin

SearchBuilder is fine with server-side processing - see example here . The text at the top of the page gives links and pointers on how to implement it.

Column visibility is fine too , you just need to set columns.visible in the config.

And finally, export - this doesn't work out of the box. By default, export only exports data in the table, which with SSP is purely the visible data, and not the rest on the server. There is a long-term plan to resolve this, but in the meantime, there are workarounds discussed on this forum, such as here, here and here,

Colin

allan

Defer render won't help that 2.5 minute download of 55MB of data. The data is still downloaded, parsed and then DataTables would sort / filter the table and draw just the 10 rows that are needed. That's the key thing with deferRender - the nodes for the rows / cells are only created when needed for display.

With a 55MB download I'd say there is no question that you need server-side processing - then the server can reply with just the 10 rows needed for display.

Allan

swetha_pendy

I keep getting the datatables error as below. Please see the screenshots. I turned on the server side and my python flask code returns the expected parameters for datatables. The json response is valid as well. But looks like the datatables/html is not getting the json response. Please see screenshots and guide me.

python code:

swetha_pendy

datatables error:

DataTables warning: table id=sorted - Requested unknown parameter 'id' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

Save to file
Copy to clipboard
Close

swetha_pendy

Datatables error on left and preview/response from Network tab on right. Please see below screenshots.

swetha_pendy

This is preview & response from Network tab

swetha_pendy

I get the JSON response as well , but it looks like datatables rendering issue, please see this screenshot and advise.

swetha_pendy

This is my datatables.

kthorngren

You have columns.data defined, ie, { data: "id" }. You are returning an array of arrays. Instead you need to return an array of objects. See the Data docs for details.

In Python terms you need to return a list of dictionaries.

Kevin

swetha_pendy

Thank you. It worked but the pagination is not correct. How can I get "Page 2 , Next, Last etc" to be enabled so user can click to fetch next 10 rows using server-side? Right now it only fetches through that show entries dropdown. But how about the pagination? I want to show the total number of records split into multiple pages for small tables atleast.

Also how will the filtering work? Does the filter value only applies for those 10 rows for existing page? I would like the filtering to be applied for the total records, not just for the ones one existing page.

swetha_pendy

For pagination , especially when I have 32k rows , how can the user see the number of pages on the bottom ? and when user enters filter value , the searching has to be on entire table but table can display based on start/length, but user should be able to click the next pages of data to see the filtered records

kthorngren

Guessing your recordsFiltered value is incorrect. Is it 10? If it is likely that is incorrect. See the Server Side Processing Returned Data docs for details of what should be returned. If you still need help then post the JSON response using the browser's network inspector.

Kevin

swetha_pendy

Thank you. Thats right recordsFiltered was incorrect.

On another note, I am implementing searchBuilder on server side and I used the editor libraries and set searchbuidler enabled and set server side to true. I notice the dropdownlist is missing for "equals" , Also its not filtering the results. Am I missing something ?

using search builder , buttons and editor libraries:

swetha_pendy

swetha_pendy

Also please advise if I can still the custom conditions for search builder using editor libraries ? In the first place I am trying to get the search builder work with editor libraries ( server side enabled). All I added was the below js and css:

https://editor.datatables.net/extensions/Editor/js/dataTables.editor.min.js
https://editor.datatables.net/extensions/Editor/css/editor.dataTables.min.css

swetha_pendy

search builder version 1.4

swetha_pendy

$("#sorted").DataTable( {
dom: 'RlBfrtip',

"processing": true,
"serverSide": true,
"info": true,
"ajax": "/datatables_{{cat}}",
"columns" : {{kv_fields | safe}},
"orderClasses": false,
"bStateSave": true,
"aaSorting": [],
"stateSaveParams": function (settings, data) {
var temp = {};
$('#sorted thead input').each(function(n) {
temp[ $(this).attr('placeholder') ] = this.value;
});
data.colsFilter = temp;
},
"stateLoadParams": function (settings, data) {
$.each(data.colsFilter, function(key, val) {
$('#sorted thead input[placeholder="'+key+'"]').val(val);
});
},
"sPaginationType": "full_numbers",
responsive: false,
paging: true,
pageLength: 10,
lengthMenu: [10, 25, 50, 100, 250, 500],
colReorder: true,
orderCellsTop: true,
fixedHeader: true,
select: {
style: 'multi'
},
language: {
search: 'Filter',
searchPlaceholder: 'Filter rows based on user string in any field'
},
columnDefs: [{targets: '_all', orderable: false, searchable: true}],
buttons: [
{
text: 'Copy',
action: function(e, dt, node, config){
var data = document.getElementById('sorted');
var range = document.createRange();
range.selectNode(data);
window.getSelection().addRange(range);
document.execCommand('copy');
}
},
'excelHtml5',
'csvHtml5',
{extend : 'pdfHtml5',orientation : 'landscape',pageSize : 'A0',titleAttr : 'PDF'},
'print',
{extend: 'colvis', collectionLayout: 'fixed four-column'},
'searchBuilder'





]
});

kthorngren

See the SearchBuilder Server Side Docs for answers to your questions. Specifically this paragraph:

There are two caveats that SearchBuilder's server-side integration carries. The first is that anywhere select elements would normally be used on the client-side, input elements are used instead. This reduces strain on the server significantly, drastically improving performance. The second is that custom conditions are not supported as these would require a custom server-side condition rather than one that can be used on the client side.

Kevin

swetha_pendy

ok, as per this screenshot - I think may be I am using client side search builder ( with server side enabled to true ). I am not passing any search builder parameters to server side. This is supposed to work with search builder ( running on client side ) with server side enabled to true ? Correct ? I removed the editor libraries. I am not sure why it isn't working.

allan

This is supposed to work with search builder ( running on client side ) with server side enabled to true ?

No - not at all. If you have serverSide enabled then the server is responsible for all search, sort and paging - including SearchBuilder.

We'd need a link to your page showing the issue to be able to debut it further.

Allan

swetha_pendy

So, its little painful to create seatch builder from scratch with server side processing (python). The below screenshot shows the request arguments from search builder. i am not sure how I should be passing them to server side with each individual search builder request here. Is there a way or an API that I can use to get all the nested conditions in one shot that I can send to server side ? I tried to use searchBuilder.getDetails but it wasn't fetching me anything.

kthorngren

The SearchBuilder parameters will need to be parsed at the same time as the other parameters, like the columns, sent in the request. You won't be able to use searchBuilder.getDetails() to send to the server independently to process the searches. The reason is Datatables expects the response from the request you posted above.

Not sure if you created you own code for Python server side processing or are using a library but it will need to be updated to parse the searchBuilder params to build the proper data queries. It seems to me that building the queries will be the difficult part.

Kevin

allan

See also the server-side processing documentation for SearchBuilder. We provide implementations in .NET, Node.js and PHP. Not one in Python I'm sorry to say.

Allan