On Tue, 4 Jan 2000, Brien Voorhees wrote:
> From: "Frank Apap" <[EMAIL PROTECTED]>
> > I am currently using hSql for my db with my ejb operations, but I want to
> > switch over to a more reliable and documented db. Does anyone have any
> > recommendations for simply but reliable database that will be easy to
> switch
> > to?
>
> I don't know if it integrates specifically with EJB but you might want to
> check out MySql. It runs on lots of different platforms and is one of the
> most popular free databases in use. I've been using it for development
> recently and found it to be extremely fast, easy to setup, and fairly rich
> in features. I'm not sure but I think it's Open Source, as well. You can
> check it out at www.mysql.com. O'Reilly has a book that covers MySQL called
> "MySQL & mSQL". It's available at BookPool, which has really good prices on
> technical books http://www.bookpool.com/.x/nt696p9rmi/sm/1565924347 .
I would definately recommend against MySql, it is missing several
important features of which the most important one in transactions (in
fact, I find it unbeliavble how a database that is missing such an
important feature can be so popular).
Missing transactions cause a lot of problems, but one of the grat ones is
that it is impossible to take a backup of the database and be sure that
you are able to restore it in a consitent state (I can discuss the
numerous race conditions that can occur off this list if anyone is
interested). Also, if the client crashes during a transaction you only
know one this: That the database might be in an inconsistent state.
There are other features that MySql lacks that at least I find to be
equally important:
o Views
o Stored procedures
o Subqueries
o Row level locking
The last one deserves a bit of exmplanation. It has to do with the fact
that since it doesnt have transactions, it has to solve concurrecy issues
in a different way. The solution takes by mysql is to create a command
called "lock tables" that can lock one or more tables for exclusive
access. While tables are locked no otherthread can access that table at
the same time. The problem is that the whole table is locked. So if you
have a large table of say 100K rows it means that each and every one of
those 100K rows are locked when you do somehting to the table. Needless to
say, that is very bad for performance.
Another really annoying misfeature of mysql is that you cannot create
indexes on VARCHAR columns. I assume you can figure out for yourself what
this does to either performance or storage space required.
Yes, you have propably guessed it. I have been burned by mysql. It took
several weeks of screaming until I decided to ditch it. I'm very happy
that I did and I'm writing this rant in order to prevent others from
falling into the same trap.
What should you use instead then? Well, I'd suggest any one of the Four
Great ones: Oracle, Sybase, Informix or DB/2. From what I have been told
DB/2 offers the best performance of any SQL RDBMS out there. At least
that's the one with the best TPC score (at least on Solaris). The only
ones out of the above 4 I have tested is Oracle and Sybase. My personal
favourtite is Sybase, but that's just because that's the one I know best.
Sybase 11.0 is free for all use (including commercial), the latest
version, 11.9, is free for non-commercial use. The other 3 are also free
for non-commercial use but I haven't checked their licenses in any detail
so I guess you'll have to verify for yourselves.
If for some reason you feel that you need a SQL RDBMS with source code (I
can't understand why because the 4 I mentioned above are magnitudes better
than the open sourced ones) then you can use PostgreSQL. I can't vouch for
its performance but at least it has most features one needs, including
transactions.
And a final word, is mysql totally useless? In my opinion, yes. In others,
no. The good arguments for the use of mysql that I have heard have been
situations where all you do with the tables are individual select's and
insert's. I would not classify EJB usage as siple inserts and selects.
Especially once you want to use the transaction features of the EJB's.
/E.
