If you want SQLite to support all ACID properties you cannot change
anything to speed up updates. You can only change disks to something
with higher rotation speeds or some non-rotational ones (although I'm
not sure that they will be faster).
Another thing to try is change your application structure and execute
all updates in one transaction as already said by others...
Pavel
On Wed, Feb 3, 2010 at 11:51 AM, a1rex <a1rex2...@yahoo.com> 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
