DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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