I'm using Mac OS X v10.4.11 with the standard Java (1.5.0_19) and sqlite3 (3.1.3) that came it. (Yeah, a little old... but see comment below.)
I have a sqlite3 database with a table with a few hundred thousand rows, with "name" and "stored" text columns. Name is one of six (so far) short strings; stored is a 19-character standard date-time string. Each column has a stand-alone index. There are only six unique name values. The following query:
select distinct name from myTable where stored >= date("now");
lists the relevant names instantly when I perform it through the Mac OS X "sqlite3" application. But it takes over 2 seconds to find each name (total of about 15 seconds) when I do the same thing in the usual way in my application:
String q = "SELECT DISTINCT name FROM myTable " +
"WHERE stored >= DATE('now');" ;
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(q);
while (rs.next()) {
final String s = rs.getString("symbol");
System.err.println("Got " + s);
}
rs.close();
}
I've tried this with both sqlitejdbc-v054 and sqlitejdbc-v055 . No perceptible difference.
Is this a known deficiency? If not, anyone have any suggestions how to attack it ?