SQLite Forum

Slow insert with prepare/step
Login

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 [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 [link] [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.