There is equipment that every 1-2 seconds (if everything is normal) sends a package of 4 fields. This package is saved to the database log-table (each package is a separate record). In addition, it is necessary for the client to ensure that the data of the last packet is displayed.

How to do it better? Request

SELECT * FROM packets WHERE device_id = :id HAVING id = MAX(id)

or last_packets table and regularly updating this table? (log table is needed anyway)

DBMS Orakl. The number of such devices is up to 200.

And another point - if at the moment of receiving the package the client is connected to the server, then the package is sent directly to this client. Those. the client does not need to constantly contact the database. Access to the last packet is needed when the equipment has ceased to transmit data, and at the time of the transfer the client was absent

  • A typical task is to get top N records (in your case, N = 1). See, for example, here: ru.stackoverflow.com/questions/751644/… - Dmitriy
  • @Dmitry Do you think that writing the last id will be correct? I repeat a lot of records. The subquery will not select them all until it sees the WHERE ROWNUM = 1 condition in the outer query? - Anton Shchyrov
  • If your id guaranteed to increase, then why not? And then, it is optional to sort by id , by date it is also possible. If the index is, it should also be used, then the speed will almost not depend on the size of the table. - Dmitriy

1 answer 1

You, it turns out, is written to a table of 200 entries per second. With such a stream, IMHO, it is pointless to keep a separate constantly updated table. Orakl cope, of course, but why? But it’s also inconvenient to keep the whole story, because the table is growing, and most requests only request the last record. I would do that: a separate historical table, which once per hour, for example, to remove everything older than three hours from the main table, and delete the copied piece in the main table. The remaining piece will be small ( 200 * 3600 * 3 = 2 160 000 lines), queries will quickly work out. Well and a composite index on fields (device_id, log_time) . Then a request that selects the last entry for each device, as described here , or you can take all devices as one option and request the last entry for each one:

 select max(id) keep (dense_rank last order by log_date) last_dev_id, device_id, max(log_date) over (aprtition by device_id) from packets group by device_id
  • Thank. The idea is clear. 200 records per second is the peak. On average, it will be about 100. Of course, the sample in the archive will be a bit more complicated, but it will be infrequently performed - Anton Shchyrov