From sqlite-users  Fri Jul 03 07:22:45 2009
From: freshie2004-sqlite () yahoo ! com ! au
Date: Fri, 03 Jul 2009 07:22:45 +0000
To: sqlite-users
Subject: Re: [sqlite] How to do 5,000,000 "select"s as fast as possible
Message-Id: <340083.92701.qm () web110809 ! mail ! gq1 ! yahoo ! com>
X-MARC-Message: https://marc.info/?l=sqlite-users&m=124660576908058

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