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.

2 comments:

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 !.