are you running the pure version of the sqlite driver? On Tue, Jan 20, 2009 at 3:10 AM, Christopher Mason < christopher.ma...@proteomesoftware.com> wrote:
> [Apologies for posting this here, but the sqlitejdbc list appears to be > defunct.] > > Hello. > > I'm noticing fairly severe performance difference between running a > complex query from the sqlite 3.6.7 command line and from within the > sqlite jdbc driver version 0.54 available at [1]. > > Sqlite command line: > real 0m44.708s > user 0m5.211s > sys 0m1.994s > > java: > 2.7min > > I've tried a number of things: > > * I've tried both sqlite 3.6.1 and 3.6.7 (I upgraded the jdbc driver > to .7); > * I've tried with and without the shared_cache option; this has no > effect for me. > * I've tried changing the optimization settings for sqlitejdbc to > match those of the command line tool I build and installed (-O2). > * Explicitly set the cache size of the > > > I've done some profiling [2] and the prevalence of > 'sqlite3_enable_shared_cache' in the java version is surprising. The > java version also uses significantly less CPU than the command line > version. > > I've copied and pasted the SQL out of the prepared statement, so I'm > confident that I'm running the same SQL. It's a single query, so I > doubt that JDBC/JNI overhead is to blame. > > Any help or ideas would be most appreciated! > > Thanks so much, > > -c > > > [1] http://zentus.com/sqlitejdbc/ > > > [2] > > Profiling with shark on Mac OS X 10.5.6: > > java: > self total > 0.0% 50.2% sqlite3_step > 5.5% 48.4% sqlite3_blob_open > 9.2% 40.0% sqlite3_randomness > 8.4% 25.1% sqlite3_enable_shared_cache > 2.1% 16.0% sqlite3_config > 5.4% 7.6% sqlite3_malloc > 1.1% 1.2% sqlite3_config > 0.0% 0.1% sqlite3_free > 0.1% 0.1% sqlite3_vfs_unregister > 0.8% 1.0% sqlite3_mutex_try > 0.1% 0.1% sqlite3_mutex_leave > 0.2% 3.3% sqlite3_value_type > 0.9% 2.7% sqlite3_os_end > 0.2% 0.2% sqlite3_mutex_try > 0.1% 0.1% sqlite3_snprintf > 0.3% 0.3% sqlite3_free > 0.2% 0.2% sqlite3_malloc > 0.1% 0.1% sqlite3_snprintf > > command line: > self total > 0.0% 64.1% sqlite3_exec > 0.0% 64.0% sqlite3_step > 8.6% 63.3% sqlite3Step > 0.3% 21.0% sqlite3VdbeCursorMoveto > 6.0% 20.6% sqlite3BtreeMovetoUnpacked > 0.3% 10.6% moveToChild > 1.0% 10.2% getAndInitPage > 0.5% 5.1% sqlite3PcacheFetch > 3.0% 4.5% pcache1Fetch > 0.6% 0.9% pthreadMutexEnter > 0.2% 0.2% dyld_stub_pthread_self > 0.1% 0.1% dyld_stub_pthread_mutex_lock > 0.2% 0.2% pcache1PinPage > 0.2% 0.2% sqlite3_mutex_leave > 0.1% 0.1% sqlite3_mutex_enter > 0.1% 0.1% pthreadMutexLeave > 0.1% 0.1% dyld_stub_pthread_mutex_unlock > 0.1% 0.1% pthreadMutexLeave > 0.0% 3.3% sqlite3BtreeGetPage > 0.3% 0.3% btreePageFromDbPage > 0.0% 0.2% pagerPagecount > 0.1% 0.1% sqlite3BtreeInitPage > 0.1% 0.1% sqlite3PagerGetData > 0.1% 0.1% sqlite3PagerGetExtra > 0.1% 0.1% btreePageFromDbPage > 0.1% 0.1% sqlite3PcacheFetch > 0.3% 2.0% moveToRoot > 1.9% 1.9% sqlite3GetVarint > 0.1% 0.1% sqlite3Get4byte > 0.1% 0.1% sqlite3PagerUnref > 0.1% 0.1% sqlite3GetVarint > 0.1% 0.1% sqlite3Get4byte > 1.5% 15.1% sqlite3BtreeMovetoUnpacked > > > > -- > Christopher Mason Proteome Software (503) 244-6027 > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Jim Dodgen j...@dodgen.us _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
