On Tue, Feb 08, 2011 at 06:02:28PM +0100, Branko Čibej wrote: > On 08.02.2011 16:34, Bert Huijben wrote: > > An even better solution would be that SQLite tries to do things completely > > in memory and only *creates* a tempfile when needed. (It seems it now > > creates the file anyway; but doesn't use it until needed. Introducing a > > heavy performance penalty on NTFS, but not on extXfs) > > You mentioned testing on journalled filesystems. Maybe you don't > consider ext3 and ext4 to be journalled, but my tests were done on > journalled HFS+ on Mac OS. > > Yes, creating a temporary file is moderately expensive. But in the > approach I showed, you really only create one temporary table and/or > database per WC operation, not 50 zillion. > > That said, I agree that a lot more testing and measuring needs to be > done. And after all, a memory-backed temporary table is in the worst > case backed by swap space.
All good points, yes. Getting the queries right is much more important than worrying about the temp_store pragma.