SQLite Forum

Slow insert with prepare/step
Login

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.