Federico Marotta Federico Marotta - 4 months ago 17
JSON Question

Bulk-update a DB table using values from a JSON object

I have a PHP program which gets from an API the weather forecast data for the following 240 hours, for 100 different cities (for a total of 24.000 records; I save them in a single table). The program gets, for every city and for every hour, temperature, humidity, probability of precipitation, sky cover and wind speed. This data is in JSON format, and I have to store all of it into a database, preferably mySQL. It is important that this operation has to be done in a single time for all the cities.

Since I would like to update the values every 10 minutes or so, performance is very important. If someone can tell me which is the most efficient way to update my table with the values from the JSON it would be of great help.

So far I have tried the following strategies:

1) decode the JSON and use a loop with a prepared statement to update each value at a time {too slow};

2) use a stored procedure {I do not know how to pass the procedure a whole JSON object, and I know there is a limited number of individual parameters I can pass};

3) use LOAD DATA INFILE {the generation of the csv file is too slow};

4) use UPDATE with CASE, generating the sql dynamically {the string gets so long that the execution is too slow}.

I will be happy to provide additional information if needed.


You have a single table with about a dozen columns, correct? And you need to insert 100 rows every 10 minutes, correct?

Inserting 100 rows like that every second would be only slightly challenging. Please show us the SQL code; something must be miserably wrong with it. I can't imagine how any of your options would take more than a few seconds. Is "a few seconds" too slow?

Or does the table have only 100 rows? And you are issuing 100 updates every 10 minutes? Still, no sweat.

Rebuild technique:

If practical, I would build a new table with the new data, then swap tables:

Load the data (LOAD DATA INFILE is good if you have a .csv)
RENAME TABLE real TO old, new TO real;

There is no downtime -- the real is always available, regardless of how long the load takes.

(Doing a massive update is much more "effort" inside the database; reloading should be faster.)