Server side processing of 200 000 rows but Allowed memory size of exhausted and Invalid JSON respons

Lilith

Hello,

I tried to use Datatable for displaying a long query of 250 000 rows with 22 columns. For that, I use server side processing, but with SSP::complex and joins table. I don't have any error in the sql query (when I test it in phpmyadmin this display well all the rows), but in my json return I have the error "Allowed memory size of 536870912 bytes exhausted ". And in my datatable "Invalid JSON response" (due to the allowed memory error ?).

I thought server side processing ask automtically the database with limit query to have only a smaller number of rows than the entire count of result.

Did I miss something or done something wrong ?

allan

Hi,

The demo SSP script doesn't provide the ability to do a join, so have you modified it to add that ability?

Normally an out of memory error in server-side processing would happen if the length limit wasn't applied correctly.

Allan

Lilith

Yes, I did like in this example : https://www.gyrocode.com/articles/jquery-datatables-using-where-join-and-group-by-with-ssp-class-php/

I also add a GROUP BY in my $table declaration (with the joins table) and a WHERE in SSP:complex

Lilith

So after I've tested on the same table containing only 1500 lines, apparently server side processing does not apply a limit and tries to get all the data.

I don't really know where the problem come for ? Any suggestions ?

allan

Sounds like the server-side processing script isn't working. You'd need to debug the script you are using - probably start by printing out the SQL it is generating.

Allan

Lilith

Huum. I modified my server-side processing script. It works now but, for the table of 200.000 rows, it takes 90 s to display the result in the datatable. The ajax query is called at each page changing. And I get only 10 datas (which is normal I think). So maybe the performance time is due to my sql query ? But I don't think I can change it.