mysql vs sqlite vs hsql

[stan k.]

First of all i'm on a win32 platform using java. I also have mysql
installed. My question isabout benchmarks and multiple inserts &
selects
SQLITE: http://www.sqlite.org/
HSQL: http://hsqldb.sourceforge.net
I current have a mysql database of approx. 80mb.
Each day I Insert approx .5mb of new records into this table,
and I might also run mutiple Update, Delete, and Select queries as
well.
I'm trying to get an idea of how fast a sql database engine will run
given that is is inside of the JVM. I know it's going to be slower
than a db engine written in C and that's a trade off for being
portable to different operating systems.
What I want to know is how much of a trade off though - I don't want
to have to wait 10 mins or deal with screen freezing... Right
now on a windows platform using mysql things move really fast.
Can anyone give me an idea of the time delay needed to do these
Inserts in HSQL (ie: how much does the jvm slow things down)..
Thanks in advance

[Pierre-Frédéric Caillaud]

Level 1:

- have you optimized your database design ?

- have you cleaned your tables ?

OPTIMIZE TABLE blah;
Level 2:
- Send several inserts per query (you save on IPC time)
- Use block inserts (inserts many rows at a time) => much much faster

Read the mysql dox

Subsidiary:

What's this doing on the python mailing list ?
On 23 Jun 2004 23:02:23 -0700, stan k. <st**********@yepmail.net> wrote:

First of all i'm on a win32 platform using java. I also have mysql
installed. My question isabout benchmarks and multiple inserts &
selects
SQLITE: http://www.sqlite.org/
HSQL: http://hsqldb.sourceforge.net
I current have a mysql database of approx. 80mb.
Each day I Insert approx .5mb of new records into this table,
and I might also run mutiple Update, Delete, and Select queries as
well.
I'm trying to get an idea of how fast a sql database engine will run
given that is is inside of the JVM. I know it's going to be slower
than a db engine written in C and that's a trade off for being
portable to different operating systems.
What I want to know is how much of a trade off though - I don't want
to have to wait 10 mins or deal with screen freezing... Right
now on a windows platform using mysql things move really fast.
Can anyone give me an idea of the time delay needed to do these
Inserts in HSQL (ie: how much does the jvm slow things down)..
Thanks in advance

--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

[Steve Menard]

stan k. wrote:

First of all i'm on a win32 platform using java. I also have mysql
installed. My question isabout benchmarks and multiple inserts &
selects
SQLITE: http://www.sqlite.org/
HSQL: http://hsqldb.sourceforge.net
I current have a mysql database of approx. 80mb.
Each day I Insert approx .5mb of new records into this table,
and I might also run mutiple Update, Delete, and Select queries as
well.
I'm trying to get an idea of how fast a sql database engine will run
given that is is inside of the JVM. I know it's going to be slower
than a db engine written in C and that's a trade off for being
portable to different operating systems.
What I want to know is how much of a trade off though - I don't want
to have to wait 10 mins or deal with screen freezing... Right
now on a windows platform using mysql things move really fast.
Can anyone give me an idea of the time delay needed to do these
Inserts in HSQL (ie: how much does the jvm slow things down)..
Thanks in advance

<followup redirected to comp.lang.java.programmer, since this question
has nothing to do with python>

Quite a few things to address in this question ...

First, lemme dispell the very common misconception that java is slow. It
USED to be, back when it was fully interpreted. Nowadays, most benchmark
agree that java is on par with C/C++ in raw speed, depending on the kind
of operation you are doing. Modern Java most notable problems are with
memory usage and startup speed, both of which are being addressed (to a
point) in the upcoming JRE 1.5.

Second, a database system is not even CPU bound, but rather IO bound. In
that respect, the algorithm used for in-memory caching and indexing will
have far more influence on overall speed.

As for hsqldb, past experience shows it to be remarkably fast. As you
are clearly trying to embed the database, I would nto even be surprised
that hsql would come out faster for you, since you can get rid of the
network-call overhead of calling MySQL. It will have no problem handling
the 500 KB of data a day you wish to insert, and should cause no more
"screen freezes" than any other databases.

Lastly sqlite does not (as far as I know) have a JDBC driver, not is it
as full-featured are hsql.

I would recommend you look at your requirements (multi user, gui app,
web app, etc ...) and make a choice based on that, rather than the
mythical slowness of the JVM.

Steve

[stan k.]

Thanks very much to both of you for the information - sorry about
posting to here. I posted here only because when I did a search on
google newsgroups it did not allow me to reply to other discussion
threads about sql because they were too old. This group recently had a
discusion thread about sql so I posted here...