[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
