1

I have a problem with displaying more than 3000 rows (this number is going to be increased in an exponential rate).

  1. I'm currently returning the data in a collection from a MYSQL database. Should I use Laravel Eloquent or Query Builder for a better performance in my controller?

  2. Should I return the data in a JSON file format, will this help speed things up?

  3. Is there a way to add, delete, edit rows in the datatables without actually returning the view every time?

If you could provide an explanation with some articles that could help, that would be great. I tried to find something to put me on the best path to handle a huge amount of data, but no luck.

Regards, James

James
  • 11
  • 1
  • 2
  • You never need to display more than 100-200 rows (even 500 is too much, probably). Always use pagination. Use indices for fast search and pagination for display. You should always pass page number with request (wether AJAX wether classic HTTP POST redirect getching GET value). This applies for displaying data on screen because approach is to that way - you never need to see more than 200 results on screen. – Tpojka Oct 09 '18 at 10:24

2 Answers2

2

For handling huge data, it is always preferred to chunk or paginate it, you may want to check this answer.

Should I use Laravel Eloquent or Query Builder for a better performance in my controller

Eloquent is easier for simple queries and reading for using the models as it uses Active record pattern while builder is faster for handling queries and complexities, you might want to check this answer

Should I return the data in a JSON file format, will this help speed things up?

json or collection, both are handled by Laravel and wont make much difference IMHO, however if you are considering saving some of the DB records in a buffer json file:

Lets consider these scenarios (while database is always huge),

  1. you have a minimal to none transactions on your database, keeping it indexed in Json files would speed up the process while keeping in mind the security measures.
  2. you have a minimal to medium transactions on your database, json wont be as much useful.
  3. you have enormous transactions, it is better to start considering mongodb or your preferred NoSQL DB.

Is there a way to add, delete, edit rows in the datatables without actually returning the view every time?

Adding or deleting or updating can be done in a single or mass way, in regards to your 3rd question, yes you can create a controller function and communicate through ajax.

the process is: create a controller function to query the database, create a route for such function, and query by ajax this function in the front end, you may want to check this tutorial.

Hamza Mohamed
  • 1,373
  • 1
  • 12
  • 28
1

When you fetch all this data to the backend, I suggest to NOT hydrate Eloquent models. Eloquent models are quite heavy. Instead, you could use lightweight stdClasses:

$users = DB::table('users')->get();

The get method returns an Illuminate\Support\Collection containing the results where each result is an instance of the PHP stdClass object.

https://laravel.com/docs/5.7/queries#retrieving-results

Besides that, you should really consider paginating the results. Fetching, transforming and rendering a massive list of items will usually result in poor user experience.

https://laravel.com/docs/5.7/pagination

Of course you can (and should) tell the database to do as much heavy lifting as possible. Databases are designed to work with big amounts of data.

Mass updates with same data: https://laravel.com/docs/5.7/queries#updates

Mass inserts: https://laravel.com/docs/5.7/queries#inserts

A more flexible batch update/insert package: https://packalyst.com/packages/package/mavinoo/laravel-batch

Also, take a look at this example: updating table rows in postgres using subquery

This is how you can update the table using the data from a subquery. It's a Postgres example and might not fit your case. Just wanted to give you a glimpse of what DBs are capable of. If you are comfortable with raw queries, your DB engine can provide you with the most performant, most flexible tools.

Andrius Rimkus
  • 643
  • 5
  • 10