Performance: CSV from server to DataTables or CSV to SQL, then to DataTables?

Innerwolf

Hey, right now I'm using cronjob to download a CSV file once per hour to my server. With every reload of the page the csv file gets parsed to a php array and then the user is forced to download the whole array (by passing it to javascript) and then the table gets created with a Javascript function. After that, DataTables does its job but the whole loading process takes around 9 seconds, which is really too long..

I'd be interested, if it was possible to choose the CSV file as datasource instead of the built up table?

Or would it be better to save the csv to a sql database with the cron-job php and then use server side processing? But to be honest I have no idea how to do this, already tried storing it with PHP as I described here: http://stackoverflow.com/questions/31141753/improve-page-performance-save-php-array-on-server
But the people on stackoverflow didn't seem to be interested in helping me..

I'd appreciate your help really much!

Page: zcatch-ranking.host56.com

tangerine

The stackoverflow people gave you some help, but you don't appear to have tried any of their suggestions.

I'm no cronjob expert, but if it's just a case of writing some PHP I don't understand why you don't get all the parsing done in the cronjob and have the user download the
resulting json.

Alternatively, having your cronjob write data to a database and using DataTables' server-side processing would make sense, as pointed out by Alex Andrei.

You have to implement the method which best suits your needs, and it may involve some trial and error.

Innerwolf

I have tried their suggestions. But doing the parsing in the cron job didn't work for me because I don't know how to get the array into the JS file. But that's not even the problem anymore, because I figured out, that parsing it in php isn't the time intensive operation. It's the JS creation of the table and DataTable afterwards.

having your cronjob write data to a database and using DataTables' server-side processing

That's exectly what I want to do, but I can't figure out how, I've tried this: http://stackoverflow.com/questions/31173563/php-import-csv-from-server-to-sql-database
I have created some alerts so you can see for yourself, what takes the most time on the website http://zcatch-ranking.host56.com/

tangerine

Your last stackoverflow question says you tried storing to a database "but it didn't work for me.".

So what does that mean? What result? What error message(s)? What happened?

Innerwolf

I don't know what happened or where to check the error messages. Not even the download worked anymore.

allan

createtable() {

Wow - you really don't want to do it like that. That is why it is seriously slow. You are creating a DOM element for every cell, then having DataTables read it back into Javascript when you already have the data in Javascript...

Just feed the data into DataTables using data and enable the deferRender option.

JS array sourced data example.

Allan

Innerwolf

Thanks for the help, allan!

Just feed the data into DataTables using data

The thing is, that I don't use all of the data straight out of the jscon encoded array, e.g. I convert seconds into hours and so on. Any options inside of "data": allowing to edit that? Or would that have to be done before data?

enable the deferRender option.

Do I have to include a source on this (ajax:)? How would this apply on my case?

allan

I would suggest doing the conversion in PHP and outputting just want you want the table to see. You can use columns.render to modify the data if you want, but I suspect you'll get slightly better performance doing it in PHP.

Do I have to include a source on this (ajax:)?

No. You can if you want to Ajax load the data, but it isn't required.

Allan