What is the limit of records we can deal with?

redani

Hello everybody,
I'm very interested in using this nice plugin, but i'm wondering how i can use it with a lit of items of many thousands records.
let's say i would like to use it to display the list of clients. what is the best way to do so?

Thank you so much in advance.
Cheers
Reda

john_l

If you have too many rows to get good performance, then the first things to try are using Deferred Rendering (so only the currently visible rows are in the page DOM), and use an AJAX data source (so datatables doesn't have a big initial hit parsing all the table info out of the DOM).

If that's not enough, then you should look into server side processing, where the heavy lifting is done on the server, and the browser only has a small section of the table to show at any time.

redani

Thank you John for your reply.
1- Is there a doc for using the Deferred Rendering with datatable?
2- when using the search box, does it search in only the rendered results or can it perform a search via ajax in all the database?

to display the clients, i'm thinking of using the alphabet, i mean display all name starting with "A" and if I click in "C" then i'll display all name starting with "C"
3- do you think it's a good idea? and can this be done with datatables?

Thank you

allan

Deferred Rendering with data table

Yes - deferRender.

You might also be interested in the speed FAQ.

Allan

redani

Thank you Allan for your reply.
any answers for question 2 and 3?

allan

Sorry - missed them.

1- Is there a doc for using the Deferred Rendering with datatable?

deferRender.

2- when using the search box, does it search in only the rendered results or can it perform a search via ajax in all the database?

It will do an Ajax search if you have server-side processing enabled (manual), but if you want to load different results, you would use the API - specifically ajax.reload() or ajax.url().

Allan

redani

OK, thanks a lot Allan ;)

redani

Hi,
I've just started testing datatables and it's amazingly easy to implement and the doc can't be clearer, thank you for this nice plugin ;)

I for the moment have 2 questions please:

1- I'm loading a list of roughly 3000 records and i'm using "deferRender": true as you have recommended, but it's still very slow to load around 8/ 9 seconds (2 or 3 seconds faster with deferRender option). Is that normal?

2- is it possible to make the table responsive?

Thanks a lot

tangerine

1. 8/9 seconds for only 3000 records is definitely not normal for DataTables. I'm getting over 100,000 in 1 second.

2. http://datatables.net/extensions/responsive/

redani

wow, thanks tangerine, for the prompt reply :)

1- indeed, that's pretty fast :) are you using ajax or server side option?

2- great, thank you, didn't see that ;)

tangerine

Sorry - should have said I use server-side, although ajax for 3000 records should be much faster than 8/9 seconds.

redani

it's okay :)

well, for the moment, i only have around 3500 records, but it's gonna be growing soon and i'm thinking of 2 solutions:
1- display only the last 100 records, and when using the search field i perform a search in the database and display the matching results
2- display the records using the alphabet, for example if you click on the lettre "C", i display records that start with the letter "C"

what do you think about that? or do you have a better idea?

allan

That's an option, but you might be best considering using server-side processing. It would likely be easier to develop.

Having said that, DataTables should cope just fine up to around 50'000 rows for client-side processing. It depends on your server's uplink speed and your own downlink speed and how much data is being transferred though.

Allan

redani

OK, thanks a lot for your help :)

I'd have one last question please :)
is it possible to perform a search operating with "OR", what I mean is if i search "Tokyo London" it will display results containing Tokyo and also results with London...

Thank you :)

allan

Client-side or server-side?

Client-side you would use a regular expression to do OR filtering.

Server-side you would modify whatever code you are using there.

Allan

redani

Client side.
how would I use the regular expression??

allan

Using the regex option in the search() method.

Allan

redani

Great, thank you very much.
Cheers

redani

Aye! again one last question please :)) or maybe never say "last" ?? :)

in my table i'll have some combo boxes with a selected value:
<select>
<option>Value1</option>
<option selected="selected">Value2</option>
<option>Value3</option>
</select>

when using the serach box to find "Value2", will it be possible to display rows with "Value2" option selected??

Thousand thanks

allan

Yes, but it is difficult and slow performance. Basically you need to use the columns.render method to return the selected value when the search data type is requested, and invalidate the row on each search change and input value change. That is what makes it perform poorly - it needs to read from the DOM rather than used the cached data.

Allan

redani

OK, fair enough, if you say it will decrease performance, then i'll figure out another way to do so :)
Thanks a lot for your assistance.
Havd a good day

redani

Hi, i'm back again for another question :)

Everything's working perfectly and as expected. i'm now trying to customise a little bit the plugin and came across some difficulties...

1- is it possible to remove the text "search" before the search box and have only a placeholder="Search"? same for "Show entries, is it possible to customize it?

