18

For what type of requirements would you choose Apache Derby (or Java DB) over MySQL (or vice versa)? I looked around and people just compare the two but no one talks about when to consider each one. I am developing a web-based application using Glassfish + Java/Restlet + MySQL.

I expect around 100-200 users for this system with a load of about 30-50 concurrent users at a given time - mostly.

I was told to look at Derby if I wish to make the web-app downloadable/distributable. But is that the only reason why I would use it? Is it suitable for web-apps? Has anyone used it? What has been your experience and when do you choose one over the other? (Most discussions of the comparison predate MySQL v5 when it didn't have support for stored procedures, triggers etc., But that is no longer the case).

I can understand a standalone DB server model with a web-server sending the requests, but how does this model change with an embedded db? Or does one default to using Derby in the network configuration?

PhD
  • 11,202
  • 14
  • 64
  • 112
  • As a rule of thumb: If your app will be small enough to contain the whole system to one server/machine: you can do either one but and embedded database makes distributing your app easier on end-users. If, on the other hand, you or your users may need to scale the app beyond one server then embedded is not the right choice. That being said, this question is off-topic. –  Mar 03 '15 at 18:12

2 Answers2

19

Why are Derby and MySQL the only RDMBS you consider? If you say Derby, you should check out HSQLDB, H2, SQLite as well. If you say MySQL, you should check out Postgres as well (which has a lot more features).

This is just to name some free RDBMS. Of course, as Charlie already put it, there are lots of others and lots of reasons to go either way. Check out this (IMO excellent) comparison page on Wikipedia, where you will find benefits and limitations of any RDBMS:

http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

As far as your requirement about your webapp being "downloadable" is concerned, of course you can embed an RDBMS (any of Derby, H2, HSQLDB) in your webapp. But you can also just make your MySQL or Postgres or whatever integration configurable and give your downloaders instructions about how to set up your webapp themselves. After all, when you use a container-configured DataSource for your webapp, this configuration can be done easily.

Now, even if you think it might be easier for you to develop your webapp with an embedded database, you should always think one step ahead. Questions like:

  • Will you be able to connect to that database directly, in order correct data inconsistencies easily? (It will happen to all of us)
  • Will you be able to alter the schema easily?
  • Will you be able to backup your data easily?
  • etc etc... there are more maintenance questions, too

Since your comments suggest that your data is increasing over time, and it should persist, I wouldn't choose an embedded version, but keep the data separate from the application. Note that this doesn't exclude Derby from your application design. It just means you'd have to run Derby as a standalone server.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Great! Exactly the information I was looking for. Yes I did look at the wikipedia page and hence the ellipsis in the title :) Since posting the question those concerns did hit me and we decided to go with MySQL for now and offering 'an' embedded database based solution for downloading and evaluating the tool with the option of switching over to MySQL when they need to. – PhD Mar 09 '11 at 18:08
  • Sounds like a sound solution. Good luck! :-) – Lukas Eder Mar 10 '11 at 08:31
  • With all due respect I have to disagree with this answer. The idea that you can just have your users set up a MySQL database without some very specific basic understanding of how it works is silly. It's not hard for guys like you and I but for the average user -- come on. Further, your assumption that growing data and persistence require an enterprise-level database solution like MySQL is a little unfair given the details provided. Finally, this is really a question about embedded databases vs a scale-able client-server model. Suggesting other RDBMS is not constructive to the answer. –  Mar 03 '15 at 18:05
  • @TomDworzanski: Well, I'm not so sure what "web app" means / meant at the time. And the OP didn't say what kind of user this will be. Besides, you can ship some installable databases with your deliverable and install them yourself (e.g. Oracle XE, SQL Server Express, etc.) without being truly "embedded". We can perhaps agree that the question isn't really very meaningful – Lukas Eder Mar 03 '15 at 18:46
  • About H2 and HSQLDB being faster than Derby (JavaDB) is dubious. Maybe back in 2011 when this answer was written. You can check benchmarks here http://www.polepos.org/ – Frankie Jul 02 '18 at 11:12
  • 1
    @Frankie: Your benchmarks seem to confirm that Derby is much slower than HSQLDB. Of course, those benchmark results seem quite flawed. In one test, Hibernate/HSQLDB heavily outperforms JDBC/HSQLDB, and no way MongoDB is *that* much faster... But anyway. I've removed the claim of one being faster than the other, because I can't back this with data – Lukas Eder Jul 02 '18 at 18:08
  • @LukasEder definitely. I was sleepwalking when I wrote this; what I actually intended to write was exactly the opposite and suggest you link the benchmark. Sorry about that. – Frankie Jul 02 '18 at 22:07
  • @Frankie: It happens to the best :-) – Lukas Eder Jul 03 '18 at 07:54
9

The requirements that are going to make the difference are the so-called "nonfunctional" requirements: capacity, reliability, throughput (and response time), availability, and security; this along with the software's own issues, like how easily it's available, how hard it will be to maintain software based on it, and so forth.

Oracle is very fast, very robust, very well supported, and very expensive.

MySQL is a good general choice with used widely. It can be configured for high availability and reliability (through mirroring and master-slave), it's well understood by a lot of programmers, and integrates well into a lot of platform software like Grails, Rails, and JBoss.

Derby is good because it's very platform independent and a lot of people read Java easily.

SQLite is fast, lightweight, and more or less native on Macs.

... and so on.

First, figure out what nonfunctional requirements are important, then choose a DBMS.

Update

Okay, following up your comment.

With those numbers, let me ask first why a separate RDBMS at all? That's 1000 rows -- consider simply storing them in-memory, say in a collection of Collections that you serialize.

If you really need a DB, say because you're using Rails, then you're not challenging ANY RDBMS -- it may be hard to choose because you're in a domain where all the choices are perfectly good. If so, then pick the one that's easiest to use and easiest to support, which is probably but not certainly MySQL, just because everyone uses it.

Charlie Martin
  • 110,348
  • 25
  • 193
  • 263
  • Agreed. And that's what I'm wanting to know - for what type of NFRs would one select MySQL over Derby (or vice versa). Where is it the Derby (or MySQL) falters that would force me to make a choice for the given scenario and the given constraints -> At most 20 tables that could grow up to 50 entries each, easily and consistently across teams, 'good' reliability, moderate response time is acceptable (3-10 seconds is fine :), moderate availability (intranet app) – PhD Feb 12 '11 at 01:40
  • Sorry. Seems I wasn't clear - the data is relational and I have about 15 tables each expected to go upto 1000 rows and would monotonically increase over a period of time (yearly?). The previous data would need to be maintained as history. Simple serialization is not well suited for this task - it'll just be a nightmare with the relational aspects and lazy loading. I should rephrase my question to "what are the limitations of Derby when using it for an (intranet) enterprise application?" rather than MySQL vs Derby – PhD Feb 13 '11 at 19:17