SQLite Forum

Slow insert with prepare/step
Login
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>