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

[Brain .]

That's interesting.  Maybe there is a reason that they changed the way the
query optimization works.

Maybe you should start a discussion with the sqlite community.  Maybe you'll
get some interesting discussion on what change caused this.  You'll almost
certainly get more discussion than this mailing list which is pretty dead.

On Tue, Mar 2, 2010 at 2:33 PM, Gary Helmstetter <gh at theworld.com> wrote:

>
> On Mar 1, 2010, at 7:17 AM, Gary H wrote:
>
> > Earlier today, I 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.)
> >
> > OK, so this weekend I upgraded to Mac OS X v10.5.8, Java 1.5.0_22,
> > sqlite3 3.4.0, and sqlitejdbc v056.
> >
> > No difference in performance; the sqlite3 app responds instantly,
> > and the java program that uses sqlitejdbc takes 10 seconds to
> > complete the same query.
> >
>
> ... and later that day, I wrote:
> >
> > [...]
> > In my particular case I have a number of solutions available, all with
> > uncomfortable issues, and the least uncomfortable is to add "INDEXED
> > BY" clauses whenever I run into this problem.  The only other solution
> > I can see is to download a "modern" sqlite3 source, build it locally,
> > and put a private copy (so as not to screw up anything else they might
> > use sqlite for) on the macs used by this project.  That's not much
> > different than writing my own Java+sqlitejdbc implementation of the
> > sqlite3 subset I need.
>
>
>
> I have done the "other solution"; specifically, I downloaded the
> sqlite3 v3.6.22 source, made a local version of the sqlite3 command
> line app, used it to make a new database containing the same tables,
> indexes, and contents as the old one, and then did the query.  It
> showed the exact same (slow) performance that I was seeing through
> sqlitejdbc.
>
> This completely exonerates sqlitejdbc, obviously, but also debunks my
> theory that this problem is caused by a version skew.  This problem,
> in fact, is a plain old sqlite3 query optimization bug, introduced by
> some version after 3.4.0 .
>
> (The rest of this message is not relevant to sqlitejdbc, so if you're
> not interested in what else I've learned about the sqlite3 bug, tune
> out now, and thanks for listening ;-).
>
> I tried doing an ANALYZE on my 3.6.22 database; no improvement.
>
> Then, on a whim, I tried the query using the 3.4.0 sqlite3 on the
> 3.6.22 database.  (According to the sqlite3 web site, this should
> work; no database format incompatibilities have been introduced since
> 3.4.0).
>
> It not only worked, it worked instantly.  This provides further
> confirmation (not that it was needed) that a query optimization bug
> has been introduced in sqlite3 sometime after 3.4.0 .
>
> Regards to all,
> Gary H
>
> _______________________________________________
> SQLiteJDBC mailing list
> SQLiteJDBC at lists.hcoop.net
> https://lists.hcoop.net/listinfo/sqlitejdbc
>