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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users