Wednesday, October 31, 2007

Derby vs. SQLite - one user chimes in

I haven't used SQLite enough to compare it in any cogent way with Derby. But recently there was a thread on the derby-user list asking to compare Derby with SQLite. A lot of interesting answers, but there was one from Ryan Bobko who apparently has used both, and he had some points to make that I thought I'd share.

Personally, I think there's no comparison between Derby and SQLite.
Derby's an actual database with "all" the bells and whistles, but still
a really compact size. SQLite is an extremely fast database-like system
with a much smaller subset of features and SQL compliance. Plus, if
you're writing in Java, go with Derby. If you're writing in C or C++,
give SQLite a run-through.

Off the top of my head, SQLite doesn't support foreign key constraints,
or use column types (everything is a string, unless it's an int, which
is actually a string). In the quirks department, I've noticed join order
can have a dramatic effect on performance. What's really nice is that
the whole database is a single file, which makes using it as a save file
in your application really nice. Also, startup times are zero. I think
Derby takes a second or two to startup. Both systems support
transactions. Derby can be used in a multi-user mode, while SQLite is
strictly mono-user.

I know both databases claim to be zero-administration, but I'd say
SQLite more serious about it. I don't even know how to configure SQLite.
Derby certainly works great without administration, but there are a
whole lot of options you can muck with if you like.


ejboy said...

For Java applications I'd rather choose H2 database or even HSQLDB instead of Derby for many reasons including support of in-memory mode for testing.
I don't want to say that Derby is worse than H2, but it is slower in most cases and H2 is lighter in memory footprint and JAR size.

Anonymous said...

This week I had a cool essay on Java / Derby . I haven't used Derby before . I start orgininizing my efforts with my similaro knowledge on MySql , MsSql etc ...

After 2-3 days I found some MAJOR limitations of Derby:

Derby (until its current v 10.5) does not support something like 'select top xx' thing (Yes the standard answer 'You can do that from Java') but what if I want to use 3-4 sub selects with 'select top xx'

Derby cannot save a view if there's is an 'order by' !!

Actuall Derby cannot have a sub select with 'order by' .

After all theese I went straight to Sqlite to see if i had those limitations ... unbelievable ... Sqlite have none of the above obstacles .

I start thinking that all these guys talking about Derby , didn't seriously used it .

Ms Access 95 (!) , or 250KB Sqlite have some more capabilities of Derby !.