"Griggs, Donald" <[EMAIL PROTECTED]> wrote: > Regarding: ... Something is misconfigured as it takes ~20 times as long to > insert into an in-memory db, as it does to select from Sybase and store to a > file. ... > > > I'm not sure you're using transactions. If not, BEGIN a transaction before > starting the loop of INSERTs and END it when done. This may increase your > speed tremendously. >
For an in-memory database, using transactions should not effect the speed at all. Here's why: SQLite easily handles 50000 inserts per second, but only about 60 transactions per second. Normally, SQLite puts each insert into a separate transaction, which then limits you to about 60 inserts per second. By using BEGIN...COMMIT you can group multiple inserts into the same transaction thus increasing your insert rate. Transactions are slow due to limitations of computer disk hardware. When SQLite writes to the disk, it has to stop and wait at a couple of places to for all of the data to actually be written to the disk surface. This is necessary in case a power failure or OS crash occurs - so that the data can be recovered. It is this stopping and waiting that takes so long. If we didn't need to wait for the disk platter to spin underneath the head, SQLite could easily handle tens of thousands of transactions per second. But because each transaction requires at least two revolutions of the disk platter, SQLite is limited to about 60 transactions per second. (Do the math: 7200 RPM disk drive means 120 revolutions per second. 2 complete revolutions per transaction -> 60 transactions per second.) But there are no such restrictions on an in-memory database. With an in-memory database we never have to wait for the platter to spin under the write head. So transactions are very, very fast. Hence grouping multiple inserts into a single transaction does not really buy you anything when using an in-memory database. Only databases that are stored on spinning media are helped by that trick. -- D. Richard Hipp <[EMAIL PROTECTED]>
