On Wed, 9 Aug 2006 18:51:30 +0600, you wrote: >hi All, > >I have to use SQLite for one of my project as ":memory:" db.
[snip] >which PRAGMA statements can improve the query speed. >the main queries are, INSERT and SELECT with joins. > >I have just tried the following PRAGMA > > sqlite3_exec(db, "PRAGMA temp_store=2", NULL, NULL, NULL); > sqlite3_exec(db, "PRAGMA synchronous=0", NULL, NULL, NULL); > sqlite3_exec(db, "PRAGMA count_changes=OFF", NULL, NULL, NULL); > sqlite3_exec(db, "pragma default_cache_size =65536", NULL, NULL,NULL); That seems Ok to me. > sqlite3_exec(db, "pragma cache_size = 8192", NULL, NULL, NULL); That is weird, why would you set a large default_cache_size but a much smaller cache_size for the current connection? For a memory database that wouldn't make any difference though. >can someone guide me which PRAGMA statements are useful for >speed improvement and what values I need to set for those. >like "pragma default_cache_size =?????" It might help to PRAGMA the page_size as well. You have only one chance to do that: at databasefile creation time, just before you CREATE your first table, and it should reflect the optimal size for your platform. On windows, it should be the same as the actual cluster size of your filesystem. For a memory database that wouldn't make much difference. >I have a 4G Physical Memory. That's a lot to play with. >//---------------------------------------------------------- >one last thing, I have tested the code written by "Dennis Cote" >Wed, 03 May 2006 " performance v2 and V3 " > >I found that Version 2.8 was much Faster than 3.6 using ":memory:", 30 >Seconds and 60 seconds in case of 2.8 and 3.6 respectively. >can I use 2.8 in my project when i have a huge amount of data to handle. > >Thanks >Regards, > >Manzoor Ilahi Tamimy The biggest gain will be in your database structure and handling: - optimize your schema - don't store anything you don't really need - define indexes for every column you will join on - be very critical on your joins - experiment with table order in joins - sometimes a union of two or more inner joins is better than one outer join - experiment - read the page about index usage - use EXPLAIN Many of these techniques are discussed on the sqlite site, it really pays off to try to read all of it. -- ( Kees Nuyt ) c[_]
