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,
</jum>
