On Mon, Feb 01, 2010 at 07:49:50AM -0800, alenD scratched on the wall:
>
> Hi all,
> I wrote a small C code to execute a number of select queries.
If you're doing a number of similar queries, look at the
prepare/bind/step interface. It is generally better than the exec()
interface.
> SELECT COUNT(DISTINCT r0.Arg0||'-'||'F') AS A1
This concatenation is pointless. You're not returning the value, so
it is never seen in the result. Because it is completely constant,
it won't change the result of the distinct count. It does turn the
count parameter into an expression, however, so the optimizer can't
touch the count operation.
If at all possible, I'd also try to find a way to re-work the query so
that the DISTINCT is not required.
> FROM pte_atm r1,pte_atm r2,pte_active r0
> WHERE r0.is_covered_aux = 0 AND r1.Arg0=r0.Arg0 AND r2.Arg0=r0.Arg0 AND
> r2.Arg2=r0.Arg1 AND r1.Arg0=r2.Arg0 AND r0.Arg1='F' AND r1.Arg3='3'
>
> and table definitions with cardinality is as follows:
>
> CREATE INDEX I13 on pte_atm(Arg0 ASC);
It is doubtful all these indexes are useful. In general, SQLite can
only use one index per table per query. If you need to key off
several values in a table, you need to create a multi-column index.
Be aware, however, that the ordering of the columns within that index
is extremely critical.
I'd drop most of these and see if you can get one multi-column index
on each table that does what you need.
> I also put the following pragmas at the very beginning of my code
> rc = sqlite3_exec(db, "PRAGMA page_size=32768", NULL, 0, &zErr);
The page size can only be changed when a database is first created,
before the first CREATE TABLE command is issued. Unless you're
building the DB from scratch, this won't do anything.
> rc = sqlite3_exec(db, "PRAGMA synchronous=OFF", NULL, 0, &zErr);
This is unlikely to significantly improve read (e.g. SELECT) performance.
> rc = sqlite3_exec(db, "PRAGMA default_cache_size = 2000", NULL, 0, &zErr);
I think you want "cache_size", not "default_cache_size", although
this will still work. 2000 is the default, however. Depending on
the size of your DB, the actual size of your pages, and the
environment you're working on, you might consider bumping this up 10x
or larger.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
