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 email to [EMAIL PROTECTED] -
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 Vista was recognising that they don't need to run in 4MB anymore! Enjoy this video: http://channel9.msdn.com/ShowPost.aspx?PostID=59936 Roger - To unsubscribe, send email to [EMAIL PROTECTED] -
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 the time I have 1Gb free. Windows could use this for temp files. Michael -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 using the control panel and/or registry: ~ http://support.microsoft.com/kb/308417 (system cache) ~ http://mywebpages.comcast.net/SupportCD/XPMyths.html (large system cache) ~ http://www.jsifaq.com/SF/Tips/Tip.aspx?id=9200 ~ http://www.techspot.com/tweaks/memory-winxp/ Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFla84mOOfHg372QQRAssiAJ99Hzrw6/9Nn8EscwqkV7Dsn/OYKgCcCOjC BZlsagFsYZ2pNRc/21g5MsU= =qdN5 -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
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 maximum size of the cache (default 10MB!). There > are zillions of pseudo-freeware programs out there to change it. You > can also change it using the control panel and/or registry: > > ~ http://support.microsoft.com/kb/308417 (system cache) > > ~ http://mywebpages.comcast.net/SupportCD/XPMyths.html (large system cache) > > ~ http://www.jsifaq.com/SF/Tips/Tip.aspx?id=9200 > > ~ http://www.techspot.com/tweaks/memory-winxp/ > > Roger __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
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 using the control panel and/or registry: ~ http://support.microsoft.com/kb/308417 (system cache) ~ http://mywebpages.comcast.net/SupportCD/XPMyths.html (large system cache) ~ http://www.jsifaq.com/SF/Tips/Tip.aspx?id=9200 ~ http://www.techspot.com/tweaks/memory-winxp/ Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFla84mOOfHg372QQRAssiAJ99Hzrw6/9Nn8EscwqkV7Dsn/OYKgCcCOjC BZlsagFsYZ2pNRc/21g5MsU= =qdN5 -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
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) gets executed when using TEMP tables: It gets called! Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
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:
fileflags = FILE_FLAG_RANDOM_ACCESS;
#if !OS_WINCE
if( delFlag ){
fileflags |= FILE_ATTRIBUTE_TEMPORARY | FILE_FLAG_DELETE_ON_CLOSE;
}
#endif
I'm really puzzled why my system hits the disk so heavily
Michael
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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. File systems avoid writing
> data back to mass storage if sufficient cache memory is available,
> because an application deletes a temporary file after a handle is
> closed. In that case, the system can entirely avoid writing the data.
> Otherwise, the data is written after the handle is closed.
>
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) gets
executed when using TEMP tables:
int sqlite3WinOpenExclusive(const char *zFilename, OsFile **pId, int delFlag){
winFile f;
HANDLE h;
DWORD fileflags;
void *zConverted = convertUtf8Filename(zFilename);
if( zConverted==0 ){
return SQLITE_NOMEM;
}
assert( *pId == 0 );
fileflags = FILE_FLAG_RANDOM_ACCESS;
#if !OS_WINCE
if( delFlag ){
/** The following line should run when opening a TEMP table **/
fileflags |= FILE_ATTRIBUTE_TEMPORARY | FILE_FLAG_DELETE_ON_CLOSE;
}
#endif
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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 database + 75 MB for the temp table. 150MB is > nothing on a 2GB system. > When writing to TEMP files, SQLite uses ordinary disk I/O operations. And it never calls FlushFileBuffers(). So it is not a question of SQLite hitting the disk but rather your operating system. Why isn't windows able to cache a 150MB file when you have 2GB of RAM? I don't know, but I don't think it has anything to do with SQLite. 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? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
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 - To unsubscribe, send email to [EMAIL PROTECTED] -
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 3.3.8. The problem was, that I was using sqlite.exe interactively. The in the 3.3.8 shell I have been running some tests that created and deleted some temp tables before I did performance tests. It turns out that the query hits the disk when the table exceeds a certain size. There's a certain size of my tables when performance goes down dramatically. It takes 14 sec for 100,000 rows and 300 for 200,000. The CPU goes down to almost 0 and the disk gets very active. My database: pragma cache_size = 2; pragma page_size = 4096; Database file (after vacuum) 70MB with about 450,000 records > time ./sqlite3.3.8.exe db.sqlite "SELECT * FROM files where id < 10 ORDER BY size, name;"|wc 9 103445 11352384 real0m14.281s user0m7.260s sys 0m3.775s Peak memory 35 MB >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 I captured the performance using sysinternals procexp: http://www.microsoft.com/technet/sysinternals/SystemInformation/ProcessExplorer.mspx See the attached screen shot. It's interesting that half of the memory is allocated in the last seconds... When I prepend the query with PRAGMA temp_store = MEMORY; The queries are fast, but the process needs a lot of memory (about 5 times the size of the .dump size of the result table) > time ./sqlite3.3.8.exe db.sqlite "PRAGMA temp_store = MEMORY;SELECT * FROM where id < 10 ORDER BY size, name;"|wc 9 103445 11352384 real0m8.262s user0m6.659s sys 0m0.210s Peak memory 58 MB > time ./sqlite3.3.8.exe db.sqlite "PRAGMA temp_store = MEMORY;SELECT * FROM where id < 20 ORDER BY size, name;"|wc 19 204598 24676875 real0m13.329s user0m12.187s sys 0m0.310s Peak memory 75 MB 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 database + 75 MB for the temp table. 150MB is nothing on a 2GB system. I thought maybe PRAGMA synchronous = OFF; would help. But it does not. Michael [EMAIL PROTECTED] wrote: > 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. - To unsubscribe, send email to [EMAIL PROTECTED] -
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]> - To unsubscribe, send email to [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 ORDER BY name limit 10; > > takes less than 3 sec with version 3.3.7 (or 3.3.0) > and 35 sec with version 3.3.8. > > What has changed in 3.3.8 to make it so slow? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7
Hi, The following query on a table with 400,000 rows SELECT * FROM table where ORDER BY name limit 10; takes less than 3 sec with version 3.3.7 (or 3.3.0) and 35 sec with version 3.3.8. What has changed in 3.3.8 to make it so slow? My application relies on fast sorting Michael - To unsubscribe, send email to [EMAIL PROTECTED] -
