2

How Can I Improve loading more than 25 million rows in a single table. I have a datatable implementation, which require this type of compulsion. I already have other solutions with laravel. But Is this possible to improve my ajax request performance so my table can be loaded with data within 30-40 seconds?

My DataTable is loading data with an ajax call https://datatables.net/reference/option/ajax

divyamohan kashyap
  • 70
  • 7
  • If you're loading 25million rows in a datatable in 30 seconds it sounds like performance is pretty amazing already. However, to make that amount of data actually usable in the UI you should look at implementing paging, filtering and sorting. If you want more specific help to achieve this, please add much more detail about your current logic to the question – Rory McCrossan Mar 15 '19 at 11:02
  • Currently you getting your data with php loop or json based? – mafortis Mar 15 '19 at 11:04
  • Don't. Full stop, just don't. Browsers aren't designed for OLAP and it will just be a waste of time trying to get this much data in the browser and likely crash your end-users' machines (which will likely be lower spec than your dev machine). Instead, implement server-side sorting/paging/etc - as you're using datatables: https://datatables.net/manual/server-side – freedomn-m Mar 15 '19 at 11:09
  • If you are trying to load million record browser not support that much records in table. thats why [REFERENCE](https://stackoverflow.com/questions/2402953/javascript-data-grid-for-millions-of-rows/2569488#2569488) check this stackoverflow question. – ThataL Mar 15 '19 at 11:09
  • @RoryMcCrossan Thanks for the suggestion. I have already look into this nothing was helpful from all of those. – divyamohan kashyap Mar 15 '19 at 11:57
  • @mafortis I am loading data with datatable ajax call for relevance it is JSON which is been grabing this data to the view. The template rendered view is having an empty table. Right after loading document ajax call takes place. – divyamohan kashyap Mar 15 '19 at 11:59
  • @freedomn-m As far as browser crash is concerned I have not seen any this type of issue till the time. But I know this problem will persist in future as data is being incresing explicitly and this is the reason I have raised this question. I have tried implementing server-side option provided by datatables in this particular case, But as a result I find there issue in exporting data in pdf and excel format. – divyamohan kashyap Mar 15 '19 at 12:09
  • @ThataL My data is being loading in a few chunks. that made it possible loading this much amount of data in a single table. – divyamohan kashyap Mar 15 '19 at 12:11
  • @divyamohankashyap well if you already getting your data in json you have gone 50% of the way, as the answer below I suggest you to use server-side-processing as well, all you have to do is to use your jason variable in JavaScript and loop the data there, it will take care of the rest of it. `PS` I really don't suggest you to use package for datatables as it's not necessary, if you want you can add it to your app,js very easy and not making your application heavy by adding many packages, here i explained how to https://stackoverflow.com/questions/55016159/adding-datatable-to-laravel-mix-files – mafortis Mar 15 '19 at 15:04

1 Answers1

2

You shouldn't load all these records at once. This will be immensely slow and there isn't much you can do about that.

A solution for this is to implement Server-side processing. This way you can modify your database query on the server side to only load the records that should be displayed.
There are already several packages for Laravel that will build these queries for you. For example: yajra/laravel-datatables.

Jerodev
  • 32,252
  • 11
  • 87
  • 108
  • My data is being loading in a few chunks. Server-side processing will not be helpful here because I have given my users export button also which will not work when I will make server side processing enable. And about Laravel Yajra Package. I have this last option, if I dont get any helpful solution here. – divyamohan kashyap Mar 15 '19 at 12:16
  • 2
    @divyamohan kashyap: I believe, you may adjust your server-side logic to feed DataTable from the proper chunk, depending on the rows requested. It doesn't make sense indeed to halt your application for ages just to load the entries, which user is not able to see, comprehend or process all at once. From this standpoint `serverSide: true` is the optimal solution. As for your export, you may override embedded DataTables export tools with your own, say, upon clicking 'export' button you'll throw an ajax call and respond with the document, prepared server-side. –  Mar 15 '19 at 13:46