Slow insert with prepare/step
(1) By lazyhiker on 2021-06-10 13:48:57 [link] [source]
This is my first time dabbling into SQLite so please forgive me if I am missing something obvious.
There are two functions below doing the same thing. On my machine Test1 takes microseconds to complete, Test2 ~ 17-20 seconds. Why is Test2 so painfully slow? I expected it to be faster than Test1.
int Test1(const char* dbname)
{
remove(dbname);
sqlite3* db;
int rc = sqlite3_open(dbname, &db);
if (rc) return 1;
std::string sql = "BEGIN; CREATE TABLE TESTTABLE(ID INT);";
int N = 1000;
for (int i = 0; i < N; i++)
sql += "INSERT INTO TESTTABLE VALUES (" + std::to_string(i) + ");";
sql += "COMMIT;";
rc = sqlite3_exec(db, sql.c_str(), 0, 0, 0);
sqlite3_close(db);
if (rc) return 2;
return 0;
}
int Test2(const char* dbname)
{
remove(dbname);
sqlite3* db;
int rc = sqlite3_open(dbname, &db);
if (rc) return 1;
const char* sql0 = "CREATE TABLE TESTTABLE(ID INT);";
rc = sqlite3_exec(db, sql0, 0, 0, 0);
if (rc) return 2;
const char* sql = "INSERT INTO TESTTABLE VALUES (?1)";
sqlite3_stmt* stmt = 0;
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc) return 3;
int N = 1000;
for (int i = 0; i < N; i++)
{
sqlite3_bind_int(stmt, 1, i);
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) return 4;
sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
(2) By mzm2021 on 2021-06-10 14:07:31 in reply to 1 [link] [source]
Test1() has a single transaction whereas Test2() has 1000 transactions.
You can improve the speed of the Test2() by starting a transaction before starting the loop and committing it after its end.
(3) By David Raymond (dvdraymond) on 2021-06-10 14:15:35 in reply to 1 [link] [source]
There are two functions below doing the same thing. On my machine Test1 takes microseconds to complete, Test2 ~ 17-20 seconds. Why is Test2 so painfully slow? I expected it to be faster than Test1.
1 transaction in the first vs 1,000 in the second?
(4.1) By lazyhiker on 2021-06-10 16:58:58 edited from 4.0 in reply to 3 [source]
Ah OK... thanks. I actually looked for dedicated begin/end transaction functions, couldn't find any and assumed it's a single transaction from prepare to finalize.
So I just need to do separate execs for BEGIN and COMMIT before and after, is it the right way to do it?
(5) By mzm2021 on 2021-06-10 15:26:23 in reply to 4.0 [link] [source]
Enclosing the loop in Test2() between
sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, NULL);
and
sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL);
should be sufficient.
If the code is going to be used in production, you'll need robust error handling for sqlite3_exec() returns.
(6) By David Raymond (dvdraymond) on 2021-06-10 15:28:01 in reply to 4.0 [link] [source]
So I just need to do separate execs for BEGIN and END before and after, is it the right way to do it?
That's my understanding anyway. Give it a whirl and let us know if that does indeed solve your issue.
(7) By lazyhiker on 2021-06-10 16:43:08 in reply to 6 [link] [source]
Yes it did and now Test2 is faster than Test1 as it should be. Thank you.