0
220
ViewsMultiple INSERT data after SELECT and UPDATE for mysql2 in nodejs
I'm looking for advice on how to approach the following problem (I'm sure there's multiple ways to tackle this, but ultimately, I'm not familiar enough with databases to know what is the best approach).
The problem at hand is that I have a 'large' (few hundred) set of elements of data come in every 30 seconds. So far, this has been handled sequentially and wasn't a problem when there were only a few dozen. Now we've upscaled this really chugs due to the back and forth per eventual insert. I think the way forward is to insert all new rows at once, but I can't think of a neat way considering the whole flow.
The tables are setup as follows (with irrelevant parts omitted):
Devices:
| ID | Address |
|:--:|:-------:|
|1 |addr1 |
|2|addr2|
|3|addr4|
DeviceData: |ID|DeviceID|Value|Latest| |:-:|:-----:|:---:|:----:| |1 |1 |22 | 0| |2|1|23|0| |3|1|22|1| |4|2|99|0| |5|2|99|1|
Devices stores identifying information about a device (fairly static), whereas DeviceData collects information (values) the device outputs (constant updates/inserts). Note that Address works to key each record, and DeviceID is a foreign key to Devices. Latest indicates the piece of data most recent to that device (i.e. has the greatest timestamp).
The data I receive comes in the following format (for Device ID 1):
{
"addr1": { "value": 22 },
"addr2": { "value": 99 }
}
The process currently is:
Select the ID for the device by its address
SELECT ID FROM Devices
WHERE Address = 'addr1'
ID is stored in $id
Update the record in DeviceData as no longer being the 'latest' data for that device
UPDATE DeviceData SET Latest = 0
WHERE DeviceID = $id AND Latest = 1
Insert the new data along with the 'latest' flag into DeviceData
INSERT DeviceData
SET DeviceID = $id, Value = 22, Latest = 1
There used to be manipulations that were made after the initial SELECT (it would select more data than just the ID), but that's no longer the case (to explain why it wasn't just a join from the beginning).
(if the tables look broken, I honestly don't know what happened they were fine in the editor. I've added it as an image in case oh lord my laptop is on 3% i don't think i'm gonna make it)
1 answers
Answer question0
update all the rows in one query to latest 0 and insert all your rows in one query
UPDATE DeviceData ,Devices
INNER JOIN Devices ON DeviceData.DeviceID= Devices.ID
SET DeviceData.Latest = 0
where Devices.Address in (1,2,3,4,5,6,7)
and then do the insert
INSERT INTO DeviceData (column_list)
VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);