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

[Gary Helmstetter]

On Mar 2, 2010, at 4:05 PM, Brain . wrote:

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

It's all a blur to me now, but I read the entire revision history a  
couple days ago looking for clues to the supposed version skew.  The  
query optimizer has been mentioned frequently in revisions in the last  
year.  I do remember entries concerning incorrect-optimization bugs.

Specifically, one mentioned adding a SQLITE_ENABLE_STAT2 compilation  
option that enables a second statistics table "to help SQLite to  
choose a better query plan under certain situations [...] to help it  
estimate how many rows will be selected by a range constraint in a  
WHERE clause" - which is my situation.

So, I built a second private copy of sqlite3 with that compilation  
conditional enabled, and ran ANALYZE with that.  It generated the  
promised sqlite_stat2 table - and lo and behold, executed the query  
instantaneously!  In other words, the optional feature cures the query  
optimizer bug that I discovered.

>
> Maybe you should start a discussion with the sqlite community.   
> Maybe you'll get some interesting discussion on what change caused  
> this.

I agree.  I think the fact that ENABLE_STAT2 cures it is an important  
clue.  I've added a note on my To Do list to report all this to their  
list.  Chasing this problem has put me several days behind schedule,  
so it will have to wait a while, but I'll get around to it eventually.

>  You'll almost certainly get more discussion than this mailing list  
> which is pretty dead.

Well, in this lists' defense:
• This didn't turn out to be a sqlitejdbc bug, after all
• You responded - and set me on the right track to nailing the problem!

By the way, from a note on www.apple.com/opensource, it looks like  
Snow Leopard comes with sqlite3 v3.6.12 (released 31-March-2009), at  
least initially.  Having just read about the various bug fixes after  
that release, I think I may drag my feet about upgrading to Snow  
Leopard - since lots of crucial Mac OS X applications, like mail, use  
sqlite!  Of course for my own projects, from now on I'll be  
downloading whatever sqlite is built into sqlitejdbc, and using that.

Regards,
Gary H