Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to optimize jquery datatable to load large data efficiently (10k-50k rows)?

I am using jquery datatable with minimal configuration to showcase data on my website. I have 10K to 50K records to show in datatable.

Currently the datatable is taking around 60 seconds to initialize 3000 records.

There are two options that can be utilized to initialize data-table:-

1) Add records as a html table on page and then initialize datatable on that table.
2) Have a json of records and then initialize datatable with that data.

Do the following steps helps us to improve the performance of datatable plugin:-

1) Reducing number of columns
2) Grouping related columns
3) Using sort only on required fields or remove sort feature all together

Data is as follows - It is a set of mcq questions with options to preview/edit/delete

enter image description here

Search on question is something very important for my application.
Please provide an optimal solution to use datatable for the provided dataset.

like image 693
abhinsit Avatar asked Sep 26 '14 05:09

abhinsit


1 Answers

You forgot a third option: server side processing

You can learn almost everything you need to know from the official documentation: http://www.datatables.net/examples/server_side/simple.html

Basically what you do is you only load data that is absolutely required to display the table. As the page needs more data such as going to a different page or searching, the server returns the client with more data.

There are many ways to get your data into DataTables, and if you are working with seriously large databases, you might want to consider using the server-side options that DataTables provides. With server-side processing enabled, all paging, searching, ordering etc actions that DataTables performs are handed off to a server where an SQL engine (or similar) can perform these actions on the large data set (after all, that's what the database engine is designed for!). As such, each draw of the table will result in a new Ajax request being made to get the required data.

like image 135
AmuletxHeart Avatar answered Oct 21 '22 13:10

AmuletxHeart