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

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

LilithLilith Posts: 6Questions: 2Answers: 0

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 ?

Answers

  • allanallan Posts: 48,278Questions: 1Answers: 6,967 Site admin

    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

  • LilithLilith Posts: 6Questions: 2Answers: 0

    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

  • LilithLilith Posts: 6Questions: 2Answers: 0

    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 ?

  • allanallan Posts: 48,278Questions: 1Answers: 6,967 Site admin

    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

  • LilithLilith Posts: 6Questions: 2Answers: 0

    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.

Sign In or Register to comment.