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

[Gary Helmstetter]

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