[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    [sqlite] How to do 5,000,000 "select"s as fast as possible
From:       knightfeng <knightfeng () 126 ! com>
Date:       2009-07-03 7:02:57
Message-ID: 11325422.219591246604577505.JavaMail.coremail () bj126app43 ! 126 ! com
[Download RAW message or body]

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


[prev in list] [next in list] [prev in thread] [next in thread]