Re: [sqlite] SQLite JDBC driver performance

[Adam Megacz]

Christopher Mason  writes:
> [Apologies for posting this here, but the sqlitejdbc list appears to be 
> defunct.]

Google deleted it without warning or explanation:

  http://www.zentus.com/sqlitejdbc/google-group-disappearance.html

It has been relocated here:

  https://lists.hcoop.net/listinfo/sqlitejdbc

  - a

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite JDBC driver performance

[Christian Smith]

On Wed, Jan 21, 2009 at 03:48:50PM -0800, Jim Dodgen wrote:
> are you running the pure version of the sqlite driver?


>From the profile output below, I'd say the pure Java version was being
used.

My guess is that the Java version is slower due to the fact that the Java
version is based on NestedVM, which basically converts MIPS binaries into
Java class files, allowing C code to be used in Java without expensive and
proprietry compilers, via compilation with a MIPS targeted compiler.

Such a scheme, while portable, will not be as fast as a direct C to Java byte
code compiler.

Notice, also, that BLOBS are being accessed. This is probably causing cache
thrashing. It'd be interesting to see relative times for queries that do not
access BLOBS.

I guess the original poster needs to retry his query with the JNI based
JDBC driver. MacOS support should be provided in the download jar on the
project front page, so perhaps the OP has downloaded the wrong jar?

Christian

> 
> 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:
> > real0m44.708s
> > user0m5.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
> > 

[sqlite] SQLite JDBC driver performance

[Christopher Mason]

[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:
real0m44.708s
user0m5.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