Re: [sqlite] Query Execution speed.
Christian Smith <[EMAIL PROTECTED]> wrote: >> > Version 2 used a Red/Black balanced tree in :memory:, whereas version 3 > appears to have done away with this optimisation. I'm curious as to why? > It is simpler to support a single algorithm (b-trees) rather than two (b-tree + red/black trees). Subtle differences between the b-tree and red/black-tree backends in version 2 was a persistent source of bugs. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query Execution speed.
Manzoor Ilahi Tamimy uttered: 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. Version 2 used a Red/Black balanced tree in :memory:, whereas version 3 appears to have done away with this optimisation. I'm curious as to why? Thanks Regards, Manzoor Ilahi Tamimy Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query Execution speed.
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[_]
Re: [sqlite] Query Execution speed.
Am 09.08.2006 um 05:51 schrieb Manzoor Ilahi Tamimy: hi All, I have to use SQLite for one of my project as ":memory:" db. // - Can I get a better speed if I change or omit some macros. I saw " http://www.sqlite.org/compile.html ". the macros defined here can only resulting in a smaller compiled library size or they can also improve some speed? we can also Override these macros through PRAGMA statements. Is there any difference between when we handle these macros directly or override through PRAGMA. 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); sqlite3_exec(db, "pragma cache_size = 8192", NULL, NULL, NULL); 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 =?" I have a 4G Physical Memory. // - You don't tell us anything about the query you're going to run. Most optimizations can actually be done to the query itself or to your database schema (e.g. by building the appropriate indices). Anything you'll get through compilation will probably only get you minor improvements really. Of course, you can check which features you don't need and leave them out at compile time, hoping that this will give you some performance boost, but I don't think you'll see a 2x improvement by doing this kind of change. I'd really go in first and try to optimize/rewrite the queries for sqlite first and see if there's some performance gain in there for you. I've seen query speed improve by a factor of 10 or more when using the right indexes or rewriting the query such that it can in fact use the one index I thought it would use in the first place. If you already have indexes you think the query uses, verify it does indeed use the index by running your query prefixed with "EXPLAIN QUERY PLAN". I think a while ago somebody posted some rules of thumb of what will make queries run fast in sqlite and what to avoid. I can't remember the title, but I'm sure if you search for threads containing "peformance" or "speed" you'll find it in the archives. It's hard to tell what might make your query run faster without knowing your query. 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. Of course you can use 2.8 - just link against the 2.8 libraries. You can even use both 2.8 and 3.x together, since they have distinct APIs whose names don't conflict, but remember that the 2.8 and 3.x databases can't be exchanged - the database format has changed. So you'll have to work on 2.8 databases using the 2.8 APIs and use 3.x APIs for a 3.x database. HTH,
[sqlite] Query Execution speed.
hi All, I have to use SQLite for one of my project as ":memory:" db. // - Can I get a better speed if I change or omit some macros. I saw " http://www.sqlite.org/compile.html ". the macros defined here can only resulting in a smaller compiled library size or they can also improve some speed? we can also Override these macros through PRAGMA statements. Is there any difference between when we handle these macros directly or override through PRAGMA. 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); sqlite3_exec(db, "pragma cache_size = 8192", NULL, NULL, NULL); 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 =?" I have a 4G Physical Memory. // - 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
