0

https://github.com/SRombauts/SQLiteCpp

SQLite::Database    db("example.db3");
while(...)
{
    db.exec("INSERT INTO xxx VALUES(...)");
}

It's sample code for SQLite to insert data. If there's no transaction, each db.exec is slow, almost takes 1 second.

So, you need a transaction :

db.exe("BEGIN");
while(...)
{
    db.exec("INSERT INTO xxx VALUES(...)");
}
db.exe("END");

But if I make all queries into one string:

db.exec("INSERT INTO xxx VALUES(...);\
INSERT INTO xxx VALUES(...);\
INSERT INTO xxx VALUES(...);\
...
");

Do I still need a transaction?

user4157124
  • 2,809
  • 13
  • 27
  • 42
Zhang
  • 3,030
  • 2
  • 14
  • 31
  • 2
    "Do I still need a transaction?" - that depends on what you need/want. Do you need or want all `INSERT`s to be atomic with regards to other readers? If yes, then you need a transaction, if no, then you probably don't. – Jesper Juhl Sep 06 '22 at 10:17
  • 3
    In SQLite, using a transaction for bulk INSERT is **much, much master**. On Windows, it is nearly 100x faster, the difference is less but still very significant on macOS or Linux. Therefore, I suggest to *always* use transaction for bulk INSERT. – prapin Sep 06 '22 at 12:52
  • 1
    In this case you are still doing multiple insert statements so I would expect you to need a transaction for performance reasons. If you inserted multiple rows in 1 insert I believe you don't: [https://stackoverflow.com/a/16592427/487892](https://stackoverflow.com/a/16592427/487892) – drescherjm Sep 06 '22 at 12:56

1 Answers1

3

If you want to insert 'everything' or 'nothing', then, YES. You still need a transaction. SQLite::Database::exec() internally calls sqlite3_exec() and semicolon separated SQL statement will not be executed atomically without a transaction.

John Park
  • 1,644
  • 1
  • 12
  • 17