Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

Enjoy this video: http://channel9.msdn.com/ShowPost.aspx?PostID=59936 Nice! The key sentence is "a lot of the assumptions that where made 15 years ago, don't hold true anymore..." Michael - To unsubscribe, send

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

Michael Sizaki wrote: It's strange that windows is not a bit more clever on caching. I have 2Gb and most of the time I have 1Gb free. Windows could use this for temp files. It is clever on caching - it was designed to operate on a machine with 4MB of RAM. Oneof the design changes in

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

Thanks Roger! I switched "Memory Usage" to "System Cache" http://www.techspot.com/tweaks/memory-winxp/ and my performance problems are gone. I have to see how this setting influences my overall performance. It's strange that windows is not a bit more clever on caching. I have 2Gb and most of

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

Thanks, Roger. I had no idea such a setting existed. Why Windows forces you to make a choice on your usage pattern up-front seems odd to me. You'd think they'd use heuristics and/or statistics to tune this dynamically on the fly. --- Roger Binns <[EMAIL PROTECTED]> wrote: > Windows XP limits the

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Sizaki wrote: | I'm really puzzled why my system hits the disk so heavily Windows XP limits the maximum size of the cache (default 10MB!). There are zillions of pseudo-freeware programs out there to change it. You can also change it

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

I went to implement this suggestion and quickly discovered that SQLite already uses the FILE_ATTRIBUTE_TEMPORARY flag on TEMP tables. Or at least I think it does. Can somebody with a symbolic debugger that runs on windows please confirm that the marked line of code in below (found in os_win.c)

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

[EMAIL PROTECTED] wrote: Perhaps someone with more windows experience can correct me if my assertion above is incorrect. Are there some special flags that SQLite could pass to CreateFileW() to trick windows into doing a better job of caching temp files? It seems you've done it right:

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > Are there some > > special flags that SQLite could pass to CreateFileW() to > > trick windows into doing a better job of caching temp > > files? > > FILE_ATTRIBUTE_TEMPORARY > A file is being used for temporary storage.

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

Michael Sizaki <[EMAIL PROTECTED]> wrote: > > What surprises me, is that the temp file is not kept in > cache. I have 2GB of memory and much bigger files can be > kept in cache. Why is sqlite "hitting the disk"? What is > going on here? The maximum file cache needed would be 70 MB > for the

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

Here's the screenshot showing the resource usage of the slow query: >time ./sqlite3.3.8.exe db.sqlite "SELECT * FROM files where id < 20 ORDER BY size, name;"|wc 19 204598 24676875 real4m49.947s user0m18.386s sys 0m13.318s Peak memory 35 MB

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

==> SUMMARY <== ==> There is indeed no difference between 3.3.7 and 3.3.8 ==> However, sqlite hits the disk a lot in a temp file??!! ==> PRAGMA temp_store = MEMORY; helps ==> Why is sqlite hitting the disk with a 70MB database? Further tests shows that there is no difference between 3.3.7 and

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

Michael Sizaki <[EMAIL PROTECTED]> wrote: > > What has changed in 3.3.8 to make it so slow? > There were no changes to the query optimizer between 3.3.7 and 3.3.8. None. Nada. Zilch. -- D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

Need more info than that. Schema, sample data, actual slow query, etc. If I were to guess - try doing the same query twice. You've probably got a cold file cache. --- Michael Sizaki <[EMAIL PROTECTED]> wrote: > The following query on a table with 400,000 rows > > SELECT * FROM table where