Improve efficiency updating the database (?)

Ignition
, ,
1

Hello!

We have 2 tables in our database.

  1. PROCESS (ID as key, PHASE, [...])
  2. PROCESS_PHASE (ID as key, PHASE as key, [...])

The first one saves 1 entry per process.
The second one saves 1 entry per process phase.

The way the code works it's like this (code in bold are interactions with the database):

  1. Process gets created.
  2. Process is saved in PROCESS.
  3. Process phase is saved in PHASE_PROCESS.
  4. Process gets updated.
  5. Code calls the corresponding function that updates the phase.
  6. Gets the process from PROCESS using it's ID (to check if exists and if the phase is different).
  7. PROCESS gets updated with the new data (like the phase).
  8. The last phase in PROCESS_PHASE gets marked as ended.
  9. New entry in PROCESS_PHASE it's created with the new phase.
  10. Process gets updated (if it is not the last phase go to step 5)
  11. Gets the process from PROCESS using it's ID (to check if exists).
  12. The process gets marked as ended in PROCESS.
  13. The process gets marked as ended in PROCESS_PHASE.
  14. The flow of this process is now ended (no more phases).

As you may see, updating the phase of a process "costs" 4 calls to the database (6, 7, 8 & 9). I don't really have a lot of knowledge on databases but maybe there is another way of doing this more efficiently.

Note: the update of the process could be asynchronous.

I'm looking for ideas or tips on how to improve it.

Thanks in advance!

2

What flavour of SQL are you using? MySQL/MariaDB / PostgreSQL, etc.

3

We use Oracle Database in our current project.