From sqlite-users  Fri Jul 03 07:02:57 2009
From: knightfeng <knightfeng () 126 ! com>
Date: Fri, 03 Jul 2009 07:02:57 +0000
To: sqlite-users
Subject: [sqlite] How to do 5,000,000 "select"s as fast as possible
Message-Id: <11325422.219591246604577505.JavaMail.coremail () bj126app43 ! 126 ! com>
X-MARC-Message: https://marc.info/?l=sqlite-users&m=124660458606955

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