views:

25

answers:

0

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 ?