[sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

[a1rex]

I just encountered very curious case in Sqlite. 
I have very simple data base with only one table and one index:

"CREATE TABLE IF NOT EXISTS notes(id INTEGER PRIMARY KEY, note TEXT)";

My updates to the simple text database were very slow. Extremely slow!

I changed my code and achieved 1000 speed improvement.
Nevertheless, something must be wrong with sqlite3_bind_parameter_index 
functions !?

My Update function looks as follows:

int UpdateNotesRecord(sqlite3 *handle,
             int idArg,
             CString note)
{
    sqlite3_stmt *stmt;
    int rc;

#if 0 // 100 updates with 5 charcter string takes 12 000 ms

    char *sql = "UPDATE notes SET note=:cNote WHERE id=:idArg";

    rc = sqlite3_prepare_v2(handle, sql, (int) strlen(sql), &stmt, 
NULL/*&tail*/);
    if(rc) PrintError("UPDATE prepare_v2",rc,handle);

    // idArg
    rc = sqlite3_bind_int(stmt, sqlite3_bind_parameter_index(stmt,":idArg"), 
idArg);
    if(rc) PrintError("bind idArg error",rc,handle);
    
    int byteCount = note.GetLength();
    char *p = note.GetBuffer();

    rc = sqlite3_bind_text(stmt, sqlite3_bind_parameter_index(stmt,":cNote"), 
p, byteCount, SQLITE_STATIC);
    if(rc) PrintError("bind note text error",rc,handle);
#endif

#if 1 // 100 updates with 5 character string takes 90 ms
      // magnitude of 1000 less than with code above!
    
    char *sql = "UPDATE notes SET note=? WHERE id=?";
    rc = sqlite3_prepare_v2(handle, sql, (int) strlen(sql), &stmt, 
NULL/*&tail*/);
    if(rc) PrintError("UPDATE prepare_v2",rc,handle);

    // idArg
    rc = sqlite3_bind_int(stmt, 1, idArg);
    if(rc) PrintError("bind idArg error",rc,handle);

    // note
    int byteCount = note.GetLength();
    char *p = note.GetBuffer();
    
    rc = sqlite3_bind_text(stmt, 2, p, byteCount, SQLITE_STATIC);
    if(rc) PrintError("bind note text error",rc,handle);
#endif

    //                         UPDATE STEP
    rc = sqlite3_step(stmt);
    if(rc != SQLITE_DONE)
    {
        PrintError("update step error",rc,handle);
    }

    rc = sqlite3_finalize(stmt);
    if(rc) PrintError("finalize update error",rc,handle);
    return rc;
}

Thank you for reading. Any comment greatly appreciated.
Samuel 



      __________________________________________________________________
Yahoo! Canada Toolbar: Search from anywhere on the web, and bookmark your 
favourite sites. Download it now
http://ca.toolbar.yahoo.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users