[Hackaton 2024][cobalt][Javascript] EC72 - Perform an SQL query inside a loop

[lmarie23]

Associated rule title

Perform an SQL query inside a loop

Associated rule link

EC72

Language and platform

SQL/Javascript

Measure justification

SQL queries within a loop pose major performance problems, especially if the SQL server(s) is (are) not on the local machine. These servers are optimised to handle several selections, insertions or modifications in a single query or transaction.

Used incorrectly, these queries consume unnecessary CPU cycles, RAM and bandwidth.

Public articles

• Comparing multiple rows insert vs single row insert with three data load methods by RedGate

Test protocol

Scenarios

1. The loop runs multiple SQL queries (1 per row) for 300000 row.
2. The loop runs a single SQL query for 300000 rows.
3. Repeat 5 times in total

A more precise description is available in the source repository

Tools

• Node.js
• Docker
• Postgres
• cAdvisor

Validation

Indicate whether the measurement performed validates or invalidates this rule.

Indicate the percentage of certainty of this validation (0% for "I'm not sure at all", 100% for "I'm certain of the result")

The tests validate the rule with 100%.

The time of execution is always longer with multiple SQL queries, but there was a glitch in one run when considering the resource consumption.
The tests would benefit from more runs, and different usage monitoring tools.