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 <[email protected]>
To: sqlite-users <[email protected]>
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
[email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
