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.


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

Reply via email to