[sqlite] How to do 5,000,000 "select"s as fast as possible

[knightfeng]

Dear all,
      We have to do 5,000,000 "select"s from a database with 40000 record 
(using C API). We do it as follow:
1. "create table refGene (geneName vchar, geneID vchar, chromo vchar, strand 
char(1), txStart number, txEnd number, cdsStart number, cdsEnd number, exonNum 
number, exonStart vchar, exonEnd vchar)"
 
2.  insert ....  40000 records.
 
3.  rc = sqlite3_exec(db, "create index indexwig on refGene (chromo, txStart, 
txEnd)" , NULL , NULL, &zErrMsg);
 
4. repeat 5,000,000  {
       sprintf(sqlCmd, "select geneName,exonStart,exonEnd from refGene where 
chromo=='%s' and txStart <= %d and     txEnd>=%d", one.chromo.c_str(), 
one.start, one.end);
 
       rc = sqlite3_prepare(db, sqlCmd, strlen(sqlCmd), &stmt, NULL);
       rc = sqlite3_step(stmt);
       while(rc == SQLITE_ROW)
      {  
            ......
            rc = sqlite3_step(stmt);
       }
        rc = sqlite3_finalize(stmt);
}

The 5,000,000 "select"s take about 30 minutes in our machine (3Gb memory, 2 x 
1.8G Hz CPU). 
 
Are there some faster ways to use sqlite to do the 5,000,000 "select"s ?
 
Thanks
 
Zhixing
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users