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