mike cariotoglou wrote: > ----- Original Message ----- From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: > <[EMAIL PROTECTED]> Sent: Thursday, October 07, 2004 7:10 PM Subject: > Re: [sqlite] Is this an in-memory database too > >> Yes. As many different processes as you want can read the database at the >> same time. Only one process at a time can write, but since writes usually >> take a millisecond or less, that is not normally a problem. > > well, this is not actually as true as it should be. true, writes are fast per > se. however, if you operate at a synchronous level of anything more than 0, > the whole operation of inserting a record,say, is quite slow, at least in > windows. >
Yes, I was a trifle optimistic with my estimates....
On an operating system with a sensible I/O scheduler (I cannot say whether or not windows qualifies) it should normally take about two complete rotations of the disk platter to complete a write. Obviously a large change would take more than that, but the common case will usually work in two rotations.
With a 7200RPM disk drive, that translates into about 17ms.
This figure (17ms per transaction) is consistent with measurements done on my Linux desktop. See http://www.sqlite.org/speed.html. Test 1 does 1000 inserts without using a transaction and does so at a rate of about 77 inserts/second, which is about 13ms per transaction. That's actually a little faster than my back-of- the-envelope calculation above. Perhaps my disk platter spins at more than 7200RPM. Or maybe some updates are happening with fewer than two complete disk rotations. Probably my cheap IDE disk drive is lying to the disk controller by telling it that sectors have been committed to the disk surface before they really have been - a common bug in IDE disk drives. But I digress: The point is that it isn't too hard to get 50 transactions per second which means that a process will normally not need to wait more than about 20ms to get read access to the database - assuming the writers cooperate by not holding locks longer than they really need them.
> > PS what does the "D." stand for ? >
The "D." stands for my first name. I go by my middle name, "Richard". :-)
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565