On Thu, 2005-09-15 at 20:16 -0800, dan greene wrote: > When I ran this program on the win2000 machine with NTFS, this loop took on > the order of 25 seconds as reported in the second printf! > When I ran the same program on my win98 machine, it took 1.1 seconds. > putting a begin; and end;commit; SQL around the entire loop, dropped the > execution on both machines into the tens of milliseconds. > > Any thoughts as to what is happening on the WIN2000 NTFS machine to slow > down the single inserts so drastically?
On each COMMIT (which happens automatically after every SQL statement unless you have an explicit BEGIN..COMMIT surrounding multiple statements) SQLite waits until all results are successfully stored on the disk surface before continuing. This is called being "Durable" and is the "D" in "ACID". On windows, the API function to pause until all information has been written to disk is FlushFileBuffers(). SQLite calls this function during each commit to force information out of the OS's internal cache and onto the disk surface where it will be safe from lose due to power failures or operating system crashes. In order for an update to be atomic (The "A" in "ACID"), two calls to FlushfileBuffers are required. If your disk drive is spinning at 7200 RPM, and each FlushFileBuffers call requires one revolution of the disk, that means you can have at most 120 FlushFileBuffer operations or 60 COMMITs per second. Those figures are the speed-of-light - most of the time COMMITs will be slower. But they appears to be faster than this on Win98. This indicates to me that FlushFileBuffers must not be working correctly on your Win98 machine. I have heard that this was the case on many versions of windows. So to answer your question, COMMIT is much faster on Win98 because of a bug in the operating system that can cause data loss or data corruption after a power failure or operating system crash. -- D. Richard Hipp <[EMAIL PROTECTED]>
