By the way these queries execute very fast in MySQL, and one point I noticed
is that a query is slow if tries to join two of the same tables, i.e.
pte_atm r1, pte_atm r2, pte_drug r3
may this be the problem?
_alenD
alenD wrote:
>
> Hi all,
> I wrote a small C code to execute a number of select queries.
> Select execution code is
>
> rc = sqlite3_exec(db, "BEGIN;", callback, 0, &zErr);
> for(eQ = 0; eQ < queries.size(); eQ++){
> cout << "Executing " << eQ << "/" << queries.size() << "\t" <<
> queries[eQ].query <<endl;
> if (queries[eQ].query != ""){
> rc = sqlite3_exec(db, queries[eQ].query.c_str(),
> callback, 0, &zErr);
> if( rc!=SQLITE_OK ){
> cout << zErr << endl;
> writeToLog("executeQueries::SQL error" ,'.');
> getchar();
> }
> }
> else
> callback(NULL, -1, NULL, NULL);
> }
> rc = sqlite3_exec(db, "END;", callback, 0, &zErr);
>
> Some queries show poor execution time, one such example is below:
>
> SELECT COUNT(DISTINCT r0.Arg0||'-'||'F') AS A1
> 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:
>
> cardinality of pte_atm is 9189
> CREATE TABLE `pte_atm` ( `Arg0` VARCHAR(80), `Arg1` VARCHAR(80), `Arg2`
> VARCHAR(80), `Arg3` VARCHAR(80), `Arg4` DECIMAL(18,3) DEFAULT 0, `id_aux`
> INTEGER);
>
> CREATE INDEX I13 on pte_atm(Arg0 ASC);
>
> CREATE INDEX I14 on pte_atm(Arg1 ASC);
>
> CREATE INDEX I15 on pte_atm(Arg2 ASC);
>
> CREATE INDEX I16 on pte_atm(Arg3 ASC);
>
> CREATE INDEX I17 on pte_atm(Arg4 ASC);
>
> cardinality of pte_active is 299
> CREATE TABLE `pte_active` (`Arg0` VARCHAR(80), `Arg1` VARCHAR(80) DEFAULT
> 'F', `is_covered_aux` INTEGER DEFAULT 0, `id_aux` INTEGER NOT NULL DEFAULT
> 0);
>
> CREATE INDEX I4 on pte_active(Arg0 ASC);
>
> CREATE INDEX I5 on pte_active(Arg1 ASC);
>
> I also put the following pragmas at the very beginning of my code
> rc = sqlite3_exec(db, "PRAGMA page_size=32768", NULL, 0, &zErr);
> rc = sqlite3_exec(db, "PRAGMA synchronous=OFF", NULL, 0, &zErr);
> rc = sqlite3_exec(db, "PRAGMA temp_store = MEMORY", NULL, 0, &zErr);
> rc = sqlite3_exec(db, "PRAGMA default_cache_size = 2000", NULL, 0, &zErr);
>
> what might the problem be, how can I improve the execution time.
>
> thanks in advance
>
> _alenD
>
--
View this message in context:
http://old.nabble.com/poor-execution-time-with-select-queries-tp27406303p27407255.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
