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>
