Am 26.07.2006 um 04:16 schrieb Joe Wilson:
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?)
We don't use PRAGMA temp_store in our code and the performance
changes when we change the default temp_store via the TEMP_STORE
macro when compiling sqlite.
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).
We explicitely define the TEMP_STORE macro in the build settings
(Xcode on Mac, Visual Studio .NET / VC 7 on Windows), so this
shouldn't be an issue.
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? ;-)
Doh! - that's it ;-) no, just kidding - apparently sqlite picks a
temp directory on the boot drive by default
Are you running Windows anti-virus software?
That might actually be the culprit - I was under the impression that
we tested without, but when I just reconfirmed with my Windows
fellows, they admitted they do have antivirus software installed |-:
Guess I'll have to make sure we run the test again without antivirus
software installed.
OTOH, our customers might also have antivirus software installed, so
this still would not be a solution :(
Does anybody have advice on how to make sqlite work smoothly with
antivirus software [on Windows]? (Probably depends on the antivirus
software?)
Thanks
</jum>
--- 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>
