As a test, have you tried wrapping your updates in a transaction? That
would isolate if the slow down is the actual writing of the data to
disk.
Where is the file sitting: A local drive, or something across a network
connection?
David
On Wed, 2010-02-03 at 08:51 -0800, a1rex wrote:
> >----- Original Message ----
> >From: Pavel Ivanov <paiva...@gmail.com>
> >To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> >Sent: Wed, February 3, 2010 11:37:17 AM
>
> >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.
>
> You are right!
> Retraction. Mea Culpa. Back to square one…
> The modified code did not write anything to the drive! But there was no error
> message from the SQLITE.
> After proper indexing the writing time is about the same!
>
> >And one more question: why don't you store your prepared statement
> >somewhere and do not prepare it for each row again and again?
>
> I will. But the problem highlighted by my mistake with indexes is not with
> the prepared statement,
> but extremely slow write to the drive.
>
> int UpdateNotesRecord(sqlite3 *handle,
> int idArg,
> CString note)
> {
> sqlite3_stmt *stmt;
> int rc;
>
> #if 1 // 100 updates with 5 character string take 12 seconds
>
> 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);
> // was: rc = sqlite3_bind_int(stmt, 2, idArg);
> if(rc) PrintError("bind idArg error",rc,handle);
>
> // note
> int byteCount = note.GetLength();
> char *p = note.GetBuffer();
>
> rc = sqlite3_bind_text(stmt, 1, p, byteCount, SQLITE_STATIC);
> // was rc = sqlite3_bind_text(stmt, 2, p, byteCount, SQLITE_STATIC);
> if(rc) PrintError("bind note text error",rc,handle);
> #endif
>
> 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;
> }
>
> Is there any way to configure database for efficient updates of small amount
> of text?
>
> Thank you for reading. Any comment greatly appreciated.
> Samuel
>
>
> __________________________________________________________________
> Make your browsing faster, safer, and easier with the new Internet Explorer®
> 8. Optimized for Yahoo! Get it Now for Free! at
> http://downloads.yahoo.com/ca/internetexplorer/
> _______________________________________________
> 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
