Imagine you have an array of object
but in the array, some object can have multiple data
for example
const transactions = [
{
"player_id": 1,
"transaction_id": "xxx",
"amount": "100",
},
{
"player_id": 2,
"transaction_id": "xxx",
"amount": "100",
},
{
"player_id": 3,
"transaction_id": "xxx",
"amount": "100",
},
{
"player_id": 1,
"transaction_id": "xxx",
"amount": "100",
}
]
and then you need to get the player information from the database
for (let i = 0; i<= transactions.length; i++) {
const player = PlayerModel.findByPk(transactions[i].player_id)
// equals to SELECT * FROM users WHERE player_id = 1
const saveTransaction = TransactionModel.create({
id: transactions[i].transaction_id,
player_id: transactions[i].player_id,
player_before_balance: player.balance,
player_after_balance: player.balance - transactions[i].amount
});
}
you'll be creating database tcp connection in each loop, and that would be bad for the performance (more bad if your database is on another host)
because on every call you make, the flow goes like
open the db connection -> execute the query -> close the connection
so instead of using that way, you can distinct the transactions array by the player_id
const distinctPlayers = [...new Set(transactions.map(transaction => transaction.player_id))]
// result [1,2,3]
the you will just call the database query only 1 time, using where in clause and store it in a variable
const players = PlayerModel.findAll({
where: {
id: distinctPlayers
}
})
// equals to SELECT * FROM users WHERE player_id IN (1,2,3)
for (let i = 0; i<= transactions.length; i++) {
const player = players.find(player => player.player_id === players.player_id);
const saveTransaction = TransactionModel.create({
id: transactions[i].transaction_id,
player_id: transactions[i].player_id,
player_before_balance: player.balance,
player_after_balance: player.balance - transactions[i].amount
});
}
but the downside is, if the array is too large
the memory will take too much
please tell me if you have better approach
Top comments (0)