[sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
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
Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
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 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
Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
>- Original Message >From: Pavel Ivanov >To: General Discussion of SQLite Database >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
Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
On Wed, Feb 03, 2010 at 08:51:46AM -0800, a1rex scratched on the wall: > >- Original Message > >From: Pavel Ivanov > >To: General Discussion of SQLite Database > >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. Having an "UPDATE...WHERE" that modifies no rows is not any more of an error then one that modifies multiple rows. You provided a valid SQL statement, SQLite executed it correctly. > >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. If you are doing bulk updates, and are in a position to re-run the data in case of an error, wrap batches of 100 or more in a transaction. Just be sure to handle any error case that trips an automatic rollback. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
a1rex wrote: > 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. Why should there be? You have a legal statement - essentially update notes set note=1 where id='note text'; The condition is not true for any row - but that's not in itself an error. > I will. But the problem highlighted by my mistake with indexes is not > with the prepared statement, > but extremely slow write to the drive. Make all your updates within a single transaction. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
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 wrote: >>- Original Message >>From: Pavel Ivanov >>To: General Discussion of SQLite Database >>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
Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
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 > >To: General Discussion of SQLite Database > >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
Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
Thank you kindly for all your suggestions! >If you want SQLite to support all ACID properties you cannot change >anything to speed up updates. Making sure that I do not loose a character was my primary objective. > If you are doing bulk updates, and are in a position to re-run the > data in case of an error, wrap batches of 100 or more in a transaction. > Just be sure to handle any error case that trips an automatic rollback. >Make all your updates within a single transaction. I will try to do something to that extent using timer and character counter. I hoped that I could update the text stored in the database character by character as fast as they come from the keyboard driver. Unfortunately updates noticeably slow down the display of typed characters. >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. It appears that single transaction is slow and I have to make less transaction with more data. >Where is the file sitting: A local drive, or something across a network >connection? File is sitting on a local drive. It is IDE Seagate 120MB drive – ST3120026A Capacity: 120.9 GB Speed: 7200 rpm Average Read Time:8.5 ms Cylinders:1023 Heads:256 Sectors: 63 · I/O data transfer rate - up to 100 Mbps · Sustained data transfer rate - Up to 58 Mbps · Average seek time - 8.5ms · Average latency - 4.16ms From this data nothing justifies the 120ms update of the record! Thank you again. Regards, Samuel __ The new Internet Explorer® 8 - Faster, safer, easier. 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
Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
>I will try to do something to that extent using timer and character >counter. >I hoped that I could update the text stored in the database character >by character as fast as they come from the keyboard driver. >Unfortunately updates noticeably slow down the display of typed >characters. You can probably use a memory database to store on the fly data with low overhead (the update time being very short) and simply update your main disk-based base from time to time. If your application can't afford loosing key hit, even in the event of power loss, you can still have a regular OS flat file to record them, in parallel or not with your memory base. But you may still loose some at power loss... (What the heck?) As the saying goes: at least with 360 systems, memory was memory. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
> Capacity: 120.9 GB > Speed: 7200 rpm > Average Read Time:8.5 ms > ... > From this data nothing justifies the 120ms update of the record! Look at 7200 rpm and here http://www.sqlite.org/faq.html#q19. Pavel On Wed, Feb 3, 2010 at 1:18 PM, a1rex wrote: > > Thank you kindly for all your suggestions! > >>If you want SQLite to support all ACID properties you cannot change >>anything to speed up updates. > > Making sure that I do not loose a character was my primary objective. > >> If you are doing bulk updates, and are in a position to re-run the >> data in case of an error, wrap batches of 100 or more in a transaction. >> Just be sure to handle any error case that trips an automatic rollback. > >>Make all your updates within a single transaction. > > I will try to do something to that extent using timer and character counter. > I hoped that I could update the text stored in the database character by > character as fast as they > come from the keyboard driver. Unfortunately updates noticeably slow down the > display of typed characters. > >>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. > > It appears that single transaction is slow and I have to > make less transaction with more data. > >>Where is the file sitting: A local drive, or something across a network >>connection? > > File is sitting on a local drive. It is IDE Seagate 120MB drive – ST3120026A > > Capacity: 120.9 GB > Speed: 7200 rpm > Average Read Time:8.5 ms > Cylinders:1023 > Heads:256 > Sectors: 63 > > · I/O data transfer rate - up to 100 Mbps > · Sustained data transfer rate - Up to 58 Mbps > · Average seek time - 8.5ms > · Average latency - 4.16ms > > From this data nothing justifies the 120ms update of the record! > > Thank you again. > > Regards, > Samuel > > > __ > The new Internet Explorer® 8 - Faster, safer, easier. 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
Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.
On Wed, Feb 03, 2010 at 10:18:01AM -0800, a1rex scratched on the wall: > ?? I/O data transfer rate - up to 100 Mbps > ?? Sustained data transfer rate - Up to 58 Mbps > ?? Average seek time - 8.5ms > ?? Average latency - 4.16ms > > From this data nothing justifies the 120ms update of the record! An update requires a read. That may or may not require disk access. Seek + latency puts a read around 12ms. A transaction write requires (IIRC) three atomic disk writes. Given the seek and latency time, that is, on average, about 40ms and can be has high as twice that. Plus overhead from the disk controller and any other I/O going on at the moment, process context switching, etc. It adds up. It also gets much worse if the DB page size is less than the native filesystem block (i.e. 1K pages on an 4K NTFS system), as the OS sometimes needs to read a block, modify part of it, and write it back. 120ms is a bit high, but stand-alone insert (not update) speeds are usually limited to a dozen or two per second, and are often directly tied to disk RPM. ACID is expensive. Since SQLite can't use caches, you're directly exposed to the fact that physical I/O and disks are very very slow. If you don't need it, turn it all off or use an in-memory database. Just don't come crying when a power glitch or application crash makes your whole database blowup. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users