(mysql.info.gz) Portability
Info Catalog
(mysql.info.gz) Design Limitations
(mysql.info.gz) Optimize Overview
(mysql.info.gz) Internal use
7.1.2 Designing Applications for Portability
--------------------------------------------
Because all SQL servers implement different parts of standard SQL, it
takes work to write portable SQL 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
harder!
To make a complex application portable, you need to determine which SQL
servers it must work with, then determine what features those servers
support.
All database systems have some weak points. That is, they have different
design compromises that lead to different behavior.
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 shouldn't have column names 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 have to 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.
For `crash-me' results, visit
`http://dev.mysql.com/tech-resources/crash-me.php'. See
`http://dev.mysql.com/tech-resources/benchmarks/' for the results from
the benchmarks.
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 records for `MyISAM' tables, but will have 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 databases 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 need to
define an easily extendable interface through which you manipulate your
data. As C++ is available on most systems, 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 may be slower, it will
allow the other servers to perform the same tasks.
With MySQL, you can use the `/*! */' syntax to add MySQL-specific
keywords to a query. The code inside `/**/' will be treated as a
comment (and ignored) by most other SQL servers.
If high performance is more important than exactness, as in 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 and set the expiration timeout higher
until things get back to normal.
In this case, the table creation information should contain information
of the initial size of the cache and how often the table should normally
be refreshed.
An 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. Query Cache.
Info Catalog
(mysql.info.gz) Design Limitations
(mysql.info.gz) Optimize Overview
(mysql.info.gz) Internal use
automatically generated byinfo2html