# Improve efficiency updating the database (?)

**URL:** <https://forum.inductiveautomation.com/t/improve-efficiency-updating-the-database/93962>\
**Category:** Ignition\
**Tags:** database, ignition81, scripting\
**Created:** [July 31, 2024, 11:12am UTC](https://forum.inductiveautomation.com/t/improve-efficiency-updating-the-database/93962 "2024-07-31T11:12:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![danielps1818](https://avatars.discourse-cdn.com/v4/letter/d/ecae2f/32.png) [@danielps1818](https://forum.inductiveautomation.com/u/danielps1818)\
**Post date:** [July 31, 2024, 11:12am UTC](https://forum.inductiveautomation.com/t/improve-efficiency-updating-the-database/93962/1 "2024-07-31T11:12:37Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [July 31, 2024, 11:20am UTC](https://forum.inductiveautomation.com/t/improve-efficiency-updating-the-database/93962/2 "2024-07-31T11:20:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![danielps1818](https://avatars.discourse-cdn.com/v4/letter/d/ecae2f/32.png) [@danielps1818](https://forum.inductiveautomation.com/u/danielps1818)\
**Post date:** [July 31, 2024, 11:21am UTC](https://forum.inductiveautomation.com/t/improve-efficiency-updating-the-database/93962/3 "2024-07-31T11:21:10Z")

</div>

We use Oracle Database in our current project.