This content originally appeared on DEV Community and was authored by Chidiebere Ogujeiofor
Here's an improved version of the introduction for Scenario 1:
Updating multiple rows in a database is a common task when managing data, whether you're setting the same value across several rows or handling different updates for each one. While the former is straightforward, the latter can quickly become complex and lead to performance issues due to excessive database calls.
In this article, we’ll explore how to efficiently update multiple rows with different values in a single query to minimize database overhead and improve performance.
Problem Example
Imagine you want event owners to update the prices of multiple tickets at once. The event owner provides an array of objects containing the ticket IDs and their new prices. The input might look something like this:
{
"event_id": "my-event-conference-id",
"ticketUpdateArr": [
{ "id": "id1", "newPrice": 400 },
{ "id": "id2", "newPrice": 700 },
{ "id": "id3", "newPrice": 790 },
{ "id": "id4", "newPrice": 1500 }
]
}
A straightforward implementation to handle this update might look like this:
async function bulkUpateTickets(ticketUpdateArr) {
for (const updateObj of ticketUpdateArr) {
await TicketsModel.update(
{
amount: updateObj.newPrice,
},
{ where: { id: updateObj.id } }
);
}
}
In this code, we loop through each item in the array and make a separate database call to update the price for each ticket.
While this works, it’s not efficient. Each database call is expensive, and as the number of tickets grows, this function becomes significantly slower.
How Can We Improve This?
We can improve performance by moving the logic to the database and utilizing SQL CASE statements. This allows us to update all tickets in a single query.
Here’s how the query would look:
UPDATE
tickets
SET
amount = CASE
WHEN id = 'id1' THEN 400
WHEN id = 'id2' THEN 700
WHEN id = 'id3' THEN 790
WHEN id = 'id4' THEN 1500
ELSE amount
END
WHERE
id IN ('id1', 'id2', 'id3', 'id4');
This query updates all the ticket prices in one go. The parts that come from user input are the WHEN ... THEN conditions and the list of ids in the WHERE clause.
Now, let's update our JavaScript code to dynamically build the query and make just one database call.
Improved JavaScript Code
async function bulkUpdateTickets(ticketUpdateArr) {
if (ticketUpdateArr.length === 0) return;
let caseStatement = "";
const ticketIds = [];
const replacements = {};
for (let index = 0; index < ticketUpdateArr.length; index++) {
const updateObj = ticketUpdateArr[index];
const ticketIdReplacement = `ticket_id_${index}`;
const ticketValueReplacement = `ticket_value_${index}`;
caseStatement += /*sql*/ `
WHEN id = :${ticketIdReplacement} THEN :${ticketValueReplacement}`;
replacements[ticketIdReplacement] = updateObj.id;
replacements[ticketValueReplacement] = updateObj.newPrice;
ticketIds.push(updateObj.id);
}
const rawQuery = /*sql*/ `
UPDATE
tickets
SET
amount = CASE ${caseStatement}
ELSE amount
END
WHERE id IN (:ticket_ids);
`;
await sequelize.query(rawQuery, {
type: QueryTypes.UPDATE,
replacements: {
...replacements,
ticket_ids: ticketIds,
},
});
}
This code builds a dynamic SQL CASE statement based on the input array, constructing the WHEN ... THEN clauses and the list of ticket ids for the WHERE clause. Here's an example of the generated SQL:
UPDATE
tickets
SET
amount = CASE
WHEN id = :ticket_id_0 THEN :ticket_value_0
WHEN id = :ticket_id_1 THEN :ticket_value_1
WHEN id = :ticket_id_2 THEN :ticket_value_2
WHEN id = :ticket_id_3 THEN :ticket_value_3
ELSE amount
END
WHERE
id IN (:ticket_ids);
The :ticket_id_n and :ticket_value_n placeholders will be replaced by the corresponding values in the replacements object provided to Sequelize. This dynamic construction not only prevents SQL injection but also improves performance significantly.
Performance Gains
Although the code still loops through the input array to build the query, the big improvement is that it only makes one database call for all updates. This drastically reduces the number of database interactions, making the application much faster, especially when handling a large number of tickets.
Conclusion
In my experience building backend applications and APIs, it’s common to encounter scenarios where we need to update multiple records in the database. While looping through each item and making individual calls works, it's not scalable. By using CASE statements and bulk queries, we can efficiently update multiple records in a single call, resulting in significant performance gains.
This content originally appeared on DEV Community and was authored by Chidiebere Ogujeiofor
Chidiebere Ogujeiofor | Sciencx (2024-09-24T18:36:11+00:00) Scenario 1: Making updates to multiple fields. Retrieved from https://www.scien.cx/2024/09/24/scenario-1-making-updates-to-multiple-fields/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.