DEV Community

dkelxldk
dkelxldk

Posted on

Optimizing database query - In a loop

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",
    }
]
Enter fullscreen mode Exit fullscreen mode

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
    });
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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
    });
}
Enter fullscreen mode Exit fullscreen mode

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)