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

List:       sqlite-users
Subject:    Re: [sqlite] How to do 5,000,000 "select"s as fast as possible
From:       freshie2004-sqlite () yahoo ! com ! au
Date:       2009-07-03 7:22:45
Message-ID: 340083.92701.qm () web110809 ! mail ! gq1 ! yahoo ! com
[Download RAW message or body]

Briefly...

sqlite_prepare_v2(db, "select geneName,exonStart,exonEnd from refGene where chromo=? \
and txStart <=? and     txEnd>=?" ... etc etc)

start loop of 5000000 records

    Use bindings to assign the parameters.

    step through it

    Reset and clear bindings.

end loop

sqlite3_finalize();

See sqlite_bind_[xxxx](), sqlite_reset(), sqlite_clear_bindings() in manual





________________________________
From: knightfeng <knightfeng@126.com>
To: sqlite-users <sqlite-users@sqlite.org>
Sent: Friday, 3 July, 2009 5:02:57 PM
Subject: [sqlite] How to do 5,000,000 "select"s as fast as possible

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



      ____________________________________________________________________________________
 Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
_______________________________________________
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]