Slow insert with prepare/step
(1) By lazyhiker on 2021-06-10 13:48:57
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. <code> 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; } <code>
(2) By mzm2021 on 2021-06-10 14:07:31 in reply to 1 [link]
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]
> 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 [link]
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]
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]
> 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]
Yes it did and now Test2 is faster than Test1 as it should be. Thank you.