lets say that im inserting data into a table under a for loop like this:

...
for(let i=0;i<data.length; i  ){
    db.run(`INSERT INTO db (a, t, p, q, bM) VALUES (?, ?, ?, ?, ?)`, [data[i].a, data[i].t, data[i].p, data[i].q, data[i].m], (err) => {
        if (err) {
            reject(err);
        }
    });
}
...

Is it better to use exec() instead off run() to reduce the number of round trips to the database?

does putting exec() under a for-loop, put the sql messages in a queue where when the for-loop ends, the exec() function will automatically execute all the queued message in a single instance ? Is that the benefit of using exec() over run()?

or

because exec() is still under a for-loop, it still makes the same roundtrips to the database as run() and so there is no diffference betweeen the two methods?

because im working with an array containing 1000s of objects, i want to reduce the number of round trips to the database.

which is the better method, exec() or run() if it is inside a for loop? is there any difference? if not, can you suggest another method to iterate thru a large array? I'm new to sqlite and databases in general.

CodePudding user response：

I will answer the overarching question which is the most efficient way to reduce the number of round trips. You can do this by sending fewer commands. SQLite supports a multi-insert syntax:

INSERT INTO 'tablename' ('column1', 'column2', 'column3') VALUES
  ('field-column1-row1', 'field-column1-row-2', 'field-column1-row-3'),
  ('field-column2-row1', 'field-column2-row-2', 'field-column2-row-3'),
...
;

Batching these inserts in as few individual calls as possible will be where you save the greatest number of round-trips.

The syntax is here: https://www.sqlite.org/lang_insert.html

db.exec imposes a marginally lesser load on the process; the major difference between db.exec and db.run is that db.run returns extra values like lastID, which you do not seem to need here.