Databases are like cars and trucks:
there isn't one choice that's
right for everyone.
is occasionally asked what the best database is.
This is a bit like asking, what is the ideal car?
The first question is "what do you want it for?"
and the second "what is the budget"?
Some factors worth considering are:
- Hard constraints from the target application
(many database related applications only run on certain databases)
- Local experience and expertise
- Scale of the database (the Mack truck versus mini-scooter question)
- Local versus remote/unattended operation
- Reliability requirements
- Ease-of-use requirements
- Performance requirements
The marketing materials from the various database
vendors often suggest performance is a critical
factor in database selection.
In fact most performance problems can be traced to
- inappropriate or poorly worked out database design
- failure to execute correctly against the design
- insufficient hardware: too little RAM, slow disks, small network pipes and the like
None of these are significantly affected by the underlying speed of the
database.
The 800 pound gorilla of the database market.
Oracle is a perfectly reasonable choice
as a primary database,
although it has some "gotchas" which
make consideration
of alternatives prudent.
In particular,
Oracle is rather harder to administer than some other databases.
In fact,
as one of our colleagues discovered,
it is possible to get an Oracle database so curdled
that it is not possible to run the restore procedures.
has done ports from
Informix to Oracle.
Our experience is that the Oracle cost-based optimizer was not in the same league
as the Informix optimizer;
it needed a great deal more help from nearby humans
if it was to execute queries at a reasonable speed,
given a specific hardware configuration.
Oracle's PL/SQL language has some nice features.
PL/SQL's debugging support was bizarre,
requiring manual intervention by the
DBA for setup.
Oracle's bulk load/unload procedures -- needed for conversions,
synchronization with remote databases, and the like --
are unimpressive.
Oracle's strong suit is its suites: Oracle
has a wealth of applications, including much stuff for the web.
(Apparently this plentitude is partly due to political issues within Oracle:
different factions will construct essentially the same
tool in an attempt to garner favor from Ellison.)
The other 800 pound gorilla of the database market.
The current version, SQL Server 2000, appears to represent an attempt
to compete with Oracle.
We're not entirely clear that it is successful in doing this,
but it does represent a very significant step forward from earlier versions.
Integration with Access and NT is, of course,
a strong suit.
The user interface is clean and easy to use.
The data transformation services (DTS) tools
are good.
(Some shops have acquired SQL Server just for DTS.)
The system administration tools are easy to use;
features such as automatic running of statistics
should be more widely emulated.
The stored procedures execute very crisply.
There may be some concurrency issues in aggressively multi-user
environments --
would be hesitant to use SQL Server
to build an airline reservation system or the like -- but
for small N situations SQL Server seems reasonable.
The stored procedure language has a bit too much
syntactic sugar for our taste,
but not so much as to make it unliveable.
The default front-end to the stored procedure language
would have been impressive in a DOS
context,
but is frankly a bit clunky in early 21st century terms.
|
has worked with Informix since 1987,
starting with version 2 (Informix is now on version 9).
has been particularly happy with
the Informix Standard Engine (SE) product.
This has offered an unequaled
combination of ease of use and performance,
within its weight class
(again, don't build an airline reservation system with this).
In general the Informix line offers a nice combination of ease of administration
and performance,
making
it surprising they do not have more market share than they do.
Their cost-based optimizer is good,
and has only occasionally required hinting.
(Hinting: putting comments in an SQL query to tell the optimizer
how to run the query to achieve best performance.)
The Informix 4GL language -- a tool oriented to the old 24 x 80 screens --
was (and is) quite good.
The Informix stored procedure language (SPL) is fast enough,
but curiously limited in features by current standards:
primitive exception handling for instance,
and relatively few operators.
Informix does not offer a lot of GUI tools,
but good GUI tools are available from third parties.
has built a variety
of Informix databases and applications.
These include
cable advertising,
medical laboratory,
and
financial applications.
built a large
cemetery management database in this.
Ingres has good performance
and a clean design.
found especially attractive
the fact that all of the reports and screens
were fully described within the database itself.
This meant that if some small change was required to a field -- say widening it --
the change could often be applied by doing a single update
to the internal tables,
without need to go the perhaps twenty screens where that field was used.
Ingres was originally an academic product.
This perhaps showed in the error messages,
which would sometimes have been less confusing if written in Greek,
as then no time would have been wasted attempting to decipher them.
A fast if somewhat strange product,
Omnis is really a hierarchical rather than a relational database.
But it gives best overall performance
if its native hierarchical
features are eschewed in favor of the relational.
Omnis is characterized by a somewhat idiosyncratic
internal language but very good performance.
It has a rich feature set.
As its name suggests,
Filemaker is really a flat file database,
albeit with some relational features,
and the word "relational" featured prominently on the shrinkwrap.
It is very easy to build a simple but attractive front-end
with this tool.
And it is sufficiently simple that
much routine database maintenance
and enhancement
may be done
by non-IT personnel.
4th Dimension
is another effective tool at the low end.
It has very good facilities for constructing
GUI front-ends,
and a rich internal language.
It is not a relational database,
again not withstanding the
statements on the shrinkwrap.
has found 4th Dimension to be a good
tool for building user-friendly one person
databases.
In our experience,
its multi-user and networking performance
rule it out for use on a larger scale.
has worked with a number of other SQL and non-SQL databases as well,
e.g. Empress, Access, File, MySQL, Postgres, and so on.
|