[SQLiteJDBC] Why is this query answered instantly by the sqlite3 application, but slowly using sqlitejdbc?

[Brain .]

With such a large time difference, it would be my guess that sqlite is not
using the index you want it to use.  It would be surprising that sqlitejdbc
would make a difference.  It's pretty much a small wrapper over the native
functions.

Are you using the exact same database in both programs?  Of course, if you
aren't then there could be differences in their indexes.

Sqlitejdbc uses sqlite 3.6.14.2.  So maybe that is why there is a
difference.

You have several options to debug this.
1.  You can try using the EXPLAIN command to try to figure out which index
it is using.  You can execute this as a query using sqlitejdbc.  It might be
a little tricky to get the output of it, but it works.
2.  You can try using the INDEXED BY clause in your query to force it to use
an index that you want it to use or give an error why it can't.
http://www.sqlite.org/lang_indexedby.html
3.  http://www.sqlite.org/optoverview.html
This page has several helpful sections such as the sqlite ANALYZE command
which will generate statistics about tables which helps it choose which
indexes it should use.

I hope this helps.

On Sun, Feb 28, 2010 at 4:20 PM, Gary Helmstetter <gh at theworld.com> wrote:

> 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.)
>
> 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. (Essentially, the table
> tracks events in the life of 6 entities; its other columns describe
> the individual events.) 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. I get the same results, but it takes over
> 2 seconds to find each name (total of about 15 seconds), when I do the
> exact same query in the obvious 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.
>
> A wild speculation, based on the way it behaves, is that when the
> sqlite3 application does the query sqlite uses the indexes optimally
> (ie. applying "stored" first), but for some reason this doesn't happen
> when sqlitejdbc does the same query.
>
> Is this a known deficiency? If not, anyone have any suggestions how to
> attack it ?
>
>
> _______________________________________________
> SQLiteJDBC mailing list
> SQLiteJDBC at lists.hcoop.net
> https://lists.hcoop.net/listinfo/sqlitejdbc
>