Just first thought came to my mind: are you sure that 2 versions of
code mentioned do the same thing? In particular I'm asserting that
second version (under #if 1) doesn't do any actual updating and
doesn't change your database because you have wrong parameter indexes.
And one more question: why don't you store your prepared statement
somewhere and do not prepare it for each row again and again?
Pavel
On Wed, Feb 3, 2010 at 10:45 AM, a1rex <a1rex2...@yahoo.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
