On Wed, Sep 17, 2008 at 06:00:45PM -0700, Roger Binns scratched on the wall:
> The second is that SQLite when opening a file under Windows explicitly
> tells Windows that the file will be used for random access even though
> that is not the case. Windows uses this hint to override its builtin
> heuristics which can cause bug #1.
> Bug #2 is that SQLite is lying to the operating system and could result
> in performance degradation if the operating system actually pays
> attention to the hint.
SQLite is not "lying." After poking around a bit to refresh my
understanding of SQLite's file structure, I think it is safe to say
that SQLite will almost never do a sequential file read, even if
you're doing a sequential table scan.
sequential table scan != sequential file access
There are some specific situations when you might get bursts of sequential
reads, but only for very specific page layouts with very specific
types of queries. In short, not the common case. Furthermore, even
those patterns can get broken up and shuffled around depending on the
state of SQLite's page cache-- especially if it is bumped up a few
dozen megs. So simply running different types of queries can change
the access patterns (this is true of the OS's file system cache as
well, of course).
It might be worth instrumenting a few systems and having a look, but
in general, if you had to label SQLite's access pattern, I think
"random" would be the most appropriate label.
I also contend that if the Windows file cache becomes some kind of
bumbling idiot if you actually try to define an access pattern, then
something is wrong. There is a very good reason why the POSIX
functions for doing this kind of thing are called "*advise()". You
might seed the heuristic statistics in a specific direction, but they
should never be totally over-ridden. That quickly leads to stupid
behaviors, like grabbing all the RAM on the system and not letting go.
Of course, we could argue philosophy for a long time. In the here
and now to work around MS's inconsistencies, it looks like the best
bet is turn it on with CE and off on Vista, because it appears to
have two totally different meanings.
-j,
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
