Slow performance with big tables

[flaveris]

I have a table with 440 000 rows and 19 columns, and I am getting "memory limit exhausted" errors when I try to enter list view. I tried raising memory limit step by step, and it only managed to load the page with 2GB memory limit - and it took about 20 seconds. I am also using ajax table option to speed things up.

Is this normal?

I was so excited to find this CRUD library for Laravel, but I'm afraid I won't be able to use it anymore when my tables grow to millions of rows... :/

[OSDDQD]

#36
Are u trying this?

[tabacitu]

Woow. That's definitely not normal - that's the point of the ajax table, to make the list view handle loads like this.

1. Are you sure you're using the correct syntax for ajaxtable?
2. how many rows per page does your list view have?
3. if you do a simple eloquent query for that number of rows (in any other page), does it load just as slow?

Cheers!

[flaveris]

Thank you for your quick replies :)

1. I suppose yes - I'm using this: $this->crud->enableAjaxTable();
2. 25 rows per page.
3. Actually this part is quite strange, because when I tried to look deeper with Debug Bar (https://github.com/barryvdh/laravel-debugbar), it showed that the list action is selecting all entries from the table (executing a general SELECT * FROM table query), which takes 5 seconds, and the overall load time is about 55 seconds, actual memory usage - from 1 to 1.5 GB. If I do a ::all() eloquent call on a clean controller action, it takes about 20-25 seconds to load.

Since I'm new to Laravel (coming from Symfony) - I don't really know if there is something like a Symfony Profiler in Laravel, where I could see a whole timeline of all functions called and how much time it took? The debug bar which I mentioned above doesn't show this. If you could suggest a tool like that, I could give you more information :)

[JeanHules]

I too am having this issue. I have $this->crud->enableAjaxTable(); in my controller, but it seems to be loading the entire dataset upfront instead of the first 25.

Am I doing something wrong? This breaks the page as I am working with a large dataset.

Any help would be much appreciated.

[tabacitu]

@flaveris , @JeanHules - you're totally right, I missed a SELECT when coding the AJAX functionality. The CrudController::index() method still got all the rows in the DB, despite the fact that they weren't used. I only tested it with ~20 000 rows before this and performance was not affected at that number, sorry for that.

Thanks for reporting this, it's now fixed in CRUD 3.0.14. Feel free to do a composer update, that'll fix the issue for you. Tested it with 1M+ records and it works now.

Cheers!

[flaveris]

Thank you so much! Works like a charm :)

[JeanHules]

Thank you for reviewing this issue.

One last issue, I am getting this error:

DataTables warning: table id=crudTable - Ajax error. For more information about this error, please see http://datatables.net/tn/7 is prompted

and jquery-2.2.0.min.js:4 POST http://www.myurl.com/admin/charity/search 500 (Internal Server Error) in console.

Any ideas on how to fix that?

[tabacitu]

Hm... Never got that, but judging by the article, I assume your EntityCrudController::search() function returns something other than a 2xx response. What's the response code in your browser's Developer Tools > Network for an AJAX call to 'search'? Also, what's the actual response? 'Cause it might be an error for some reason.

It SHOULD look something like this:

[JeanHules]

This is weird... I am getting a 500 error when I look at Network and Console, but when I go directly to the URL as you would a normal website, I get a 403.

Any idea on what could be causing this?

[tabacitu]

It's normal to get a 403 error when accessing it manually - the route only supports POST and you're trying a GET.

But it's odd that you're getting a 500 error in Network and Console. I guess there's no other way to figure out what's wrong, other than moving a dd() after every line of the CrudController::search() method and see what line is causing the issue... Does your log register any error?

[JeanHules]

I figured it out. I am not sure if it is because I am using MSSQL or not, but I had to use

echo json_encode($dataTable->make());
in place of:
$dataTable->make()

and I was also getting a Sql error 207 which was caused by an added "id" column which my table does not have. All seems well now. Thanks for the help. Great product.