2- i'm displaying the list of all clients, i have some removed clients and thinking to use a filter. what is the best way to do so? should I have a button "Removed" and when i click on it i load over ajax all removed clients? or just have a column in which i have the value "active", or "removed" ?

3- are you planing to have any updated using font awesome for the icons such as triangles for sorting, ...

thank you so much ;)

allan

1- is it possible to remove the text "search" before the search box and have only a placeholder="Search"? same for "Show entries, is it possible to customize it?

Sure - from the manual:

• language.search
• language.searchPlaceholder

2- i'm displaying the list of all clients, i have some removed clients and thinking to use a filter. what is the best way to do so?

Whichever is easiest for you! I don't think either will make a huge amount of difference in terms of performance. I suppose reloading the table would allow it to contain smaller data sets.

3- are you planing to have any updated using font awesome for the icons such as triangles for sorting, ...

You mean like this?

Allan

redani

1- perfect, got all I need, thanks :)
1.1- is there a way to set searchPlaceholder and search to all my scripts or do I have to specify them every time I use datatable()?

2- OK! so let's say when i click on a button "Removed clients", how can I refresh datatable with new records?

3- Yes, perfect, thanks again ;)

I'll have additional questions please :)
4- when using "lengthMenu": [[50, 200, 500, 1000, -1], [50, 200, 500, 1000, "All"]] to dipslay all results, doest it affect speed of loading or not?

5- as i'm using "deferRender": true, i retrieve all records in the ajax file and return a json response. I would need to send some extra data to display in the main page, but not in datatable, is that possible?

Thank you

allan

1.1- is there a way to set searchPlaceholder and search to all my scripts

Set a default.

2 [...] how can I refresh datatable with new records?

Again from the manual, assuming it is Ajax loaded us ajax.reload().

4- when using "lengthMenu": [[50, 200, 500, 1000, -1], [50, 200, 500, 1000, "All"]] to dipslay all results, doest it affect speed of loading or not?

Yes of course. It takes longer to draw all of those rows. Exactly how much longer depends on the computer being used, the complexity of the rows, etc.

5- as i'm using "deferRender": true, i retrieve all records in the ajax file and return a json response. I would need to send some extra data to display in the main page, but not in datatable, is that possible?

Sure - use initComplete or init to access the JSON.

Allan

redani

1.1- thank you, but i get an error message in pages where i dont use datatable, which means i don't call the datatable.js script!! I guess i either have to include datatable.js script in all my pages or manually add the desired setting in each initialisations of datatable, right?

5- great, i managed to add extra text in the json response and display it correctly in my page, thanks :)

5.1- let's say i'm displaying a list of orders: 10 orders with a total of $200, when i use the search box to filter orders and get only 6 orders for example, can the total be updated calculating the new total??

6- I searched but didn't find, is it possible to highlight search result in the list?

7- this can be an enhancement for next updates: It'll be amazing if we can chose columns where we want to permorm search, for example i type in the search box: column_name: search

Once again, thousand thanks :)

allan

1.1- thank you, but i get an error message in pages where i dont use datatable

Just put a check in to see if DataTables has been loaded or not?

5.1- let's say i'm displaying a list of orders: 10 orders with a total of $200, when i use the search box to filter orders and get only 6 orders for example, can the total be updated calculating the new total??

Example

6- I searched but didn't find, is it possible to highlight search result in the list?

Like this?

Allan

redani

1.1- True, didn't think about it :)
here is the code if someone's looking for the answer, and maybe correct me if I'm mistaken:
if($.fn.dataTable) {
dataTable initialization...
}

And how about question 7?? :) do you think i can be implemented in a future version??

Thanks

allan

7- this can be an enhancement for next updates: It'll be amazing if we can chose columns where we want to permorm search, for example i type in the search box: column_name: search

Yes, nice idea. Not something I'm likely to build into DataTables core, but I don't see why it couldn't be built as a plug-in.

Allan

redani

Thanks a lot for your time and assistance ;)

redani

Hi Allan, how are you?
sorry to bother you, i'm just stuck implementing the reload of ajax data :( it's about my question 2: "let's say when i click on a button "Removed clients", how can I refresh datatable with new records?"
you told me to use ajax.reload() but i didn't manage to make it work. here is my code:

$(".datatable").dataTable({
"deferRender": true,
"ajax": "/path/file.php?removed=n",
"lengthMenu": [[50, 200, 500, 1000, -1], [50, 200, 500, 1000, "All"]],
});

$("#filters .filter:not(.active)").click(function() {
    
    // and here don't know what to do, need to call "/path/file.php?removed=y|n"
    
});

Thank you

P.S. sorry didn't manage to markdown correctly the code :/