[sqlite] poor execution time with select queries

2010-02-01 Thread 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

Re: [sqlite] poor execution time with select queries

2010-02-01 Thread alenD 

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

[sqlite] creating database from dump file within C code

2010-01-24 Thread alenD 

Hi all,
I am a newbie to sqlite!
Is there a way to create and populate a sqlite database from an already
created dump file within C code.

thanks!

_alen D.
-- 
View this message in context: 
http://old.nabble.com/creating-database-from-dump-file-within-C-code-tp27295724p27295724.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

Re: [sqlite] creating database from dump file within C code

2010-01-24 Thread alenD 

Thanks, it worked perfect!

_alenD


Roger Binns wrote:
 
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 
 alenD wrote:
 Is there a way to create and populate a sqlite database from an already
 created dump file within C code.
 
 Load the dump text so you have it as one long string and then call
 sqlite3_exec pointing to that string.
 
 Behind the scenes what happens is that sqlite3_prepare is called, then
 sqlite3_step until done then sqlite3_finalize.  sqlite3_prepare only works
 on the first statement, but tells you where the next one begins so this is
 done in a loop until there are no next statements.
 
 Roger
 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.4.9 (GNU/Linux)
 Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
 
 iEYEARECAAYFAktcfIoACgkQmOOfHg372QRJ7QCg0/X3/cZLYa9bW6lJE1BJwmzL
 PdUAn3FVe9+WOe6/Q0cgSLJXFgv5iP/f
 =glCf
 -END PGP SIGNATURE-
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
 
 

-- 
View this message in context: 
http://old.nabble.com/creating-database-from-dump-file-within-C-code-tp27295724p27299030.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

[sqlite] creating indexes

2010-01-24 Thread alenD 

Hi,
Should an index be created before insertions or after insertions?:super:

thanks in advance!

_alenD
-- 
View this message in context: 
http://old.nabble.com/creating-indexes-tp27299680p27299680.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