[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]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic