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