On 3 Mar 2014, at 9:11pm, romtek <rom...@gmail.com> wrote: > Simon, does a real disk have to be a rotating hard disk? Is there problem > with SSDs as far as SQLite is concerned?
SSDs aren't a problem, and SQLite works fine with them, but they change the timings associated with SQLite a great deal. Simplified explanation follows. With rotating hard disks each read or write operation has to wait for the disk to rotate to the right place. And if you have your files defragmented, all of a SQLite database is clumped up together on disk right next to the journal file for that database. An END TRANSACTION command can involve six (more ? anyone ?) accesses of database or journal file, and if all the parts of disk you're writing to are near one-another that can involve six rotations of the hard disk. So ... Hard disk rotates at 5400rpm = 90rotations/s . 6 reads or writes to disk, assuming no skipped rotations, takes 66ms = 1/15th of a second . A loop doing 100 INSERTs can take over six seconds ! This means that in real life SQLite can be faster if you do /not/ defragment your drive. Because you don't have to wait for a full rotation every time. SSDs, of course, don't have to wait for anything physical. The improvement in speed gained by using transactions does not work the same way: there are still fewer operations, so it should still be faster, but not by as much. My original statement about so much of the time being spent on the END TRANSACTION no longer holds. That's why I asked. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
