Doug Currie wrote:
Add the line:
DeleteFile("test.txt");
in front of the CreateFile() call...
Doug,
This does make a difference. I thought that the CREATE_ALWAYS flag to
CreateFile would delete any existing file, but upon further
investigation I found out that what it does is open the file and set its
length to 0. Apparently it it faster to delete the file. In either case
it seems to be a side effect of these calls, since their execution time
is not included in the measurements.
There seems to be another effect due to the run time of the program. For
loops of 1000 flushes I consistently get higher rates than for loops of
10000 flushes. I know some OS's lower the priority of long running
process that do lots of I/O. This is supposed to be an indication of a
long running batch type process so they do this to improve the response
to more interactive processes. In my case the CPU is spending most of
its time in the idle process waiting for the disk so this still doesn't
make much sense.
The following is the from my first six runs with 1000 flushes per run.
The first column is the time, the second is the flushes/sec rate.
26 38.5
18 55.6
26 38.5
19 52.6
26 38.5
18 55.6
These are the average and standard deviation of the execution time
above, as well as the ratio of the standard deviation to the average as
percentage which is an indication of the variability.
22.2 46.5
4.2 8.9
19.02% 19.12%
When I increased the number of loops by a factor of 10 to average over a
longer time I got the following results for seven runs.
274 36.5
314 31.8
238 42.0
265 37.7
312 32.1
229 43.7
268 37.3
271.4 37.3
32.8 4.5
12.07% 12.03%
The average flush rate dropped to 80% of it previous value, and the
variability dropped by about 40%.
After I saw your posting, I modified my code to explicitly delete the
file before each run and repeated the tests. For 10 runs with 1000 loops
I got:
12 83.3
12 83.3
11 90.9
15 66.7
12 83.3
14 71.4
14 71.4
14 71.4
12 83.3
12 83.3
12.8 78.9
1.3 7.9
10.29% 10.00%
The average flush rate increased by 70% and the variability dropped by
about 50%.
I then repeated the test with loops of 10000 flushes per run and got the
following results.
207 48.3
211 47.4
204 49.0
197 50.8
206 48.5
205.0 48.8
5.1 1.2
2.51% 2.55%
Again the average flush rate over the longer run is about 60% of what it
was for the short runs. The variability is only 20% of what it was
without the explicit delete.
It seems *really* strange that deleting the file would reduce the
average run time of the 10000 loop case by more than 60 seconds. Neither
overwriting the file or deleting the file should take anywhere near this
long. These are small files of only 10K characters after all.
Something else is going on here. If we can figure out what, we may be
able to speed up SQLite under Windows. It currently seems to be running
about 1/8 the speed of FreeBSD when doing inserts with individual
transactions.
In any case we seem to be settling to a long term sustained rate of
about 40 flushes per second for WinXP. Since SQLite is getting only 10
inserts per second, it seems to me that SQLite must be doing about 4
flushes per insert. Does that seem right to you Richard?
Dennis Cote
