What is the best way to INSERT multiple rows in a SQLite table

Question

My problem mainly is performance related, I have this code running on the main ElectronJS proccess :

ipcMain.handle('add_product', async (event, args)=>{
       return new Promise((resolve, reject)=>{
            try {
                if(Array.isArray(args)){
                    args.forEach(prod =>{
                        const {name,barcode,stock,price,buy_price,image,alert} = prod
                        const stmt = db.prepare("INSERT INTO products VALUES (?,?,?,?,?,?,?)")
                        stmt.run(name, barcode, stock, alert, price, buy_price, image)
                        stmt.finalize()
                    })
                    resolve({text : `${args.length} product have been added to database!`})
                }else{
                    // This code execute's only when adding a single product
                    // It is not relevant to the question
                    const {name,barcode,stock,price,buy_price,image,alert} = args
                    const stmt = db.prepare("INSERT INTO products VALUES (?,?,?,?,?,?,?)")
                    stmt.run(name, barcode, stock, alert, price, buy_price, image)
                    stmt.finalize()
                    resolve({text : `Product '${name}' have been saved!`})
                }                 
            }catch (error){
                reject(error)
            }
        })   
    })

It receives an array of objects, each object contains a single product details. Now the above code works and successfully inserts rows inside the database. However when testing it with a substantial data sample (more than 5000 product) the whole application freezes for a couple of seconds while it is saving rows to the database before it becomes responsive again.

The dev stack is :

ElectronJS
ReactJS (using it for the VIEW)
SQLite

What is the optimal and performance driven way to make the application works fatser?

Is that function called 5000 times, once per product? Or you call it once, with a batch of 5000? And are you happy for the DB writes to happen, asynchronously, in the background, while the user does other stuff? Or should the UI be locked until the data has all been loaded into the database? — Darren Cook, Dec 06 '22 at 12:29
That's exactly the issue, each product is its own seperate INSERT query which means there's going to be 5000 seperate writes in the database. No wonder it made the whole app freeze. I reformulated the query to run once and it seems that the performance have been boosted significantly. I will mark this question as answered. — Spetsz_, Dec 06 '22 at 19:54

Spetsz_ · Answer 1 · 2022-12-06T20:07:14.177

Okay so the way I formulated the query was that it would run 5000 times -once for each product- which significantly slowed the whole application.

I changed the code to :

ipcMain.handle('add_product', async (event, args)=>{
       return new Promise((resolve, reject)=>{
            try {
                if(Array.isArray(args)){
                    let sql = `INSERT INTO products VALUES`
                    args.forEach((prod, i) =>{
                        const {name,barcode,price,buy_price,stock,alert,image} = prod 
                        if(i === args.length - 1){
                            sql += `('${name}','${barcode}','${price}','${buy_price}','${stock}','${alert}','${image}')`
                        }else{
                            sql += `('${name}','${barcode}','${price}','${buy_price}','${stock}','${alert}','${image}'),`
                        }
                    })
                    db.exec(sql, (error)=>{
                        if(error){
                            reject(error)
                        }else{
                            resolve({text : `${args.length} product have been added to database!`})
                        }
                    })
                }else{
                    const {name,barcode,price,buy_price,stock,alert,image} = args
                    const stmt = db.prepare("INSERT INTO products VALUES (?,?,?,?,?,?,?)")
                    stmt.run(name, barcode, stock, alert, price, buy_price, image)
                    stmt.finalize()
                    resolve({text : `Product '${name}' have been saved!`})
                }                 
            }catch (error){
                reject(error)
            }
        })   
    })

Now the query runs only once (in an asynch fashion to not block the UI) but with all the products and it's much faster.

What is the best way to INSERT multiple rows in a SQLite table

1 Answers1