7.1.2. Designing Applications for Portability
Because all SQL servers implement different parts of standard
SQL, it takes work to write portable database applications. It
is very easy to achieve portability for very simple selects and
inserts, but becomes more difficult the more capabilities you
require. If you want an application that is fast with many
database systems, it becomes even more difficult.
All database systems have some weak points. That is, they have
different design compromises that lead to different behavior.
To make a complex application portable, you need to determine
which SQL servers it must work with, and then determine what
features those servers support. You can use the MySQL
crash-me program to find functions, types,
and limits that you can use with a selection of database
servers. crash-me does not check for every
possible feature, but it is still reasonably comprehensive,
performing about 450 tests. An example of the type of
information crash-me can provide is that you
should not use column names that are longer than 18 characters
if you want to be able to use Informix or DB2.
The crash-me program and the MySQL benchmarks
are all very database independent. By taking a look at how they
are written, you can get a feeling for what you must do to make
your own applications database independent. The programs can be
found in the sql-bench directory of MySQL
source distributions. They are written in Perl and use the DBI
database interface. Use of DBI in itself solves part of the
portability problem because it provides database-independent
access methods. See Section 7.1.4, “The MySQL Benchmark Suite”.
If you strive for database independence, you need to get a good
feeling for each SQL server's bottlenecks. For example, MySQL is
very fast in retrieving and updating rows for
MyISAM tables, but has a problem in mixing
slow readers and writers on the same table. Oracle, on the other
hand, has a big problem when you try to access rows that you
have recently updated (until they are flushed to disk).
Transactional database systems in general are not very good at
generating summary tables from log tables, because in this case
row locking is almost useless.
To make your application really database
independent, you should define an easily extendable interface
through which you manipulate your data. For example, C++ is
available on most systems, so it makes sense to use a C++
class-based interface to the databases.
If you use some feature that is specific to a given database
system (such as the REPLACE statement, which
is specific to MySQL), you should implement the same feature for
other SQL servers by coding an alternative method. Although the
alternative might be slower, it enables the other servers to
perform the same tasks.
With MySQL, you can use the /*! */ syntax to
add MySQL-specific keywords to a statement. The code inside
/* */ is treated as a comment (and ignored)
by most other SQL servers. For information about writing
comments, see Section 9.4, “Comment Syntax”.
If high performance is more important than exactness, as for
some Web applications, it is possible to create an application
layer that caches all results to give you even higher
performance. By letting old results expire after a while, you
can keep the cache reasonably fresh. This provides a method to
handle high load spikes, in which case you can dynamically
increase the cache size and set the expiration timeout higher
until things get back to normal.
In this case, the table creation information should contain
information about the initial cache size and how often the table
should normally be refreshed.
An attractive alternative to implementing an application cache
is to use the MySQL query cache. By enabling the query cache,
the server handles the details of determining whether a query
result can be reused. This simplifies your application. See
Section 5.13, “The MySQL Query Cache”.
