According to the documentation in http://sqlite.org/pragma.html for PRAGMA temp_store, it seems that only the TEMP_STORE macro values of 0 and 3 unconditionally guarantee temporary storage to be file and memory respectively. Otherwise the runtime "PRAGMA temp_store=FILE|MEMORY" plays a role. (Am I the only person to find the use of these two variables to determine temp store to be confusing?)
In order to isolate some variables, what results do you get with the default ./configure && make on both platforms? (default is temp store = file, non-threadsafe). What compiler are you using on Windows? VC6, 7, Cygwin, MinGW, Intel, other? And your temp_store_directory isn't set to something crazy like a floppy drive, is it? ;-) --- Jens Miltner <[EMAIL PROTECTED]> wrote: > Hi, > > we just found that when using file-based temporary storage (compile > time macro definition TEMP_STORE=1) vs. memory-based temporary > storage (TEMP_STORE=2), on Mac OS X, the performance almost doesn't > degrade at all, whereas on Windows, we're getting a huge performance > penalty when using file-based temporary storage. > > We are compiling sqlite 3.3.6 ourselves using pretty much the stock > compile time options, except for TEMP_STORE and THREAD_SAFE=1. > We also found that apparently, database performance with TEMP_STORE=1 > is especially slow when running on Windows XP (SP2). > > OS Versions where Mac OS X 10.4.4 and Windows XP (SP2). > Both machines were equipped with > 1 GB of RAM, but since we're using > file temp storage, memory usage is not really a limit here. Hard disk > performance should be about equal on both machines. > Queries that suffer most from the performance hit are, of course, > those that obviously seem to access temporary tables/views, e.g. > CREATE TEMP VIEW xyz or DROP VIEW xyz. > (needless to say that the very same queries were performed on both > platforms...) > The performance difference between a 2.8GHz Pentium Windows XP > machine and a 2x1GHz Mac OS X PowerPC machine was easily a factor of > 10... When using memory temp storage on Windows, the performance is > about par with the Mac OS X performance when using file temp storage. > > > Anybody got an explanation / solutions / workarounds for this > performance problem? > > Thanks, > </jum> > > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
