[sqlite] poor execution time with select queries

[alenD]

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-tp27406303p27406303.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