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